Re: strange IS NULL behaviour

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@momjian.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 TABLEINSERT INTO test2 VALUES (null);ERROR:  null value in column "x"
violatesnot-null constraintDETAIL:  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.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +