arnaud.mouronval@gmail.com writes:
> 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
This looks correct to me. Keep in mind that the default window framing
clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which really
means "all rows up to and including the last sort peer of the current row".
With no ORDER BY clause, all rows in the partition are peers, so you get a
count covering the whole partition no matter which row you are on. With
an ORDER BY, you get a count of only the rows up to the current row (or
its last peer, when there are duplicate values in the ORDER BY columns).
> I was expecting to get the same values in the last 2 columns.
For that you'd want RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING.
regards, tom lane