Re: strange IS NULL behaviour

On Tue, Sep 10, 2013 at 09:12:08AM -0500, Merlin Moncure wrote:
> > 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

Wow, OK, more inconsistent places. :-(

> implement SQL standard composite null handing? If so, you probably

I am unclear if section 8.7, Null Predicate, in the SQL 2003 standard is
talking about just IS NULL, or all NULL tests.

> 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.

I see. So going one-level deep in the ROW NULL inspection is something
we do for IS NULL in queries (actually double-deep inspection)q, but it
was never consistently implemented across all NULL tests.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +