Window Function Bug

selwyth

Tested on server v7.2.2-1.

 

-- Create sample data
CREATE TABLE test_window AS
SELECT '1' AS cola, 'A' AS colB
UNION ALL
SELECT '2' AS cola, 'A' AS colB
UNION ALL
SELECT '3' AS cola, 'B' AS colB
;

 

-- Here's what it looks like
SELECT * FROM test_window;
 cola | colB
------+------
 1    | A
 3    | B
 2    | A
(3 rows)

-- Expected behavior
SELECT cola, MAX(colb) OVER (PARTITION BY cola) AS max_colb FROM test_window;
 cola | max_colb
------+----------
 1    | A
 2    | A
 3    | B
(3 rows)

-- Note that I cannot use a column alias in the window function, as expected.
SELECT '1' AS colc, MAX(colb) OVER (PARTITION BY colc) AS max_colb FROM test_window;
ERROR:  Column "colc" does not exist

-- However, I can if the column alias is the same as a column in the table, and behavior is then different from intended, and diverges from postgresql behavior.
SELECT '1' AS cola, MAX(colb) OVER (PARTITION BY cola) AS max_colb FROM test_window;
 cola | max_colb
------+----------
 1    | B
 1    | B
 1    | B
(3 rows)