Repository: tobymao/sqlglot

Issue:
Bug: Cast using array element (Snowflake)
sqlglot 25.8.1 produces invalid sql:
```
>>> sqlglot.parse_one("SELECT ARRAY_CONSTRUCT('foo')::VARIANT[0] as variant_key",dialect="snowflake").sql("snowflake")
"SELECT CAST(['foo'] AS ARRAY(VARIANT)[0]) AS variant_key"
```
This sql errors on Snowflake with:
```
ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 1 at position 37 unexpected '['.
syntax error line 1 at position 40 unexpected ')'.
```
Because the `[0]` is inside the bracket.

sqlglot 25.5.1 works:
```
>>> sqlglot.parse_one("SELECT ARRAY_CONSTRUCT('foo')::VARIANT[0] as variant_key",dialect="snowflake").sql("snowflake")
"SELECT CAST(['foo'] AS VARIANT)[0] AS variant_key"
```


Discussion:

Patch:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 9a1158d8..d2eef99d 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -375,6 +375,12 @@ class Dialect(metaclass=_Dialect):
     as the former is of type INT[] vs the latter which is SUPER
     """
 
+    SUPPORTS_FIXED_SIZE_ARRAYS = False
+    """
+    Whether expressions such as x::INT[5] should be parsed as fixed-size array defs/casts e.g. in DuckDB. In
+    dialects which don't support fixed size arrays such as Snowflake, this should be interpreted as a subscript/index operator
+    """
+
     # --- Autofilled ---
 
     tokenizer_class = Tokenizer
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 7fdc96c8..70edc1e5 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -227,6 +227,7 @@ class DuckDB(Dialect):
     INDEX_OFFSET = 1
     CONCAT_COALESCE = True
     SUPPORTS_ORDER_BY_ALL = True
+    SUPPORTS_FIXED_SIZE_ARRAYS = True
 
     # https://duckdb.org/docs/sql/introduction.html#creating-a-new-table
     NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index f1681e21..cd028774 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2848,6 +2848,8 @@ class Parser(metaclass=_Parser):
             table = self._match(TokenType.TABLE)
             this = self._parse_select() or self._parse_string() or self._parse_table()
             return self.expression(exp.Summarize, this=this, table=table)
+        elif self._match(TokenType.DESCRIBE):
+            this = self._parse_describe()
         else:
             this = None
 
@@ -4641,6 +4643,7 @@ class Parser(metaclass=_Parser):
         matched_array = self._match(TokenType.ARRAY)
 
         while self._curr:
+            datatype_token = self._prev.token_type
             matched_l_bracket = self._match(TokenType.L_BRACKET)
             if not matched_l_bracket and not matched_array:
                 break
@@ -4650,8 +4653,12 @@ class Parser(metaclass=_Parser):
             if (
                 values
                 and not schema
-                and this.is_type(exp.DataType.Type.ARRAY, exp.DataType.Type.MAP)
+                and (
+                    not self.dialect.SUPPORTS_FIXED_SIZE_ARRAYS or datatype_token == TokenType.ARRAY
+                )
             ):
+                # Retreating here means that we should not parse the following values as part of the data type, e.g. in DuckDB
+                # ARRAY[1] should retreat and instead be parsed into exp.Array in contrast to INT[x][y] which denotes a fixed-size array data type
                 self._retreat(index)
                 break
 


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 15af0864..c37dc77c 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -818,6 +818,9 @@ class TestDuckDB(Validator):
         )
 
         self.validate_identity("SELECT LENGTH(foo)")
+        self.validate_identity("SELECT ARRAY[1, 2, 3]", "SELECT [1, 2, 3]")
+
+        self.validate_identity("SELECT * FROM (DESCRIBE t)")
 
     def test_array_index(self):
         with self.assertLogs(helper_logger) as cm:
@@ -1156,6 +1159,12 @@ class TestDuckDB(Validator):
             },
         )
 
+        self.validate_identity("SELECT x::INT[3][3]", "SELECT CAST(x AS INT[3][3])")
+        self.validate_identity(
+            """SELECT ARRAY[[[1]]]::INT[1][1][1]""",
+            """SELECT CAST([[[1]]] AS INT[1][1][1])""",
+        )
+
     def test_encode_decode(self):
         self.validate_all(
             "ENCODE(x)",
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 31753052..16ef0196 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -860,6 +860,11 @@ WHERE
             },
         )
 
+        self.validate_identity(
+            """SELECT ARRAY_CONSTRUCT('foo')::VARIANT[0]""",
+            """SELECT CAST(['foo'] AS VARIANT)[0]""",
+        )
+
     def test_null_treatment(self):
         self.validate_all(
             r"SELECT FIRST_VALUE(TABLE1.COLUMN1) OVER (PARTITION BY RANDOM_COLUMN1, RANDOM_COLUMN2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MY_ALIAS FROM TABLE1",