Repository: tobymao/sqlglot

Issue:
Aggregate null exclusion filter transpile does not handle DISTINCT
```python
sqlglot.parse_one("""SELECT DISTINCT ID, ANOTHER, TO_JSON(ARRAY_AGG(DISTINCT COL) OVER (PARTITION BY ID)) AS COLS FROM (SELECT COLUMN1 AS ID, COLUMN2 AS COL, COLUMN3 AS ANOTHER FROM (VALUES (1, 's1', 'c1'), (1, 's2', 'c1'), (1, 's3', 'c1'), (2, 's1', 'c2'), (2, 's2', 'c2')) AS _("COLUMN1", "COLUMN2", "COLUMN3")) ORDER BY ID;""", read="snowflake").sql(dialect="duckdb")
```

Produces:
```sql
SELECT DISTINCT ID, ANOTHER, TO_JSON(ARRAY_AGG(DISTINCT COL) FILTER(WHERE DISTINCT COL IS NOT NULL) OVER (PARTITION BY ID)) AS COLS FROM (SELECT COLUMN1 AS ID, COLUMN2 AS COL, COLUMN3 AS ANOTHER FROM (VALUES (1, \'s1\', \'c1\'), (1, \'s2\', \'c1\'), (1, \'s3\', \'c1\'), (2, \'s1\', \'c2\'), (2, \'s2\', \'c2\')) AS _("COLUMN1", "COLUMN2", "COLUMN3")) ORDER BY ID
```

Which fails in duckdb:

```
Parser Error: syntax error at or near "DISTINCT"
LINE 1: ...N(ARRAY_AGG(DISTINCT COL) FILTER(WHERE DISTINCT COL IS NOT NULL) OVER (PARTITI...
```


Introduced by #4033 


Discussion:

Patch:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 23b19b48..f0b13763 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -4323,6 +4323,13 @@ class Generator(metaclass=_Generator):
                 parent_cond = parent.expression.this
                 parent_cond.replace(parent_cond.and_(expression.this.is_(exp.null()).not_()))
             else:
-                array_agg = f"{array_agg} FILTER(WHERE {self.sql(expression, 'this')} IS NOT NULL)"
+                # DISTINCT is already present in the agg function, do not propagate it to FILTER as well
+                this = expression.this
+                this_sql = (
+                    self.expressions(this)
+                    if isinstance(this, exp.Distinct)
+                    else self.sql(expression, "this")
+                )
+                array_agg = f"{array_agg} FILTER(WHERE {this_sql} IS NOT NULL)"
 
         return array_agg
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index a47ca8eb..a2180357 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -4343,6 +4343,9 @@ class Parser(metaclass=_Parser):
             )
 
         while self._match_set(self.ASSIGNMENT):
+            if isinstance(this, exp.Column) and len(this.parts) == 1:
+                this = this.this
+
             this = self.expression(
                 self.ASSIGNMENT[self._prev.token_type],
                 this=this,


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 2887dd0f..ad503ae9 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -295,6 +295,9 @@ class TestDuckDB(Validator):
         self.validate_identity("SUMMARIZE tbl").assert_is(exp.Summarize)
         self.validate_identity("SUMMARIZE SELECT * FROM tbl").assert_is(exp.Summarize)
         self.validate_identity("CREATE TABLE tbl_summary AS SELECT * FROM (SUMMARIZE tbl)")
+        self.validate_identity("UNION_VALUE(k1 := 1)").find(exp.PropertyEQ).this.assert_is(
+            exp.Identifier
+        )
         self.validate_identity(
             "SELECT species, island, COUNT(*) FROM t GROUP BY GROUPING SETS (species), GROUPING SETS (island)"
         )
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 40413c82..1e412b04 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -755,6 +755,8 @@ WHERE
             write={
                 "spark": "SELECT COLLECT_LIST(DISTINCT a)",
                 "snowflake": "SELECT ARRAY_AGG(DISTINCT a)",
+                "duckdb": "SELECT ARRAY_AGG(DISTINCT a) FILTER(WHERE a IS NOT NULL)",
+                "presto": "SELECT ARRAY_AGG(DISTINCT a) FILTER(WHERE a IS NOT NULL)",
             },
         )
         self.validate_all(