From: | Marti Raudsepp <marti(at)juffo(dot)org> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Allowing NOT IN to use ANTI joins |
Date: | 2014-06-11 09:32:48 |
Message-ID: | CABRT9RDtE+E0=VWm_aFvhfaQ1R+g54OOxs85y+KeYEgQmuYgWQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
Regards,
Marti
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2014-06-11 09:43:55 | Re: Allowing NOT IN to use ANTI joins |
Previous Message | Marti Raudsepp | 2014-06-11 09:17:03 | Re: Allowing NOT IN to use ANTI joins |