BUG #15336: Wrong cursor's bacward fetch results in select with ALL(subquery)

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: v(dot)g(dot)baranoff(at)gmail(dot)com
Subject: BUG #15336: Wrong cursor's bacward fetch results in select with ALL(subquery)
Date: 2018-08-17 09:28:41
Message-ID: 153449812167.1304.1741624125628126322@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: 15336
Logged by: Vladimir Baranoff
Email address: v(dot)g(dot)baranoff(at)gmail(dot)com
PostgreSQL version: 10.0
Operating system: Ubuntu 18.04
Description:

create table longs (value int8 ) with ( oids=false );
insert into longs (value) values (1), (2), (3), (4), (5), (6), (7), (8),
(9);

Now fetch results with the cursor in forward direction:

begin;
declare "mycursor" binary scroll cursor for
select "l".value as "column1"
from longs as "l"
where "l".value <> all( select 5 union all select 6 union all select 7)
;
move absolute 0 in "mycursor";
fetch forward 9 from "mycursor";
commit;
The result set is correct (1, 2, 3, 4, 8, 9).

Then execute the following script:

begin;
declare "mycursor" binary scroll cursor for
select "l".value as "column1"
from longs as "l"
where "l".value <> all ( select 5 union all select 6 union all select 7
)
;
move absolute 10 in "mycursor";
fetch backward 9 from "mycursor";
commit;
The result set is wrong (9, 8, 7, 6, 5, 4, 3, 2, 1).
It seems that selection predicate is ignored.
Replacing ALL(subquery) by NOT IN (subquery) solves the problem, but this
violates statement that "NOT IN is equivalent to <> ALL.".
This bug has been reproduced with PostgreSQL 9.6 and 10.0

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Piotr Stegmann 2018-08-17 09:35:32 sql_inheritance
Previous Message Amit Langote 2018-08-17 08:46:44 Re: BUG #15334: Partition elimination not working as expected when using enum as partition key