BUG #10256: COUNT(*) behaves sort of like RANK() when used over a window containing an ORDER BY

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).

Responses

Browse pgsql-bugs by date

  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