|From:||Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>|
|To:||Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>|
|Cc:||emre(at)hasegeli(dot)com, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||Re: constraint exclusion and nulls in IN (..) clause|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
> I think it'd make more sense to see about incorporating that idea in
After further thought, it seems like the place to deal with this is
really operator_predicate_proof(), as in the attached draft patch
against HEAD. This passes the smell test for me, in the sense that
it's an arguably correct and general extension of the proof rules,
but it could use more testing.
TBH, the change in the existing regression test case in inherit.sql
makes me itch. We've got
create table list_parted (
) partition by list (a);
create table part_null_xy partition of list_parted for values in (null, 'xy');
explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd');
Now, the fact that "null" is included in this query's IN clause is a
complete no-op, because the IN is using a strict equality operator.
So it's nice that the planner can see that and realize that there's
no point in scanning part_null_xy ... but this means that the syntax
that's been chosen for list partitioning is seriously misleading.
"in (null, 'xy')" in the CREATE TABLE command has nothing to do with
the semantics of that identical clause in any other context, and indeed
it seems chosen in a way to confuse even (or especially?) seasoned
I suppose it's too late to do anything about that now, but it sure
seems like NULL should've been handled some other way.
regards, tom lane
|Next Message||Fabien COELHO||2018-03-10 06:45:03||Re: csv format for psql|
|Previous Message||David G. Johnston||2018-03-10 02:58:10||Re: csv format for psql|