| 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: | Whole Thread | Raw Message | 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 |