In the regression database:
regression=# select distinct on (ten) ten, thousand from tenk1 order by ten,
thousand;
ten | thousand
-----+----------
0 | 0
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
(10 rows)
This is correct, but watch this:
regression=# begin;
BEGIN
regression=# declare c cursor for
regression-# select distinct on (ten) ten, thousand from tenk1 order by ten,
thousand;
DECLARE CURSOR
regression=# fetch forward all in c;
ten | thousand
-----+----------
0 | 0
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
(10 rows)
regression=# fetch backward all in c;
ten | thousand
-----+----------
9 | 999
8 | 998
7 | 997
6 | 996
5 | 995
4 | 994
3 | 993
2 | 992
1 | 991
0 | 990
(10 rows)
This happens in all supported releases (and even further back;
it's broken in 7.1 which is the oldest release I have running
at the moment).
The reason is that nodeUnique claims to support backwards scan, but
what it actually delivers during backwards scanning is the last
tuple (the first-encountered one) from each group, not the first
tuple (the last-encountered one) as would be needed to maintain
consistency with the forward scan direction.
We could probably fix this by complicating the logic in ExecUnique,
but I wonder whether it wouldn't be better to just stop treating
Unique nodes as backwards-scannable. The only reason for that
node type to exist (as opposed to using Group nodes) is that it's
simple and low-overhead. So complicating it to support a corner case
that no one has noticed in many years might be counterproductive.
Thoughts?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
