search

tobymao / sqlglot

Python SQL Parser and Transpiler
https://sqlglot.com/
MIT License
6.59k stars 688 forks source link

Incorrect handling of the `~~` operator in DuckDB #4188

Closed rustyconover closed 2 weeks ago

rustyconover commented 2 weeks ago

When testing SQLGlot with DuckDB, the ~~ operator is incorrectly handled. This operator, which functions similarly to the SQL LIKE pattern matching, is being translated into an invalid SQL expression.

Original Query:

SELECT ?::VARCHAR ~~ ?::VARCHAR LIMIT 1

SQLGlot Output:

SELECT REGEXP_MATCHES(CAST(? AS TEXT), ~CAST(? AS TEXT)) LIMIT 1

Expected Behavior:

SQLGlot should retain the ~~ operator for pattern matching in DuckDB, as it is equivalent to LIKE. The correct output should be:

SELECT ?::VARCHAR ~~ ?::VARCHAR LIMIT 1
georgesittas commented 2 weeks ago

Appreciate the reports @rustyconover, PRs are also welcome if you wanna take a stab at them. Just let us know so we don't do overlapping work.

rustyconover commented 2 weeks ago

Hi @georgesittas I don't have any PRs yet, but I'd be happy to polish up my test suite that discovered these issues.

It enumerates all functions available in duckdb then just formulates example queries, then I pass that through sqlglot to make sure that the query parses and the result is the same.

Rusty

rustyconover commented 2 weeks ago

@georgesittas Just posted two PRs for the easy ones, the rest of the operator parsing cases I'll leave up you experts.