Re: Allowing NOT IN to use ANTI joins

On Wed, Jun 11, 2014 at 9:32 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:

> On Sun, Jun 8, 2014 at 3:36 PM, David Rowley <dgrowleyml(at)gmail(dot)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