Repository: tobymao/sqlglot
Issue category: Bug Report

Wrong result for transpiling `OVER (ORDER BY .. )` from `trino` to `bigquery` 
**Fully reproducible code snippet**
It can be reproduced by the following code:
```python
import sqlglot

sql = """
WITH t1 as (
  select 1 f1, 2 f2 union all select 2 f1, 4 f2 union all select 3 f1, 6 f2
)
select sum(f1) over (order by f2) from t1;
"""
result = sqlglot.transpile(sql, read="trino", write="bigquery")[0]
print(result)
# the output is
# WITH t1 AS (SELECT 1 AS f1, 2 AS f2 UNION ALL SELECT 2 AS f1, 4 AS f2 UNION ALL SELECT 3 AS f1, 6 AS f2)
# SELECT SUM(f1) OVER (ORDER BY f2 NULLS LAST) FROM t1
```
This SQL isn't valid for BigQuery. It will get the error message from BigQuery:
```
NULLS LAST not supported with ascending sort order in RANGE clauses of analytic functions.
```
It seems that `NULLS LAST` shouldn't be added in the `OVER` clause.

If I don't assign the `read` dialect, it works well.
```python
import sqlglot

sql = """
WITH t1 as (
  select 1 f1, 2 f2 union all select 2 f1, 4 f2 union all select 3 f1, 6 f2
)
select sum(f1) over (order by f2) from t1;
"""
result = sqlglot.transpile(sql, read=None, write="bigquery")[0]
print(result)
# WITH t1 AS (SELECT 1 AS f1, 2 AS f2 UNION ALL SELECT 2 AS f1, 4 AS f2 UNION ALL SELECT 3 AS f1, 6 AS f2)
# SELECT SUM(f1) OVER (ORDER BY f2) FROM t1
```

**Official Documentation**
[Window function syntax](https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls#syntax)


Functions to edit:
sqlglot/generator.py:Generator.ordered_sql

Patch:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 842df5a753..9aee03c1ca 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -2399,18 +2399,21 @@ def ordered_sql(self, expression: exp.Ordered) -> str:
                     f"'{nulls_sort_change.strip()}' translation not supported in window functions"
                 )
                 nulls_sort_change = ""
-            elif (
-                self.NULL_ORDERING_SUPPORTED is False
-                and (isinstance(expression.find_ancestor(exp.AggFunc, exp.Select), exp.AggFunc))
-                and (
-                    (asc and nulls_sort_change == " NULLS LAST")
-                    or (desc and nulls_sort_change == " NULLS FIRST")
-                )
+            elif self.NULL_ORDERING_SUPPORTED is False and (
+                (asc and nulls_sort_change == " NULLS LAST")
+                or (desc and nulls_sort_change == " NULLS FIRST")
             ):
-                self.unsupported(
-                    f"'{nulls_sort_change.strip()}' translation not supported for aggregate functions with {sort_order} sort order"
-                )
-                nulls_sort_change = ""
+                # BigQuery does not allow these ordering/nulls combinations when used under
+                # an aggregation func or under a window containing one
+                ancestor = expression.find_ancestor(exp.AggFunc, exp.Window, exp.Select)
+
+                if isinstance(ancestor, exp.Window):
+                    ancestor = ancestor.this
+                if isinstance(ancestor, exp.AggFunc):
+                    self.unsupported(
+                        f"'{nulls_sort_change.strip()}' translation not supported for aggregate functions with {sort_order} sort order"
+                    )
+                    nulls_sort_change = ""
             elif self.NULL_ORDERING_SUPPORTED is None:
                 if expression.this.is_int:
                     self.unsupported(

Test patch: diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 80a5dcc387..ec16dba243 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -2131,6 +2131,16 @@ def test_null_ordering(self):
                 },
             )
 
+            self.validate_all(
+                f"SELECT SUM(f1) OVER (ORDER BY f2 {sort_order}) FROM t",
+                read={
+                    "": f"SELECT SUM(f1) OVER (ORDER BY f2 {sort_order} {null_order}) FROM t",
+                },
+                write={
+                    "bigquery": f"SELECT SUM(f1) OVER (ORDER BY f2 {sort_order}) FROM t",
+                },
+            )
+
     def test_json_extract(self):
         self.validate_all(
             """SELECT JSON_QUERY('{"class": {"students": []}}', '$.class')""",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 13caf1b71f..a9d6330228 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -79,7 +79,7 @@ def test_duckdb(self):
         self.validate_all(
             "SELECT SUM(X) OVER (ORDER BY x)",
             write={
-                "bigquery": "SELECT SUM(X) OVER (ORDER BY x NULLS LAST)",
+                "bigquery": "SELECT SUM(X) OVER (ORDER BY x)",
                 "duckdb": "SELECT SUM(X) OVER (ORDER BY x)",
                 "mysql": "SELECT SUM(X) OVER (ORDER BY CASE WHEN x IS NULL THEN 1 ELSE 0 END, x)",
             },