Repository: tobymao/sqlglot
Pull request: feat(duckdb): Add more Postgres operators
Fixes #4189

The `^@` operator is an alias of the `STARTS_WITH(a, b)` function:

```SQL
──────────────┬────────────────────┬─────────┬──────────────────┬──────────────────┬──────────┬──────────────┬────────────────────────┬────────────┐
│ database_name │ database_oid │ schema_name │ function_name │ function_type │                   description                    │ comment │ tags │ return_type │       parameters        │  parameter_types   │ varargs │ macro_definition │ has_side_effects │ internal │ function_oid │        example         │ stability  │
├───────────────┼──────────────┼─────────────┼───────────────┼───────────────┼──────────────────────────────────────────────────┼─────────┼──────┼─────────────┼─────────────────────────┼────────────────────┼─────────┼──────────────────┼──────────────────┼──────────┼──────────────┼────────────────────────┼────────────┤
│ system        │ 0            │ main        │ ^@            │ scalar        │ Returns true if string begins with search_string │         │ {}   │ BOOLEAN     │ [string, search_string] │ [VARCHAR, VARCHAR] │         │                  │ false            │ true     │ 428          │ starts_with('abc','a') │ CONSISTENT │
│ system        │ 0            │ main        │ starts_with   │ scalar        │ Returns true if string begins with search_string │         │ {}   │ BOOLEAN     │ [string, search_string] │ [VARCHAR, VARCHAR] │         │                  │ false            │ true     │ 994          │ starts_with('abc','a') │ CONSISTENT │
└───────────────┴──────────────┴─────────────┴───────────────┴───────────────┴──────────────────────────────────────────────────┴─────────┴──────┴─────────────┴─────────────────────────┴────────────────────┴─────────┴──────────────────┴──────────────────┴──────────┴──────────────┴────────────────────────┴────────────┘
```

All other operators seem to be borrowed by Postgres, so this PR extends/moves their definitions to the base dialect.

Docs
--------
[Postgres operators](https://www.postgresql.org/docs/6.3/c09.htm) | [DuckDB Text operators/functions](https://duckdb.org/docs/sql/functions/char.html)
 

Problem statement:
DuckDB `!~~`, `!~~*`, `&&`, `**`, `<@`, `@>`,  `^@` operators are not parsed
When using SQLGlot's DuckDB dialect, it fails to parse several valid SQL statements containing operators that are supported by DuckDB. The issue appears to stem from SQLGlot not recognizing these operators.

#### Example Queries (Valid in DuckDB but Failing in SQLGlot):

```sql
SELECT ?::VARCHAR !~~ ?::VARCHAR LIMIT 1
SELECT ?::VARCHAR !~~* ?::VARCHAR LIMIT 1
SELECT ?::VARCHAR[] && ?::VARCHAR[] LIMIT 1
SELECT ?::DOUBLE ** ?::DOUBLE LIMIT 1
SELECT ?::VARCHAR[] <@ ?::VARCHAR[] LIMIT 1
SELECT ?::VARCHAR ^@ ?::VARCHAR LIMIT 1
```

#### Issue:
SQLGlot does not recognize these operators, which results in a parsing failure when using the DuckDB dialect

#### DuckDB Function Definitions:
The operators are defined as follows in DuckDB:

```sql
SELECT function_name, description 
FROM duckdb_functions() 
WHERE function_name IN ('!~~', '!~~*', '&&', '**', '<@', '@>',  '^@');
```

| Function Name | Description                                                                 |
|---------------|-----------------------------------------------------------------------------|
| !~~           |                                                                             |
| !~~*          |                                                                             |
| &&            | Returns true if the lists have any element in common. NULLs are ignored.    |
| **            | Computes x to the power of y                                                |
| <@            | Returns true if all elements of l2 are in l1. NULLs are ignored.            |
| @>            | Returns true if all elements of l2 are in l1. NULLs are ignored.            |
| ^@            | Returns true if string begins with search_string                            |

Hints from issue discussion:
Hey @rustyconover,

Thanks once again for these issues, they really help increase coverage. You mentioned that these tests were done automatically, do you think these specific operators are widely used? I personally haven't come across these before, and the first two do not seem documented with a description yet.

The pain point is that each of these will probably require a new token, a new AST node, their precedence level figured out etc so if they're not particularly famous and/or properly documented, I'd be inclined to close this as not planned.

It will really help if we can map these to an existing DuckDB function, which will remove most of the burden as with the other operators.
Hi @VaggelisD,

Thank you for your response and PRs. I appreciate your really prompt attention to the issues I opened.  It's amazing to see such great support for this project.  It makes me feel like I'm really utilizing a great project as a dependency.

I’ve been developing code that performs extensive DuckDB SQL translation and analysis, and I I think it’s important to conduct testing upfront rather than discovering issues during my application's runtime.

To that end, I’ve tested nearly every function DuckDB provides to ensure consistent results when passed through `sqlglot`. While I don’t have specific statistics on the usage frequency of these in this issue operators, I’m aware that many are likely the same as PostgreSQL, given DuckDB's use of its parser. I believe this effort could potentially benefit PostgreSQL as well.

From my professional experience, I’ve frequently used operators like `<@`, `@>`, and `&&`, so I believe they’re valuable to implement. However, that’s based on anecdotal evidence rather than concrete data.

As for lack of documentation, I have good interactions with the DuckDB team and can get documentation updated.

Best regards and thank you for your help!
 
Rusty
Thanks for your kind words, they truly mean a lot! Well written issues help with the prompt replies.

Indeed, most of these operators come from Postgres and we already support a few of them so their DuckDB extension will not take long; I'll hopefully have them all supported by tomorrow.

Commit message: Remove tokens from base tokenizer

Patch to review:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 8eb766b3c3..13b5fa1312 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -38,6 +38,7 @@
 )
 from sqlglot.helper import seq_get
 from sqlglot.tokens import TokenType
+from sqlglot.parser import binary_range_parser
 
 DATETIME_DELTA = t.Union[
     exp.DateAdd, exp.TimeAdd, exp.DatetimeAdd, exp.TsOrDsAdd, exp.DateSub, exp.DatetimeSub
@@ -290,6 +291,9 @@ class Tokenizer(tokens.Tokenizer):
             **tokens.Tokenizer.KEYWORDS,
             "//": TokenType.DIV,
             "**": TokenType.DSTAR,
+            "^@": TokenType.CARET_AMP,
+            "@>": TokenType.AT_GT,
+            "<@": TokenType.LT_AT,
             "ATTACH": TokenType.COMMAND,
             "BINARY": TokenType.VARBINARY,
             "BITSTRING": TokenType.BIT,
@@ -328,6 +332,12 @@ class Parser(parser.Parser):
         }
         BITWISE.pop(TokenType.CARET)
 
+        RANGE_PARSERS = {
+            **parser.Parser.RANGE_PARSERS,
+            TokenType.DAMP: binary_range_parser(exp.ArrayOverlaps),
+            TokenType.CARET_AMP: binary_range_parser(exp.StartsWith),
+        }
+
         EXPONENT = {
             **parser.Parser.EXPONENT,
             TokenType.CARET: exp.Pow,
@@ -488,7 +498,6 @@ class Generator(generator.Generator):
             **generator.Generator.TRANSFORMS,
             exp.ApproxDistinct: approx_count_distinct_sql,
             exp.Array: inline_array_unless_query,
-            exp.ArrayContainsAll: rename_func("ARRAY_HAS_ALL"),
             exp.ArrayFilter: rename_func("LIST_FILTER"),
             exp.ArraySize: rename_func("ARRAY_LENGTH"),
             exp.ArgMax: arg_max_or_min_no_count("ARG_MAX"),
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 1ed4c98edc..7c431c78d3 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -286,8 +286,6 @@ class Tokenizer(tokens.Tokenizer):
 
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
-            "~~*": TokenType.ILIKE,
-            "~*": TokenType.IRLIKE,
             "~": TokenType.RLIKE,
             "@@": TokenType.DAT,
             "@>": TokenType.AT_GT,
@@ -385,12 +383,10 @@ class Parser(parser.Parser):
 
         RANGE_PARSERS = {
             **parser.Parser.RANGE_PARSERS,
-            TokenType.AT_GT: binary_range_parser(exp.ArrayContainsAll),
             TokenType.DAMP: binary_range_parser(exp.ArrayOverlaps),
             TokenType.DAT: lambda self, this: self.expression(
                 exp.MatchAgainst, this=self._parse_bitwise(), expressions=[this]
             ),
-            TokenType.LT_AT: binary_range_parser(exp.ArrayContainsAll, reverse_args=True),
             TokenType.OPERATOR: lambda self, this: self._parse_operator(this),
         }
 
@@ -488,8 +484,6 @@ class Generator(generator.Generator):
             **generator.Generator.TRANSFORMS,
             exp.AnyValue: any_value_to_max_sql,
             exp.ArrayConcat: lambda self, e: self.arrayconcat_sql(e, name="ARRAY_CAT"),
-            exp.ArrayContainsAll: lambda self, e: self.binary(e, "@>"),
-            exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
             exp.ArrayFilter: filter_array_using_unnest,
             exp.ArraySize: lambda self, e: self.func("ARRAY_LENGTH", e.this, e.expression or "1"),
             exp.BitwiseXor: lambda self, e: self.binary(e, "#"),
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index bf5f5f8093..665a3f57fc 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -114,6 +114,8 @@ class Generator(metaclass=_Generator):
         **JSON_PATH_PART_TRANSFORMS,
         exp.AllowedValuesProperty: lambda self,
         e: f"ALLOWED_VALUES {self.expressions(e, flat=True)}",
+        exp.ArrayContainsAll: lambda self, e: self.binary(e, "@>"),
+        exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
         exp.AutoRefreshProperty: lambda self, e: f"AUTO REFRESH {self.sql(e, 'this')}",
         exp.BackupProperty: lambda self, e: f"BACKUP {self.sql(e, 'this')}",
         exp.CaseSpecificColumnConstraint: lambda _,
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 5f73496730..86de17a8b1 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -859,6 +859,7 @@ class Parser(metaclass=_Parser):
     }
 
     RANGE_PARSERS = {
+        TokenType.AT_GT: binary_range_parser(exp.ArrayContainsAll),
         TokenType.BETWEEN: lambda self, this: self._parse_between(this),
         TokenType.GLOB: binary_range_parser(exp.Glob),
         TokenType.ILIKE: binary_range_parser(exp.ILike),
@@ -866,6 +867,7 @@ class Parser(metaclass=_Parser):
         TokenType.IRLIKE: binary_range_parser(exp.RegexpILike),
         TokenType.IS: lambda self, this: self._parse_is(this),
         TokenType.LIKE: binary_range_parser(exp.Like),
+        TokenType.LT_AT: binary_range_parser(exp.ArrayContainsAll, reverse_args=True),
         TokenType.OVERLAPS: binary_range_parser(exp.Overlaps),
         TokenType.RLIKE: binary_range_parser(exp.RegexpLike),
         TokenType.SIMILAR_TO: binary_range_parser(exp.SimilarTo),
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index b66c489bde..f78af72aa6 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -60,6 +60,7 @@ class TokenType(AutoName):
     PIPE_SLASH = auto()
     DPIPE_SLASH = auto()
     CARET = auto()
+    CARET_AMP = auto()
     TILDA = auto()
     ARROW = auto()
     DARROW = auto()
@@ -651,6 +652,8 @@ class Tokenizer(metaclass=_Tokenizer):
         "??": TokenType.DQMARK,
         "~~~": TokenType.GLOB,
         "~~": TokenType.LIKE,
+        "~~*": TokenType.ILIKE,
+        "~*": TokenType.IRLIKE,
         "ALL": TokenType.ALL,
         "ALWAYS": TokenType.ALWAYS,
         "AND": TokenType.AND,
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index ab15f8cffc..6b58934ccc 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -868,6 +868,18 @@ def test_duckdb(self):
         self.validate_identity("a ** b", "POWER(a, b)")
         self.validate_identity("a ~~~ b", "a GLOB b")
         self.validate_identity("a ~~ b", "a LIKE b")
+        self.validate_identity("a @> b")
+        self.validate_identity("a <@ b", "b @> a")
+        self.validate_identity("a && b").assert_is(exp.ArrayOverlaps)
+        self.validate_identity("a ^@ b", "STARTS_WITH(a, b)")
+        self.validate_identity(
+            "a !~~ b",
+            "NOT a LIKE b",
+        )
+        self.validate_identity(
+            "a !~~* b",
+            "NOT a ILIKE b",
+        )
 
     def test_array_index(self):
         with self.assertLogs(helper_logger) as cm:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 63266a50e6..62ae24729e 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -354,10 +354,10 @@ def test_postgres(self):
         self.validate_all(
             "SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]",
             read={
-                "duckdb": "SELECT ARRAY_HAS_ALL([1, 2, 3], [1, 2])",
+                "duckdb": "SELECT [1, 2, 3] @> [1, 2]",
             },
             write={
-                "duckdb": "SELECT ARRAY_HAS_ALL([1, 2, 3], [1, 2])",
+                "duckdb": "SELECT [1, 2, 3] @> [1, 2]",
                 "mysql": UnsupportedError,
                 "postgres": "SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]",
             },
@@ -398,13 +398,6 @@ def test_postgres(self):
                 "postgres": "SELECT (data ->> 'en-US') AS acat FROM my_table",
             },
         )
-        self.validate_all(
-            "SELECT ARRAY[1, 2, 3] && ARRAY[1, 2]",
-            write={
-                "": "SELECT ARRAY_OVERLAPS(ARRAY(1, 2, 3), ARRAY(1, 2))",
-                "postgres": "SELECT ARRAY[1, 2, 3] && ARRAY[1, 2]",
-            },
-        )
         self.validate_all(
             "SELECT JSON_EXTRACT_PATH_TEXT(x, k1, k2, k3) FROM t",
             read={
@@ -802,6 +795,7 @@ def test_postgres(self):
         )
         self.validate_identity("SELECT OVERLAY(a PLACING b FROM 1)")
         self.validate_identity("SELECT OVERLAY(a PLACING b FROM 1 FOR 1)")
+        self.validate_identity("ARRAY[1, 2, 3] && ARRAY[1, 2]").assert_is(exp.ArrayOverlaps)
 
     def test_ddl(self):
         # Checks that user-defined types are parsed into DataType instead of Identifier


Code review comments:
diff_hunk: @@ -290,6 +291,9 @@ class Tokenizer(tokens.Tokenizer):
             **tokens.Tokenizer.KEYWORDS,
             "//": TokenType.DIV,
             "**": TokenType.DSTAR,
+            "^@": TokenType.CARET_AMP,
path: sqlglot/dialects/duckdb.py
text: @user1:
I think there's a typo here, this needs to be `CARET_AT`, not `AMP`.