Unparsing of Window functions is generating incorrect queries #17360
Description
Describe the bug
The unparsing of Window functions on optimized plans is generating incorrect queries. The reason for this is that the projection on Window columns is optimized away, meaning the final query will use the entire function as the column name.
To Reproduce
Example:
-- original (the window function is used both in the filter and in the projection)
select k, v, r
from (
select *, rank() over(partition by k order by v) as r
from t
) t
where r = 1
-- unoptimized plan (the window function is projected as "r", which is used by the filter and the projection)
Projection: t.k, t.v, t.r
Filter: t.r = Int64(1)
SubqueryAlias: t
Projection: t.k, t.v, rank() PARTITION BY [t.k] ORDER BY [t.v ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS r
WindowAggr: windowExpr=[[rank() PARTITION BY [t.k] ORDER BY [t.v ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
TableScan: t
-- optimized plan (the projection is optimized out, the filter and projection refer to the window function directly by the original code)
SubqueryAlias: t
Projection: t.k, t.v, rank() PARTITION BY [t.k] ORDER BY [t.v ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS r
Filter: rank() PARTITION BY [t.k] ORDER BY [t.v ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = UInt64(1)
WindowAggr: windowExpr=[[rank() PARTITION BY [t.k] ORDER BY [t.v ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
TableScan: t projection=[k, v]
-- invalid filter
SELECT * FROM (
SELECT t.k, t.v, rank() OVER (PARTITION BY t.k ORDER BY t.v ASC NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS r
FROM t
WHERE ("rank() PARTITION BY [t.k] ORDER BY [t.v ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" = 1)
) AS tExpected behavior
Return valid SQL.
Additional context
Also failing with the QUALIFY statement:
-- original
select *, rank() over(partition by k order by v) as r
from t
qualify r = 1;
-- optimized plan
Projection: t.k, t.v, rank() PARTITION BY [t.k] ORDER BY [t.v ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS r
Filter: rank() PARTITION BY [t.k] ORDER BY [t.v ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = UInt64(1)
WindowAggr: windowExpr=[[rank() PARTITION BY [t.k] ORDER BY [t.v ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
TableScan: t projection=[k, v]
-- output
SELECT t.k, t.v, rank() OVER (PARTITION BY t.k ORDER BY t.v ASC NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS r
FROM t
WHERE ("rank() PARTITION BY [t.k] ORDER BY [t.v ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" = 1)