Repository: tobymao/sqlglot

Issue:
SQLGlot silently ignores `ignore nulls` when transpiling spark to duckdb
**Before you file an issue**
- Make sure you specify the "read" dialect eg. `parse_one(sql, read="spark")`
- Make sure you specify the "write" dialect eg. `ast.sql(dialect="duckdb")`
- Check if the issue still exists on main

**Fully reproducible code snippet**
```python
import sqlglot

print(sqlglot.transpile('select last(`a1`.`a`) ignore nulls over (order by `a1`.`idx`)', read='spark', write='duckdb'))
print(sqlglot.transpile('select last(`a1`.`a`) over (order by `a1`.`idx`)', read='spark', write='duckdb'))

```
Outputs:
```
['SELECT LAST("a1"."a") OVER (ORDER BY "a1"."idx" NULLS FIRST)']
['SELECT LAST("a1"."a") OVER (ORDER BY "a1"."idx" NULLS FIRST)']
```

Expected behaviour: either:
- "ignore nulls" be respected
- throw an error if this is not implemented or can't be respected

Version: SQLGlot 26.15.0

**Official Documentation**
Please include links to official SQL documentation related to your issue.

Hints from issue comments:

Solution patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 932f028a2a..c1550f4229 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -47,14 +47,6 @@
     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
@@ -879,6 +871,14 @@ class Generator(generator.Generator):
         PROPERTIES_LOCATION[exp.TemporaryProperty] = exp.Properties.Location.POST_CREATE
         PROPERTIES_LOCATION[exp.ReturnsProperty] = exp.Properties.Location.POST_ALIAS
 
+        IGNORE_RESPECT_NULLS_WINDOW_FUNCTIONS = (
+            exp.FirstValue,
+            exp.Lag,
+            exp.LastValue,
+            exp.Lead,
+            exp.NthValue,
+        )
+
         def show_sql(self, expression: exp.Show) -> str:
             return f"SHOW {expression.name}"
 
@@ -1098,11 +1098,21 @@ def unnest_sql(self, expression: exp.Unnest) -> str:
             return super().unnest_sql(expression)
 
         def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
-            if isinstance(expression.this, WINDOW_FUNCS_WITH_IGNORE_NULLS):
+            if isinstance(expression.this, self.IGNORE_RESPECT_NULLS_WINDOW_FUNCTIONS):
                 # 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)
 
+            self.unsupported("IGNORE NULLS is not supported for non-window functions.")
+            return self.sql(expression, "this")
+
+        def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
+            if isinstance(expression.this, self.IGNORE_RESPECT_NULLS_WINDOW_FUNCTIONS):
+                # DuckDB should render RESPECT NULLS only for the general-purpose
+                # window functions that accept it e.g. FIRST_VALUE(... RESPECT NULLS) OVER (...)
+                return super().respectnulls_sql(expression)
+
+            self.unsupported("RESPECT NULLS is not supported for non-window functions.")
             return self.sql(expression, "this")
 
         def arraytostring_sql(self, expression: exp.ArrayToString) -> str:


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 32359c46d9..bf18c62bcf 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -448,14 +448,13 @@ def test_bigquery(self):
             "SELECT SUM(x RESPECT NULLS) AS x",
             read={
                 "bigquery": "SELECT SUM(x RESPECT NULLS) AS x",
-                "duckdb": "SELECT SUM(x RESPECT NULLS) AS x",
                 "postgres": "SELECT SUM(x) RESPECT NULLS AS x",
                 "spark": "SELECT SUM(x) RESPECT NULLS AS x",
                 "snowflake": "SELECT SUM(x) RESPECT NULLS AS x",
             },
             write={
                 "bigquery": "SELECT SUM(x RESPECT NULLS) AS x",
-                "duckdb": "SELECT SUM(x RESPECT NULLS) AS x",
+                "duckdb": "SELECT SUM(x) AS x",
                 "postgres": "SELECT SUM(x) RESPECT NULLS AS x",
                 "spark": "SELECT SUM(x) RESPECT NULLS AS x",
                 "snowflake": "SELECT SUM(x) RESPECT NULLS AS x",
@@ -465,7 +464,7 @@ def test_bigquery(self):
             "SELECT PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()",
             write={
                 "bigquery": "SELECT PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()",
-                "duckdb": "SELECT QUANTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()",
+                "duckdb": "SELECT QUANTILE_CONT(x, 0.5) OVER ()",
                 "spark": "SELECT PERCENTILE_CONT(x, 0.5) RESPECT NULLS OVER ()",
             },
         )
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 56fcf76aac..4c4f0ba089 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -1,4 +1,5 @@
 from sqlglot import ErrorLevel, ParseError, UnsupportedError, exp, parse_one, transpile
+from sqlglot.generator import logger as generator_logger
 from sqlglot.helper import logger as helper_logger
 from sqlglot.optimizer.annotate_types import annotate_types
 from tests.dialects.test_dialect import Validator
@@ -1416,20 +1417,26 @@ def test_parameter_token(self):
 
     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
+        from sqlglot.dialects.duckdb import DuckDB
 
         agg_funcs = (exp.Sum, exp.Max, exp.Min)
 
-        for func_type in WINDOW_FUNCS_WITH_IGNORE_NULLS + agg_funcs:
+        for func_type in DuckDB.Generator.IGNORE_RESPECT_NULLS_WINDOW_FUNCTIONS + 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:
+            if func_type in DuckDB.Generator.IGNORE_RESPECT_NULLS_WINDOW_FUNCTIONS:
                 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"))
+                with self.assertLogs(generator_logger) as cm:
+                    self.assertEqual(ignore_null.sql("duckdb"), func.sql("duckdb"))
+                    self.assertNotIn("IGNORE NULLS", windowed_ignore_null.sql("duckdb"))
+
+                self.assertEqual(
+                    str(cm.output[0]),
+                    "WARNING:sqlglot:IGNORE NULLS is not supported for non-window functions.",
+                )
 
     def test_attach_detach(self):
         # ATTACH