Re: BUG #6701: IS NOT NULL doesn't work on complex composites

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org, "Rikard Pavelic" <rikard(dot)pavelic(at)zg(dot)htnet(dot)hr>
Subject: Re: BUG #6701: IS NOT NULL doesn't work on complex composites
Date: 2012-06-21 18:23:52
Message-ID: 29240.1340303032@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> It's not clear to me whether the SQL standard rules on what should
>> happen in this case, or whether we should listen to it if it does
>> say that these values are not distinct. They certainly *look*
>> distinct.

> I do sympathize with the point of view that a row value about which
> absolutely no applicable facts are known is a lot like not knowing
> what row you have, but they do seem distinct when you look at the
> output.

>> (Oh, and dare I mention arrays of nulls?)

> Hey, look! An elephant!

The reason I mentioned arrays is that it seems clear to me that nobody
sane would consider ARRAY[NULL,NULL]::int[] to be equivalent to
NULL::int[]. The former has got well-defined array dimensions, for one
thing, while the latter does not. So I think the standard is not being
very bright by conflating a null container with a container full of
nulls in the ROW case. I'm willing to hold my nose and do what they say
for the specific case of "foo IS NULL" and "foo IS NOT NULL"
expressions, but I'm not eager to let that confusion propagate anyplace
else.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2012-06-21 19:27:53 Re: BUG #6701: IS NOT NULL doesn't work on complex composites
Previous Message Kevin Grittner 2012-06-21 17:43:18 Re: BUG #6701: IS NOT NULL doesn't work on complex composites