The following bug has been logged on the website:
Bug reference: 15336
Logged by: Vladimir Baranoff
Email address: v.g.baranoff@gmail.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
