I'm evaluating two AI coding assistants for a specific task: writing SQL queries for a healthcare data warehouse ETL pipeline. The data is sensitive, so correctness is critical. I tested both Claude (Sonnet via API) and Codeium on the same prompt.
My prompt was to write a SQL query that merges patient demographic data from two tables, applying HIPAA-safe de-identification by masking the last four characters of a patient identifier, and only including patients from the current year. I gave them the table schemas.
Claude produced a query using `CONCAT(LEFT(patient_id, LENGTH(patient_id)-4), 'XXXX')`. This seemed fine at first.
Codeium suggested using `REGEXP_REPLACE(patient_id, '.{4}$', 'XXXX')`.
The problem? Both assistants failed on the same edge case: patient identifiers that are not standard lengths or could be shorter than 4 characters. The `LEFT` function would error, and the regex would behave unexpectedly. Neither suggested a safer method, like using a `CASE` statement to check length.
The correct, production-safe answer should handle variable-length IDs:
`CASE WHEN LENGTH(patient_id) >= 4 THEN CONCAT(LEFT(patient_id, LENGTH(patient_id)-4), 'XXXX') ELSE 'XXXX' END`
This seems like a basic oversight for a healthcare context. Has anyone else run into similar issues with AI-generated SQL where edge cases in data cleaning are missed? I'm now hesitant to use either for this without very careful review.