Repository: tobymao/sqlglot

Issue:
Incorrect Parsing Between Spark SQL and DuckDB
There are a couple of mistakes when translating from Spark to DuckDB

**Fully reproducible code snippet**
```python
from sqlglot import parse_one
 query = """SELECT SHA2(str, 256), ANY_VALUE(str2, true), FIRST(str3, true), LAST(str4, true), TRANSFORM(array_col)
FROM table 
GROUP BY group_col"""
 parse_one(query, dialect="spark").sql(dialect="duckdb")

``` 
The output is:
```python
SELECT SHA2(str, 256), ANY_VALUE(str2 IGNORE NULLS), FIRST(str3 IGNORE NULLS), LAST(str4 IGNORE NULLS), TRANSFORM(array_col) FROM "table" GROUP BY group_col
``` 
Which fails for three different reasons:
1. SHA2 is not a function in duckdb - the relevant function is sha256 
2. TRANSFORM should be LIST_TRANSFORM
3. When using non-window function, duckdb doesn't support RESPECT/IGNORE NULLS. The default behavior is without NULLs 
The expected output
**Official Documentation**
1. https://duckdb.org/docs/sql/functions/utility.html - SHA256
2. https://duckdb.org/docs/sql/functions/list.html - LIST_TRANSFORM
3. https://duckdb.org/docs/sql/functions/aggregates.html - aggregate functions


Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index dcc63b18..22315054 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -33,6 +33,7 @@ from sqlglot.dialects.dialect import (
     timestrtotime_sql,
     unit_to_var,
     unit_to_str,
+    sha256_sql,
 )
 from sqlglot.helper import seq_get
 from sqlglot.tokens import TokenType
@@ -41,6 +42,14 @@ DATETIME_DELTA = t.Union[
     exp.DateAdd, exp.TimeAdd, exp.DatetimeAdd, exp.TsOrDsAdd, exp.DateSub, exp.DatetimeSub
 ]
 
+WINDOW_FUNCS_WITH_IGNORE_NULLS = (
+    exp.FirstValue,
+    exp.LastValue,
+    exp.Lag,
+    exp.Lead,
+    exp.NthValue,
+)
+
 
 def _date_delta_sql(self: DuckDB.Generator, expression: DATETIME_DELTA) -> str:
     this = expression.this
@@ -540,6 +549,7 @@ class DuckDB(Dialect):
             exp.ReturnsProperty: lambda self, e: "TABLE" if isinstance(e.this, exp.Schema) else "",
             exp.Rand: rename_func("RANDOM"),
             exp.SafeDivide: no_safe_divide_sql,
+            exp.SHA2: sha256_sql,
             exp.Split: rename_func("STR_SPLIT"),
             exp.SortArray: _sort_array_sql,
             exp.StrPosition: str_position_sql,
@@ -547,6 +557,7 @@ class DuckDB(Dialect):
                 "EPOCH", self.func("STRPTIME", e.this, self.format_time(e))
             ),
             exp.Struct: _struct_sql,
+            exp.Transform: rename_func("LIST_TRANSFORM"),
             exp.TimeAdd: _date_delta_sql,
             exp.Time: no_time_sql,
             exp.TimeDiff: _timediff_sql,
@@ -908,3 +919,11 @@ class DuckDB(Dialect):
                 return self.sql(select)
 
             return super().unnest_sql(expression)
+
+        def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
+            if isinstance(expression.this, WINDOW_FUNCS_WITH_IGNORE_NULLS):
+                # DuckDB should render IGNORE NULLS only for the general-purpose
+                # window functions that accept it e.g. FIRST_VALUE(... IGNORE NULLS) OVER (...)
+                return super().ignorenulls_sql(expression)
+
+            return self.sql(expression, "this")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 0896677b..040c07c2 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -4306,7 +4306,7 @@ class Parser(metaclass=_Parser):
                 exp.JSON, **{"this": kind, "with": _with, "unique": unique}
             )
         else:
-            expression = self._parse_null() or self._parse_boolean()
+            expression = self._parse_primary() or self._parse_null()
             if not expression:
                 self._retreat(index)
                 return None


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 8c9f0935..f6e8fe8f 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -370,7 +370,7 @@ LANGUAGE js AS
             },
             write={
                 "bigquery": "SELECT SUM(x IGNORE NULLS) AS x",
-                "duckdb": "SELECT SUM(x IGNORE NULLS) AS x",
+                "duckdb": "SELECT SUM(x) AS x",
                 "postgres": "SELECT SUM(x) IGNORE NULLS AS x",
                 "spark": "SELECT SUM(x) IGNORE NULLS AS x",
                 "snowflake": "SELECT SUM(x) IGNORE NULLS AS x",
@@ -405,7 +405,7 @@ LANGUAGE js AS
             "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 10) AS x",
             write={
                 "bigquery": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 10) AS x",
-                "duckdb": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a NULLS FIRST, b DESC LIMIT 10) AS x",
+                "duckdb": "SELECT ARRAY_AGG(DISTINCT x ORDER BY a NULLS FIRST, b DESC LIMIT 10) AS x",
                 "spark": "SELECT COLLECT_LIST(DISTINCT x ORDER BY a, b DESC LIMIT 10) IGNORE NULLS AS x",
             },
         )
@@ -413,7 +413,7 @@ LANGUAGE js AS
             "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 1, 10) AS x",
             write={
                 "bigquery": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 1, 10) AS x",
-                "duckdb": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a NULLS FIRST, b DESC LIMIT 1, 10) AS x",
+                "duckdb": "SELECT ARRAY_AGG(DISTINCT x ORDER BY a NULLS FIRST, b DESC LIMIT 1, 10) AS x",
                 "spark": "SELECT COLLECT_LIST(DISTINCT x ORDER BY a, b DESC LIMIT 1, 10) IGNORE NULLS AS x",
             },
         )
@@ -814,6 +814,7 @@ LANGUAGE js AS
                 "presto": "SHA256(x)",
                 "redshift": "SHA2(x, 256)",
                 "trino": "SHA256(x)",
+                "duckdb": "SHA256(x)",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index f8a0fc33..b32f802c 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -1257,3 +1257,20 @@ class TestDuckDB(Validator):
             read={"bigquery": "SELECT @foo"},
             write={"bigquery": "SELECT @foo", "duckdb": "SELECT $foo"},
         )
+
+    def test_ignore_nulls(self):
+        # Note that DuckDB differentiates window functions (e.g. LEAD, LAG) from aggregate functions (e.g. SUM)
+        from sqlglot.dialects.duckdb import WINDOW_FUNCS_WITH_IGNORE_NULLS
+
+        agg_funcs = (exp.Sum, exp.Max, exp.Min)
+
+        for func_type in WINDOW_FUNCS_WITH_IGNORE_NULLS + agg_funcs:
+            func = func_type(this=exp.to_identifier("col"))
+            ignore_null = exp.IgnoreNulls(this=func)
+            windowed_ignore_null = exp.Window(this=ignore_null)
+
+            if func_type in WINDOW_FUNCS_WITH_IGNORE_NULLS:
+                self.assertIn("IGNORE NULLS", windowed_ignore_null.sql("duckdb"))
+            else:
+                self.assertEqual(ignore_null.sql("duckdb"), func.sql("duckdb"))
+                self.assertNotIn("IGNORE NULLS", windowed_ignore_null.sql("duckdb"))
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index a2d87dff..feccd369 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -710,6 +710,13 @@ TBLPROPERTIES (
         )
         self.validate_identity("DESCRIBE schema.test PARTITION(ds = '2024-01-01')")
 
+        self.validate_all(
+            "SELECT ANY_VALUE(col, true), FIRST(col, true), FIRST_VALUE(col, true) OVER ()",
+            write={
+                "duckdb": "SELECT ANY_VALUE(col), FIRST(col), FIRST_VALUE(col IGNORE NULLS) OVER ()"
+            },
+        )
+
     def test_bool_or(self):
         self.validate_all(
             "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index 46bbadc1..f2c98029 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -91,6 +91,10 @@ class TestSQLite(Validator):
             read={"snowflake": "LEAST(x, y, z)"},
             write={"snowflake": "LEAST(x, y, z)"},
         )
+        self.validate_identity(
+            "SELECT * FROM station WHERE city IS NOT ''",
+            "SELECT * FROM station WHERE NOT city IS ''",
+        )
 
     def test_strftime(self):
         self.validate_identity("SELECT STRFTIME('%Y/%m/%d', 'now')")