From: | arnaud(dot)mouronval(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #10256: COUNT(*) behaves sort of like RANK() when used over a window containing an ORDER BY |
Date: | 2014-05-07 18:17:02 |
Message-ID: | 20140507181702.1397.66739@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 10256
Logged by: Arnaud Mouronval
Email address: arnaud(dot)mouronval(at)gmail(dot)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).
From | Date | Subject | |
---|---|---|---|
Next Message | Emanuel Calvo | 2014-05-07 19:55:50 | Re: BUG #10255: CREATE COLLATION bug on 9.4 |
Previous Message | Tom Lane | 2014-05-07 17:39:43 | Re: Server process crash - Segmentation fault |