Repository: tobymao/sqlglot

Issue:
generate_series() is not transpiled to sequence() when going from duckdb to spark sql
**Fully reproducible code snippet**

```python
from sqlglot import transpile 

# duckdb query
sql_query = """
WITH date_table AS (
    SELECT 
        UNNEST(generate_series(DATE '2022-06-01', DATE '2024-01-01', INTERVAL '1 month')) AS date
)
select * from date_table
"""

spark_query = transpile(sql_query, read="duckdb", write="spark")[0]

spark = <your spark session>
spark.sql(spark_query).show()
```

This code throws an error because there is no `generate_series()` function in spark:
```
AnalysisException: [UNRESOLVED_ROUTINE] Cannot resolve function `GENERATE_SERIES` on search path [`system`.`builtin`, `system`.`session`, `spark_catalog`.`default`]
```

The output of transpile() is:

```
"WITH date_table AS (SELECT EXPLODE(GENERATE_SERIES(CAST('2022-06-01' AS DATE), CAST('2024-01-01' AS DATE), INTERVAL '1' MONTH)) AS date) SELECT * FROM date_table"
```

but it should be:

```
"WITH date_table AS (SELECT EXPLODE(SEQUENCE(CAST('2022-06-01' AS DATE), CAST('2024-01-01' AS DATE), INTERVAL '1' MONTH)) AS date) SELECT * FROM date_table"
```


I think the same case is when going from postgres -> spark.


---

A similar issue for trino was created before #1077 


Discussion:

Patch:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index a6409f86..e08f8bae 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -1588,3 +1588,24 @@ def build_timestamp_from_parts(args: t.List) -> exp.Func:
 
 def sha256_sql(self: Generator, expression: exp.SHA2) -> str:
     return self.func(f"SHA{expression.text('length') or '256'}", expression.this)
+
+
+def sequence_sql(self: Generator, expression: exp.GenerateSeries):
+    start = expression.args["start"]
+    end = expression.args["end"]
+    step = expression.args.get("step")
+
+    if isinstance(start, exp.Cast):
+        target_type = start.to
+    elif isinstance(end, exp.Cast):
+        target_type = end.to
+    else:
+        target_type = None
+
+    if target_type and target_type.is_type("timestamp"):
+        if target_type is start.to:
+            end = exp.cast(end, target_type)
+        else:
+            start = exp.cast(start, target_type)
+
+    return self.func("SEQUENCE", start, end, step)
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 5120a1d8..fe7e2160 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -31,6 +31,7 @@ from sqlglot.dialects.dialect import (
     timestrtotime_sql,
     unit_to_str,
     var_map_sql,
+    sequence_sql,
 )
 from sqlglot.transforms import (
     remove_unique_constraints,
@@ -310,6 +311,7 @@ class Hive(Dialect):
             "REGEXP_EXTRACT": lambda args: exp.RegexpExtract(
                 this=seq_get(args, 0), expression=seq_get(args, 1), group=seq_get(args, 2)
             ),
+            "SEQUENCE": exp.GenerateSeries.from_arg_list,
             "SIZE": exp.ArraySize.from_arg_list,
             "SPLIT": exp.RegexpSplit.from_arg_list,
             "STR_TO_MAP": lambda args: exp.StrToMap(
@@ -506,6 +508,7 @@ class Hive(Dialect):
             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.GenerateSeries: sequence_sql,
             exp.If: if_sql(),
             exp.ILike: no_ilike_sql,
             exp.IsNan: rename_func("ISNAN"),
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index c25f9f89..cda1c940 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -365,6 +365,7 @@ class Postgres(Dialect):
             "NOW": exp.CurrentTimestamp.from_arg_list,
             "REGEXP_REPLACE": _build_regexp_replace,
             "TO_CHAR": build_formatted_time(exp.TimeToStr, "postgres"),
+            "TO_DATE": build_formatted_time(exp.StrToDate, "postgres"),
             "TO_TIMESTAMP": _build_to_timestamp,
             "UNNEST": exp.Explode.from_arg_list,
             "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)),
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 27f050b4..c8a3b00a 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -28,6 +28,7 @@ from sqlglot.dialects.dialect import (
     timestrtotime_sql,
     ts_or_ds_add_cast,
     unit_to_str,
+    sequence_sql,
 )
 from sqlglot.dialects.hive import Hive
 from sqlglot.dialects.mysql import MySQL
@@ -435,6 +436,7 @@ class Presto(Dialect):
             exp.FirstValue: _first_last_sql,
             exp.FromTimeZone: lambda self,
             e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'",
+            exp.GenerateSeries: sequence_sql,
             exp.Group: transforms.preprocess([transforms.unalias_group]),
             exp.GroupConcat: lambda self, e: self.func(
                 "ARRAY_JOIN", self.func("ARRAY_AGG", e.this), e.args.get("separator")
@@ -651,26 +653,6 @@ class Presto(Dialect):
             modes = f" {', '.join(modes)}" if modes else ""
             return f"START TRANSACTION{modes}"
 
-        def generateseries_sql(self, expression: exp.GenerateSeries) -> str:
-            start = expression.args["start"]
-            end = expression.args["end"]
-            step = expression.args.get("step")
-
-            if isinstance(start, exp.Cast):
-                target_type = start.to
-            elif isinstance(end, exp.Cast):
-                target_type = end.to
-            else:
-                target_type = None
-
-            if target_type and target_type.is_type("timestamp"):
-                if target_type is start.to:
-                    end = exp.cast(end, target_type)
-                else:
-                    start = exp.cast(start, target_type)
-
-            return self.func("SEQUENCE", start, end, step)
-
         def offset_limit_modifiers(
             self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit]
         ) -> t.List[str]:


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index b89cd940..92e8443d 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -565,24 +565,33 @@ class TestPostgres(Validator):
                 "postgres": "GENERATE_SERIES(CAST('2019-01-01' AS TIMESTAMP), CURRENT_TIMESTAMP, INTERVAL '1 DAY')",
                 "presto": "SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP AS TIMESTAMP), INTERVAL '1' DAY)",
                 "trino": "SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP AS TIMESTAMP), INTERVAL '1' DAY)",
+                "hive": "SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP() AS TIMESTAMP), INTERVAL '1' DAY)",
+                "spark2": "SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP() AS TIMESTAMP), INTERVAL '1' DAY)",
+                "spark": "SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP() AS TIMESTAMP), INTERVAL '1' DAY)",
+                "databricks": "SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), CAST(CURRENT_TIMESTAMP() AS TIMESTAMP), INTERVAL '1' DAY)",
             },
         )
         self.validate_all(
             "GENERATE_SERIES(a, b)",
-            write={
+            read={
                 "postgres": "GENERATE_SERIES(a, b)",
                 "presto": "SEQUENCE(a, b)",
                 "trino": "SEQUENCE(a, b)",
                 "tsql": "GENERATE_SERIES(a, b)",
+                "hive": "SEQUENCE(a, b)",
+                "spark2": "SEQUENCE(a, b)",
+                "spark": "SEQUENCE(a, b)",
+                "databricks": "SEQUENCE(a, b)",
             },
-        )
-        self.validate_all(
-            "GENERATE_SERIES(a, b)",
-            read={
+            write={
                 "postgres": "GENERATE_SERIES(a, b)",
                 "presto": "SEQUENCE(a, b)",
                 "trino": "SEQUENCE(a, b)",
                 "tsql": "GENERATE_SERIES(a, b)",
+                "hive": "SEQUENCE(a, b)",
+                "spark2": "SEQUENCE(a, b)",
+                "spark": "SEQUENCE(a, b)",
+                "databricks": "SEQUENCE(a, b)",
             },
         )
         self.validate_all(
@@ -759,6 +768,14 @@ class TestPostgres(Validator):
             },
         )
 
+        self.validate_all(
+            "SELECT TO_DATE('01/01/2000', 'MM/DD/YYYY')",
+            write={
+                "duckdb": "SELECT CAST(STRPTIME('01/01/2000', '%m/%d/%Y') AS DATE)",
+                "postgres": "SELECT TO_DATE('01/01/2000', 'MM/DD/YYYY')",
+            },
+        )
+
     def test_ddl(self):
         # Checks that user-defined types are parsed into DataType instead of Identifier
         self.parse_one("CREATE TABLE t (a udt)").this.expressions[0].args["kind"].assert_is(