On Sun, Jun 8, 2014 at 3:36 PM, David Rowley <dgrowle...@gmail.com> wrote: > Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS > queries and leaves NOT IN alone. The reason for this is because the values > returned by a subquery in the IN clause could have NULLs.
There's a bug in targetListIsGuaranteedNotToHaveNulls, you have to drill deeper into the query to guarantee the nullability of a result column. If a table is OUTER JOINed, it can return NULLs even if the original column specification has NOT NULL. This test case produces incorrect results with your patch: create table a (x int not null); create table b (x int not null, y int not null); insert into a values(1); select * from a where x not in (select y from a left join b using (x)); Unpatched version correctly returns 0 rows since "y" will be NULL. Your patch returns the value 1 from a. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
