Re: strange IS NULL behaviour

On Tue, Sep 10, 2013 at 1:54 PM, Bruce Momjian <bruce@momjian.us> wrote:
> 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)


Also consider:
STRICT
PQisNull
EXCEPT/UNION
WHERE NOT IN (see how it plays with 'null gotcha')
etc

(but I don't think your lens should be focused on 'record recursion'
-- there is a deeper problem for which that is just a particular
symptom)

merlin