BUG #10256: COUNT(*) behaves sort of like RANK() when used over a window containing an ORDER BY
От | arnaud.mouronval@gmail.com |
---|---|
Тема | BUG #10256: COUNT(*) behaves sort of like RANK() when used over a window containing an ORDER BY |
Дата | |
Msg-id | 20140507181702.1397.66739@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #10256: COUNT(*) behaves sort of like RANK() when used over a window containing an ORDER BY
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 10256 Logged by: Arnaud Mouronval Email address: arnaud.mouronval@gmail.com PostgreSQL version: 9.3.4 Operating system: Windows 8.1 Description: I discovered a problem while using a window that used an ORDER BY clause, and using this window with ROW_NUMBER() and COUNT(*) at the same time. Here is a short SQL script to replicate it : DROP TABLE IF EXISTS tmp_count_window_bug_data; CREATE TABLE tmp_count_window_bug_data (c1 character varying(8), c2 character varying(8)); INSERT INTO tmp_count_window_bug_data (c1, c2) VALUES ('A', 'AA'), ('A', 'AB'), ('B', 'BA'), ('B', 'BB'), ('B', 'BC'), ('B', 'BC'), ('B', 'BD'); SELECT c1, COUNT(*) OVER(PARTITION BY c1), COUNT(*) OVER(PARTITION BY c1 ORDER BY c2) FROM tmp_count_window_bug_data; Result on my machine : "A";2;1 "A";2;2 "B";5;1 "B";5;2 "B";5;4 "B";5;4 "B";5;5 I was expecting to get the same values in the last 2 columns. As you can see, the third column looks much more like a RANK() (except RANK() would have answered 3 instead of 4 for 2 lines).
В списке pgsql-bugs по дате отправления: