Repository: tobymao/sqlglot

Issue:
Unsupported expression type ArrayContained
**Before you file an issue**
- Make sure you specify the "read" dialect eg. `parse_one(sql, read="postgres")`
- Make sure you specify the "write" dialect eg. `ast.sql(dialect="mysql")`
- Check if the issue still exists on main:Couldn't find anything related

**Fully reproducible code snippet**
```python
import sqlglot

sql = """select string_to_array('dd,ee', ',') <@array['dd','ee','ff','gg','hh'];"""

result1 = sqlglot.transpile(sql, read='postgres', write='mysql')[0]

print(result1)
``` 

The generator function throws an error: 
```ValueError: Unsupported expression type ArrayContained```


**Official Documentation**
Please include links to official SQL documentation related to your issue.

[(postgres array/string_to_array)](http://www.postgres.cn/docs/12/functions-array.html)




Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 19f2f4af..24c2b388 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -233,7 +233,6 @@ class DuckDB(Dialect):
 
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,
-            "ARRAY_HAS": exp.ArrayContains.from_arg_list,
             "ARRAY_REVERSE_SORT": _build_sort_array_desc,
             "ARRAY_SORT": exp.SortArray.from_arg_list,
             "DATEDIFF": _build_date_diff,
@@ -381,6 +380,7 @@ class DuckDB(Dialect):
             **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/mysql.py b/sqlglot/dialects/mysql.py
index e52d3ee6..d707cde0 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -823,6 +823,14 @@ class MySQL(Dialect):
             exp.DataType.Type.TIMESTAMPLTZ,
         }
 
+        def array_sql(self, expression: exp.Array) -> str:
+            self.unsupported("Arrays are not supported by MySQL")
+            return self.function_fallback_sql(expression)
+
+        def arraycontainsall_sql(self, expression: exp.ArrayContainsAll) -> str:
+            self.unsupported("Array operations are not supported by MySQL")
+            return self.function_fallback_sql(expression)
+
         def dpipe_sql(self, expression: exp.DPipe) -> str:
             return self.func("CONCAT", *expression.flatten())
 
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 1ee192b4..e50a557d 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -380,12 +380,12 @@ class Postgres(Dialect):
 
         RANGE_PARSERS = {
             **parser.Parser.RANGE_PARSERS,
-            TokenType.AT_GT: binary_range_parser(exp.ArrayContains),
+            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.ArrayContained),
+            TokenType.LT_AT: binary_range_parser(exp.ArrayContainsAll, reverse_args=True),
             TokenType.OPERATOR: lambda self, this: self._parse_operator(this),
         }
 
@@ -484,8 +484,7 @@ class Postgres(Dialect):
                 else f"{self.normalize_func('ARRAY')}[{self.expressions(e, flat=True)}]"
             ),
             exp.ArrayConcat: rename_func("ARRAY_CAT"),
-            exp.ArrayContained: lambda self, e: self.binary(e, "<@"),
-            exp.ArrayContains: lambda self, e: self.binary(e, "@>"),
+            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"),
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 0b28ba54..558caa51 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4808,11 +4808,11 @@ class ArrayConstructCompact(Func):
 
 
 class ArrayContains(Binary, Func):
-    pass
+    _sql_names = ["ARRAY_CONTAINS", "ARRAY_HAS"]
 
 
-class ArrayContained(Binary):
-    pass
+class ArrayContainsAll(Binary, Func):
+    _sql_names = ["ARRAY_CONTAINS_ALL", "ARRAY_HAS_ALL"]
 
 
 class ArrayFilter(Func):
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 048512d1..43b150ad 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -41,11 +41,17 @@ def build_like(args: t.List) -> exp.Escape | exp.Like:
 
 
 def binary_range_parser(
-    expr_type: t.Type[exp.Expression],
+    expr_type: t.Type[exp.Expression], reverse_args: bool = False
 ) -> t.Callable[[Parser, t.Optional[exp.Expression]], t.Optional[exp.Expression]]:
-    return lambda self, this: self._parse_escape(
-        self.expression(expr_type, this=this, expression=self._parse_bitwise())
-    )
+    def _parse_binary_range(
+        self: Parser, this: t.Optional[exp.Expression]
+    ) -> t.Optional[exp.Expression]:
+        expression = self._parse_bitwise()
+        if reverse_args:
+            this, expression = expression, this
+        return self._parse_escape(self.expression(expr_type, this=this, expression=expression))
+
+    return _parse_binary_range
 
 
 def build_logarithm(args: t.List, dialect: Dialect) -> exp.Func:


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index a8a6c12e..12af0d35 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -38,8 +38,6 @@ class TestPostgres(Validator):
         self.validate_identity("CAST(x AS TSTZMULTIRANGE)")
         self.validate_identity("CAST(x AS DATERANGE)")
         self.validate_identity("CAST(x AS DATEMULTIRANGE)")
-        self.validate_identity("SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]")
-        self.validate_identity("SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]")
         self.validate_identity("x$")
         self.validate_identity("SELECT ARRAY[1, 2, 3]")
         self.validate_identity("SELECT ARRAY(SELECT 1)")
@@ -64,6 +62,10 @@ class TestPostgres(Validator):
         self.validate_identity("EXEC AS myfunc @id = 123", check_command_warning=True)
         self.validate_identity("SELECT CURRENT_USER")
         self.validate_identity("SELECT * FROM ONLY t1")
+        self.validate_identity(
+            "SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]",
+            "SELECT ARRAY[1, 2] @> ARRAY[1, 2, 3]",
+        )
         self.validate_identity(
             """UPDATE "x" SET "y" = CAST('0 days 60.000000 seconds' AS INTERVAL) WHERE "x"."id" IN (2, 3)"""
         )
@@ -325,6 +327,17 @@ class TestPostgres(Validator):
             "CAST(x AS BIGINT)",
         )
 
+        self.validate_all(
+            "SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]",
+            read={
+                "duckdb": "SELECT ARRAY_HAS_ALL([1, 2, 3], [1, 2])",
+            },
+            write={
+                "duckdb": "SELECT ARRAY_HAS_ALL([1, 2, 3], [1, 2])",
+                "mysql": UnsupportedError,
+                "postgres": "SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]",
+            },
+        )
         self.validate_all(
             "SELECT REGEXP_REPLACE('mr .', '[^a-zA-Z]', '', 'g')",
             write={