From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-bugs(at)postgreSQL(dot)org |
Subject: | Hmm, nodeUnique doesn't really support backwards scan too well |
Date: | 2008-08-05 17:07:11 |
Message-ID: | 10436.1217956031@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2008-08-05 22:03:32 | Re: Hmm, nodeUnique doesn't really support backwards scan too well |
Previous Message | Hiroshi Saito | 2008-08-05 15:15:35 | Re: BUG #4186: set lc_messages does not work |