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

Hints from issue comments:

Solution patch:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 23b19b4899..f0b13763bd 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -4323,6 +4323,13 @@ def arrayagg_sql(self, expression: exp.ArrayAgg) -> str:
                 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 a47ca8ebbc..a218035733 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -4343,6 +4343,9 @@ def _parse_assignment(self) -> t.Optional[exp.Expression]:
             )
 
         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 2887dd0f1d..ad503ae955 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -295,6 +295,9 @@ def test_duckdb(self):
         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 40413c8215..1e412b04aa 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -755,6 +755,8 @@ def test_snowflake(self):
             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(