Repository: tobymao/sqlglot

Issue:
Incorrect handling of the `~~` operator in DuckDB
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:

```sql
SELECT ?::VARCHAR ~~ ?::VARCHAR LIMIT 1
```

#### SQLGlot Output:

```sql
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:

```sql
SELECT ?::VARCHAR ~~ ?::VARCHAR LIMIT 1
```

Hints from issue comments:
@georgesittas: 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: 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: @georgesittas Just posted two PRs for the easy ones, the rest of the operator parsing cases I'll leave up you experts.

Solution patch:
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 49ff0aa0b7..1ed4c98edc 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -286,7 +286,6 @@ class Tokenizer(tokens.Tokenizer):
 
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
-            "~~": TokenType.LIKE,
             "~~*": TokenType.ILIKE,
             "~*": TokenType.IRLIKE,
             "~": TokenType.RLIKE,
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index fd084d869a..b66c489bde 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -650,6 +650,7 @@ class Tokenizer(metaclass=_Tokenizer):
         "&&": TokenType.DAMP,
         "??": TokenType.DQMARK,
         "~~~": TokenType.GLOB,
+        "~~": TokenType.LIKE,
         "ALL": TokenType.ALL,
         "ALWAYS": TokenType.ALWAYS,
         "AND": TokenType.AND,


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index c9f4d62a4c..ab15f8cffc 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -867,6 +867,7 @@ def test_duckdb(self):
         self.validate_identity("a ^ b", "POWER(a, b)")
         self.validate_identity("a ** b", "POWER(a, b)")
         self.validate_identity("a ~~~ b", "a GLOB b")
+        self.validate_identity("a ~~ b", "a LIKE b")
 
     def test_array_index(self):
         with self.assertLogs(helper_logger) as cm: