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 <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers