Repository: tobymao/sqlglot

Issue:
duckdb struct to trino row syntax is incorrect
**Fully reproducible code snippet**

```
In [12]: sg.parse_one("select {'a': 1}", read="duckdb").sql(dialect="trino")
Out[12]: "SELECT ROW('a' : 1)"
```

The generated code is a syntax error in trino:

```
❯ trino --catalog hive --schema default --execute "SELECT ROW('a' : 1)"
Query 20230915_181601_00052_vnzqz failed: line 1:16: mismatched input ':'. Expecting: '%', ')', '*', '+', ',', '-', '.', '/', 'AND', 'AT', 'OR', 'ORDER', '[', '||', <predicate>
SELECT ROW('a' : 1)
```

The code generated from `struct_pack` is syntactically valid but incorrect:

```
In [13]: sg.parse_one("select struct_pack(a := 1)", read="duckdb").sql(dialect="trino")
Out[13]: 'SELECT ROW(a = 1)'
```

**Official Documentation**

https://trino.io/docs/current/language/types.html#row


Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 728e5f87..fe692003 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -12,6 +12,7 @@ from sqlglot.dialects.dialect import (
     date_add_interval_sql,
     datestrtodate_sql,
     format_time_lambda,
+    if_sql,
     inline_array_sql,
     json_keyvalue_comma_sql,
     max_or_greatest,
@@ -433,9 +434,7 @@ class BigQuery(Dialect):
             exp.GenerateSeries: rename_func("GENERATE_ARRAY"),
             exp.GroupConcat: rename_func("STRING_AGG"),
             exp.Hex: rename_func("TO_HEX"),
-            exp.If: lambda self, e: self.func(
-                "IF", e.this, e.args.get("true"), e.args.get("false") or "NULL"
-            ),
+            exp.If: if_sql(false_value="NULL"),
             exp.ILike: no_ilike_sql,
             exp.IntDiv: rename_func("DIV"),
             exp.JSONFormat: rename_func("TO_JSON_STRING"),
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 41551b05..05597e48 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -340,10 +340,18 @@ def approx_count_distinct_sql(self: Generator, expression: exp.ApproxDistinct) -
     return self.func("APPROX_COUNT_DISTINCT", expression.this)
 
 
-def if_sql(self: Generator, expression: exp.If) -> str:
-    return self.func(
-        "IF", expression.this, expression.args.get("true"), expression.args.get("false")
-    )
+def if_sql(
+    name: str = "IF", false_value: t.Optional[exp.Expression | str] = None
+) -> t.Callable[[Generator, exp.If], str]:
+    def _if_sql(self: Generator, expression: exp.If) -> str:
+        return self.func(
+            name,
+            expression.this,
+            expression.args.get("true"),
+            expression.args.get("false") or false_value,
+        )
+
+    return _if_sql
 
 
 def arrow_json_extract_sql(self: Generator, expression: exp.JSONExtract | exp.JSONBExtract) -> str:
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index bec27d3d..6c772d3e 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -413,7 +413,7 @@ class Hive(Dialect):
             exp.DiToDate: lambda self, e: f"TO_DATE(CAST({self.sql(e, 'this')} AS STRING), {Hive.DATEINT_FORMAT})",
             exp.FileFormatProperty: lambda self, e: f"STORED AS {self.sql(e, 'this') if isinstance(e.this, exp.InputOutputFormat) else e.name.upper()}",
             exp.FromBase64: rename_func("UNBASE64"),
-            exp.If: if_sql,
+            exp.If: if_sql(),
             exp.ILike: no_ilike_sql,
             exp.IsNan: rename_func("ISNAN"),
             exp.JSONExtract: rename_func("GET_JSON_OBJECT"),
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 128a4f62..0d8d4ab5 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -310,7 +310,7 @@ class Presto(Dialect):
             exp.First: _first_last_sql,
             exp.Group: transforms.preprocess([transforms.unalias_group]),
             exp.Hex: rename_func("TO_HEX"),
-            exp.If: if_sql,
+            exp.If: if_sql(),
             exp.ILike: no_ilike_sql,
             exp.Initcap: _initcap_sql,
             exp.ParseJSON: rename_func("JSON_PARSE"),
@@ -343,7 +343,6 @@ class Presto(Dialect):
             exp.StrToMap: rename_func("SPLIT_TO_MAP"),
             exp.StrToTime: _str_to_time_sql,
             exp.StrToUnix: lambda self, e: f"TO_UNIXTIME(DATE_PARSE({self.sql(e, 'this')}, {self.format_time(e)}))",
-            exp.Struct: rename_func("ROW"),
             exp.StructExtract: struct_extract_sql,
             exp.Table: transforms.preprocess([_unnest_sequence]),
             exp.TimestampTrunc: timestamptrunc_sql,
@@ -369,6 +368,13 @@ class Presto(Dialect):
             exp.Xor: bool_xor_sql,
         }
 
+        def struct_sql(self, expression: exp.Struct) -> str:
+            if any(isinstance(arg, (exp.EQ, exp.Slice)) for arg in expression.expressions):
+                self.unsupported("Struct with key-value definitions is unsupported.")
+                return self.function_fallback_sql(expression)
+
+            return rename_func("ROW")(self, expression)
+
         def interval_sql(self, expression: exp.Interval) -> str:
             unit = self.sql(expression, "unit")
             if expression.this and unit.lower().startswith("week"):
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 3b758081..5c493315 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -9,6 +9,7 @@ from sqlglot.dialects.dialect import (
     date_trunc_to_time,
     datestrtodate_sql,
     format_time_lambda,
+    if_sql,
     inline_array_sql,
     max_or_greatest,
     min_or_least,
@@ -242,6 +243,12 @@ class Snowflake(Dialect):
             **parser.Parser.FUNCTIONS,
             "ARRAYAGG": exp.ArrayAgg.from_arg_list,
             "ARRAY_CONSTRUCT": exp.Array.from_arg_list,
+            "ARRAY_GENERATE_RANGE": lambda args: exp.GenerateSeries(
+                # ARRAY_GENERATE_RANGE has an exlusive end; we normalize it to be inclusive
+                start=seq_get(args, 0),
+                end=exp.Sub(this=seq_get(args, 1), expression=exp.Literal.number(1)),
+                step=seq_get(args, 2),
+            ),
             "ARRAY_TO_STRING": exp.ArrayJoin.from_arg_list,
             "BITXOR": binary_from_function(exp.BitwiseXor),
             "BIT_XOR": binary_from_function(exp.BitwiseXor),
@@ -405,8 +412,11 @@ class Snowflake(Dialect):
             exp.DataType: _datatype_sql,
             exp.DayOfWeek: rename_func("DAYOFWEEK"),
             exp.Extract: rename_func("DATE_PART"),
+            exp.GenerateSeries: lambda self, e: self.func(
+                "ARRAY_GENERATE_RANGE", e.args["start"], e.args["end"] + 1, e.args.get("step")
+            ),
             exp.GroupConcat: rename_func("LISTAGG"),
-            exp.If: rename_func("IFF"),
+            exp.If: if_sql(name="IFF", false_value="NULL"),
             exp.LogicalAnd: rename_func("BOOLAND_AGG"),
             exp.LogicalOr: rename_func("BOOLOR_AGG"),
             exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
@@ -464,12 +474,23 @@ class Snowflake(Dialect):
         }
 
         def unnest_sql(self, expression: exp.Unnest) -> str:
+            selects = ["value"]
+            unnest_alias = expression.args.get("alias")
+
+            offset = expression.args.get("offset")
+            if offset:
+                if unnest_alias:
+                    expression = expression.copy()
+                    unnest_alias.append("columns", offset.pop())
+
+                selects.append("index")
+
             subquery = exp.Subquery(
-                this=exp.select("value").from_(
+                this=exp.select(*selects).from_(
                     f"TABLE(FLATTEN(INPUT => {self.sql(expression.expressions[0])}))"
                 ),
             )
-            alias = self.sql(expression, "alias")
+            alias = self.sql(unnest_alias)
             alias = f" AS {alias}" if alias else ""
             return f"{self.sql(subquery)}{alias}"
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index dbda7149..d0e7cc7b 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -973,7 +973,7 @@ class Generator:
         agg = expression.this.copy()
         agg_arg = agg.this
         cond = expression.expression.this
-        agg_arg.replace(exp.If(this=cond.copy(), true=agg_arg.copy(), false=exp.null()))
+        agg_arg.replace(exp.If(this=cond.copy(), true=agg_arg.copy()))
         return self.sql(agg)
 
     def hint_sql(self, expression: exp.Hint) -> str:
diff --git a/sqlglot/transforms.py b/sqlglot/transforms.py
index 0a8d397d..f66b779c 100644
--- a/sqlglot/transforms.py
+++ b/sqlglot/transforms.py
@@ -187,6 +187,7 @@ def explode_to_unnest(index_offset: int = 0) -> t.Callable[[exp.Expression], exp
                 table=[series_alias],
             )
 
+            # we use list here because expression.selects is mutated inside the loop
             for select in list(expression.selects):
                 to_replace = select
                 pos_alias = ""


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 6b496ecd..96d06592 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -11,6 +11,9 @@ class TestDuckDB(Validator):
             exp.select("*").from_("t").limit(exp.select("5").subquery()).sql(dialect="duckdb"),
         )
 
+        for struct_value in ("{'a': 1}", "struct_pack(a := 1)"):
+            self.validate_all(struct_value, write={"presto": UnsupportedError})
+
         for join_type in ("SEMI", "ANTI"):
             exists = "EXISTS" if join_type == "SEMI" else "NOT EXISTS"
 
@@ -135,7 +138,7 @@ class TestDuckDB(Validator):
             "SELECT UNNEST([1, 2, 3])",
             write={
                 "duckdb": "SELECT UNNEST([1, 2, 3])",
-                "snowflake": "SELECT IFF(pos = pos_2, col) AS col FROM (SELECT value FROM TABLE(FLATTEN(INPUT => GENERATE_SERIES(0, GREATEST(ARRAY_SIZE([1, 2, 3])) - 1)))) AS _u(pos) CROSS JOIN (SELECT value FROM TABLE(FLATTEN(INPUT => [1, 2, 3]))) AS _u_2(col) WHERE pos = pos_2 OR (pos > (ARRAY_SIZE([1, 2, 3]) - 1) AND pos_2 = (ARRAY_SIZE([1, 2, 3]) - 1))",
+                "snowflake": "SELECT IFF(pos = pos_2, col, NULL) AS col FROM (SELECT value FROM TABLE(FLATTEN(INPUT => ARRAY_GENERATE_RANGE(0, (GREATEST(ARRAY_SIZE([1, 2, 3])) - 1) + 1)))) AS _u(pos) CROSS JOIN (SELECT value, index FROM TABLE(FLATTEN(INPUT => [1, 2, 3]))) AS _u_2(col, pos_2) WHERE pos = pos_2 OR (pos > (ARRAY_SIZE([1, 2, 3]) - 1) AND pos_2 = (ARRAY_SIZE([1, 2, 3]) - 1))",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 30a1f037..93bfe281 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -83,6 +83,23 @@ class TestSnowflake(Validator):
         self.validate_all("CAST(x AS CHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
         self.validate_all("CAST(x AS CHARACTER VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
         self.validate_all("CAST(x AS NCHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
+        self.validate_all(
+            "ARRAY_GENERATE_RANGE(0, 3)",
+            write={
+                "bigquery": "GENERATE_ARRAY(0, 3 - 1)",
+                "postgres": "GENERATE_SERIES(0, 3 - 1)",
+                "presto": "SEQUENCE(0, 3 - 1)",
+                "snowflake": "ARRAY_GENERATE_RANGE(0, (3 - 1) + 1)",
+            },
+        )
+        self.validate_all(
+            "ARRAY_GENERATE_RANGE(0, 3 + 1)",
+            read={
+                "bigquery": "GENERATE_ARRAY(0, 3)",
+                "postgres": "GENERATE_SERIES(0, 3)",
+                "presto": "SEQUENCE(0, 3)",
+            },
+        )
         self.validate_all(
             "SELECT DATE_PART('year', TIMESTAMP '2020-01-01')",
             write={