From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Hannu Krosing <hannu(at)2ndquadrant(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>, Andrew Dunstan <andrew(at)dunslane(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: strange IS NULL behaviour |
Date: | 2013-09-10 14:12:08 |
Message-ID: | CAHyXU0z+uegE3nu_vaZCas4YYpwrL9YJeckemwbzbHdoGOO70g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Sep 10, 2013 at 8:56 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Tue, Sep 10, 2013 at 08:45:14AM -0400, Robert Haas wrote:
>> On Mon, Sep 9, 2013 at 3:51 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > The problem is that I don't believe this patch is commit-ready ---
>> > someone needs to research the IS NULL tests in all areas of our code to
>> > see if they match this patch, and I can't do that. Is that something a
>> > reviewer is going to be willing to do? I don't think I have ever seen a
>> > commit-fest item that still required serious research outside the patch
>> > area before committing. I could ask just for feedback, but I have
>> > already received enough feedback to know I can't get the patch to a
>> > ready-enough state.
>>
>> OK, well then there's probably not much point.
>
> FYI, I think these queries below prove that NOT NULL constraints do not
> follow the single-depth ROW NULL inspection rule that PL/pgSQL follows,
> and that my patch was trying to promote for queries:
>
> CREATE TABLE test2(x test NOT NULL);
> CREATE TABLE
> INSERT INTO test2 VALUES (null);
> ERROR: null value in column "x" violates not-null constraint
> DETAIL: Failing row contains (null).
> --> INSERT INTO test2 VALUES (row(null));
> INSERT 0 1
>
> So, in summary, NOT NULL constraints don't inspect into ROW values for
> NULLs, PL/pgSQL goes one level deep into ROW, and queries go two levels
> deep. I am not sure what other areas need checking.
Our composite null handling (as noted) is an absolute minefield of
issues. Consider:
postgres=# select coalesce(row(null,null), row('no', 'bueno'));
coalesce
----------
(,)
postgres=# select case when row(null,null) is null then row('no', 'bueno') end;
case
------------
(no,bueno)
It's just a mess. So it bears repeating: do we or do we not want to
implement SQL standard composite null handing? If so, you probably
have to hit all the targets. If not, I'd either A: leave things alone
or B: remove the special case logic in IS NULL (so that it behaves as
coalesce() does) and document our divergence from the standard. Point
being: B might actually be the best choice, but it should be
understood that we are not going in that direction before pushing
patches that go in the other direction.
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2013-09-10 14:25:47 | Patch to add support of "IF NOT EXISTS" to others "CREATE" statements |
Previous Message | Tom Lane | 2013-09-10 13:57:48 | Re: Custom Plan node |