We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Window Function Bug — Vertica Forum

Window Function Bug

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)

 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file