Merging upstream version 23.12.1.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 23 insertions, 13 deletions

diff --git a/sqlglot/transforms.py b/sqlglot/transforms.py
index 2bd02ec..ec2a0df 100644
--- a/sqlglot/transforms.py
+++ b/sqlglot/transforms.py
@@ -93,7 +93,9 @@ def eliminate_qualify(expression: exp.Expression) -> exp.Expression:
     Some dialects don't support window functions in the WHERE clause, so we need to include them as
     projections in the subquery, in order to refer to them in the outer filter using aliases. Also,
     if a column is referenced in the QUALIFY clause but is not selected, we need to include it too,
-    otherwise we won't be able to refer to it in the outer query's WHERE clause.
+    otherwise we won't be able to refer to it in the outer query's WHERE clause. Finally, if a
+    newly aliased projection is referenced in the QUALIFY clause, it will be replaced by the
+    corresponding expression to avoid creating invalid column references.
     """
     if isinstance(expression, exp.Select) and expression.args.get("qualify"):
         taken = set(expression.named_selects)
@@ -105,20 +107,31 @@ def eliminate_qualify(expression: exp.Expression) -> exp.Expression:
 
         outer_selects = exp.select(*[select.alias_or_name for select in expression.selects])
         qualify_filters = expression.args["qualify"].pop().this
+        expression_by_alias = {
+            select.alias: select.this
+            for select in expression.selects
+            if isinstance(select, exp.Alias)
+        }
 
         select_candidates = exp.Window if expression.is_star else (exp.Window, exp.Column)
-        for expr in qualify_filters.find_all(select_candidates):
-            if isinstance(expr, exp.Window):
+        for select_candidate in qualify_filters.find_all(select_candidates):
+            if isinstance(select_candidate, exp.Window):
+                if expression_by_alias:
+                    for column in select_candidate.find_all(exp.Column):
+                        expr = expression_by_alias.get(column.name)
+                        if expr:
+                            column.replace(expr)
+
                 alias = find_new_name(expression.named_selects, "_w")
-                expression.select(exp.alias_(expr, alias), copy=False)
+                expression.select(exp.alias_(select_candidate, alias), copy=False)
                 column = exp.column(alias)
 
-                if isinstance(expr.parent, exp.Qualify):
+                if isinstance(select_candidate.parent, exp.Qualify):
                     qualify_filters = column
                 else:
-                    expr.replace(column)
-            elif expr.name not in expression.named_selects:
-                expression.select(expr.copy(), copy=False)
+                    select_candidate.replace(column)
+            elif select_candidate.name not in expression.named_selects:
+                expression.select(select_candidate.copy(), copy=False)
 
         return outer_selects.from_(expression.subquery(alias="_t", copy=False), copy=False).where(
             qualify_filters, copy=False
@@ -336,13 +349,10 @@ def explode_to_unnest(index_offset: int = 0) -> t.Callable[[exp.Expression], exp
     return _explode_to_unnest
 
 
-PERCENTILES = (exp.PercentileCont, exp.PercentileDisc)
-
-
 def add_within_group_for_percentiles(expression: exp.Expression) -> exp.Expression:
     """Transforms percentiles by adding a WITHIN GROUP clause to them."""
     if (
-        isinstance(expression, PERCENTILES)
+        isinstance(expression, exp.PERCENTILES)
         and not isinstance(expression.parent, exp.WithinGroup)
         and expression.expression
     ):
@@ -358,7 +368,7 @@ def remove_within_group_for_percentiles(expression: exp.Expression) -> exp.Expre
     """Transforms percentiles by getting rid of their corresponding WITHIN GROUP clause."""
     if (
         isinstance(expression, exp.WithinGroup)
-        and isinstance(expression.this, PERCENTILES)
+        and isinstance(expression.this, exp.PERCENTILES)
         and isinstance(expression.expression, exp.Order)
     ):
         quantile = expression.this.this