Repository: tobymao/sqlglot

Issue:
DuckDB: WINDOW and QUALIFY clauses get reversed
I am using sqlmesh with duckdb and running into an issue where optimized queries coming out of sqlglot are causing syntax errors once the query reaches the database engine. 

So the read and write dialect are both duckdb. 

A simplified form of the input query is:

```
select 
    *,
    lead(x) over w as next_x, 
    lag(x) over w as prev_x
from mytable
window w (partition by entity_id order by created_at)
qualify type = 'a'
```

And the resulting query that is actually passed to duckdb is:

```
SELECT
  *,
  LEAD("x") OVER "w" AS "next_x",
  LAG("x") OVER "w" AS "prev_x"
FROM "mytable" AS "mytable"
QUALIFY
  "type" = 'a'
WINDOW "w" AS (PARTITION BY "entity_id" ORDER BY "created_at")
```

This causes a syntax error because [QUALIFY comes after WINDOW](https://duckdb.org/docs/sql/query_syntax/qualify.html) in duckdb.  

As a workaround, I am omitting `WINDOW` for now and simply repeating the window definition in the select list for now. 


Discussion:
georgesittas: > are other dialects reversed? only duckdb is like this?

I checked them, apparently CH [added support](https://clickhouse.com/docs/en/sql-reference/statements/select) for `QUALIFY` and so `eliminate_qualify` is not needed anymore. FYI @cpcloud, the time that transformation was [added](https://github.com/tobymao/sqlglot/commit/090724da9ed7a9c1c0b8024b68340a15b0b64ff0), there was no `QUALIFY` (see old docs [snapshot](https://web.archive.org/web/20231024155133/https://clickhouse.com/docs/en/sql-reference/statements/select)).


For this reason I refactored the base `Generator` mapping to have the order `WINDOW` -> `QUALIFY` which applies to both CH and DuckDB based on my findings, and overrode it in BigQuery to preserve the correct order:

<img width="637" alt="Screenshot 2024-07-06 at 11 50 36 PM" src="https://github.com/tobymao/sqlglot/assets/46752250/067e416a-4eb1-4363-b4c2-fb8193318869">
<img width="606" alt="Screenshot 2024-07-06 at 11 50 53 PM" src="https://github.com/tobymao/sqlglot/assets/46752250/fa70684f-d82f-4ee5-b1ed-aab30eacef65">

The remaining dialects don't seem to support both `QUALIFY` and `WINDOW`.

tobymao: snowflake is also window qualify right?
georgesittas: > snowflake is also window qualify right?

Snowflake doesn't seem to support `WINDOW`. Checked both docs and tried this:

```sql
-- Syntax error: unexpected 'WINDOW'. (line 1)
SELECT schema_name, function_name FROM DUCKDB_FUNCTIONS t WINDOW my_window AS (PARTITION BY schema_name ORDER BY function_name) QUALIFY ROW_NUMBER() OVER my_window < 3
```
tobymao: sounds good

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index fd03798b..e6fcf356 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -735,6 +735,13 @@ class BigQuery(Dialect):
             exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
         }
 
+        # WINDOW comes after QUALIFY
+        # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#window_clause
+        AFTER_HAVING_MODIFIER_TRANSFORMS = {
+            "qualify": generator.Generator.AFTER_HAVING_MODIFIER_TRANSFORMS["qualify"],
+            "windows": generator.Generator.AFTER_HAVING_MODIFIER_TRANSFORMS["windows"],
+        }
+
         # from: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#reserved_keywords
         RESERVED_KEYWORDS = {
             "all",
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 75217499..3841ea64 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -2,7 +2,7 @@ from __future__ import annotations
 
 import typing as t
 
-from sqlglot import exp, generator, parser, tokens, transforms
+from sqlglot import exp, generator, parser, tokens
 from sqlglot.dialects.dialect import (
     Dialect,
     arg_max_or_min_no_count,
@@ -789,7 +789,6 @@ class ClickHouse(Dialect):
             exp.Quantile: _quantile_sql,
             exp.RegexpLike: lambda self, e: self.func("match", e.this, e.expression),
             exp.Rand: rename_func("randCanonical"),
-            exp.Select: transforms.preprocess([transforms.eliminate_qualify]),
             exp.StartsWith: rename_func("startsWith"),
             exp.StrPosition: lambda self, e: self.func(
                 "position", e.this, e.args.get("substr"), e.args.get("position")
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index c2a6c557..482e5f34 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -406,13 +406,13 @@ class Generator(metaclass=_Generator):
     AFTER_HAVING_MODIFIER_TRANSFORMS = {
         "cluster": lambda self, e: self.sql(e, "cluster"),
         "distribute": lambda self, e: self.sql(e, "distribute"),
-        "qualify": lambda self, e: self.sql(e, "qualify"),
         "sort": lambda self, e: self.sql(e, "sort"),
         "windows": lambda self, e: (
             self.seg("WINDOW ") + self.expressions(e, key="windows", flat=True)
             if e.args.get("windows")
             else ""
         ),
+        "qualify": lambda self, e: self.sql(e, "qualify"),
     }
 
     TOKEN_MAPPING: t.Dict[TokenType, str] = {}


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index ef84d488..a31cc24a 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -83,6 +83,9 @@ class TestClickhouse(Validator):
         self.validate_identity("TRUNCATE TABLE t1 ON CLUSTER test_cluster")
         self.validate_identity("TRUNCATE DATABASE db")
         self.validate_identity("TRUNCATE DATABASE db ON CLUSTER test_cluster")
+        self.validate_identity(
+            "SELECT number, COUNT() OVER (PARTITION BY number % 3) AS partition_count FROM numbers(10) WINDOW window_name AS (PARTITION BY number) QUALIFY partition_count = 4 ORDER BY number"
+        )
         self.validate_identity(
             "SELECT id, quantileGK(100, 0.95)(reading) OVER (PARTITION BY id ORDER BY id RANGE BETWEEN 30000 PRECEDING AND CURRENT ROW) AS window FROM table"
         )
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index c0afb2f4..44839e9e 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -2267,7 +2267,7 @@ SELECT
             write={
                 "duckdb": "SELECT * FROM t QUALIFY COUNT(*) OVER () > 1",
                 "snowflake": "SELECT * FROM t QUALIFY COUNT(*) OVER () > 1",
-                "clickhouse": "SELECT * FROM (SELECT *, COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1",
+                "clickhouse": "SELECT * FROM t QUALIFY COUNT(*) OVER () > 1",
                 "mysql": "SELECT * FROM (SELECT *, COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1",
                 "oracle": "SELECT * FROM (SELECT *, COUNT(*) OVER () AS _w FROM t) _t WHERE _w > 1",
                 "postgres": "SELECT * FROM (SELECT *, COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1",
@@ -2279,7 +2279,7 @@ SELECT
             write={
                 "duckdb": 'SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id" FROM t QUALIFY COUNT(*) OVER () > 1',
                 "snowflake": 'SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id" FROM t QUALIFY COUNT(*) OVER () > 1',
-                "clickhouse": 'SELECT "user id", some_id, other_id, "2 nd id" FROM (SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id", COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1',
+                "clickhouse": 'SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id" FROM t QUALIFY COUNT(*) OVER () > 1',
                 "mysql": "SELECT `user id`, some_id, other_id, `2 nd id` FROM (SELECT `user id`, some_id, 1 AS other_id, 2 AS `2 nd id`, COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1",
                 "oracle": 'SELECT "user id", some_id, other_id, "2 nd id" FROM (SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id", COUNT(*) OVER () AS _w FROM t) _t WHERE _w > 1',
                 "postgres": 'SELECT "user id", some_id, other_id, "2 nd id" FROM (SELECT "user id", some_id, 1 AS other_id, 2 AS "2 nd id", COUNT(*) OVER () AS _w FROM t) AS _t WHERE _w > 1',
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index e0b0131f..395cbe65 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -361,6 +361,10 @@ class TestDuckDB(Validator):
         self.validate_identity(
             "SELECT * FROM (PIVOT Cities ON Year USING SUM(Population) GROUP BY Country) AS pivot_alias"
         )
+        self.validate_identity(
+            # QUALIFY comes after WINDOW
+            "SELECT schema_name, function_name, ROW_NUMBER() OVER my_window AS function_rank FROM DUCKDB_FUNCTIONS() WINDOW my_window AS (PARTITION BY schema_name ORDER BY function_name) QUALIFY ROW_NUMBER() OVER my_window < 3"
+        )
         self.validate_identity("DATE_SUB('YEAR', col, '2020-01-01')").assert_is(exp.Anonymous)
         self.validate_identity("DATESUB('YEAR', col, '2020-01-01')").assert_is(exp.Anonymous)