Re: strange IS NULL behaviour

On Tue, Sep 10, 2013 at 10:50:32AM -0400, Bruce Momjian wrote:
> > 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.

Using your examples and others I have collected, I have created an SQL
script which shows our inconsistent behavior, attached, and its output.

If we agree that a single-level NULL inspection of ROWS is the right
approach, it would seem we need my patch, and we need to fix coalesce()
and NOT NULL constraint testing?  Is that accurate?  Is there more
areas?

Nested RECORDS seem to collapse to a single level, so I don't think we
have to change the recursion there:

    SELECT RECORD(RECORD(RECORD(NULL)));
     record
    --------
     (null)

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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