On Wed, Jun 11, 2014 at 9:32 PM, Marti Raudsepp <ma...@juffo.org> wrote:
> 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. > > Thanks, I actually was just looking at that. I guess I'll just need to make sure that nothing in the targetlist comes from an outer join. Regards David Rowley