On Wed, Jul 29, 2009 at 9:40 AM, Sam Mason<sam@samason.me.uk> wrote:
> On Wed, Jul 29, 2009 at 01:15:27PM +0000, Jasen Betts wrote:
>> On 2009-07-23, Sam Mason <sam@samason.me.uk> wrote:
>> >
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types
>> >
>> > is scary; even worse is that it was changed to be like this in 8.2
>> > because the standard says it should behave this way. What on earth were
>> > they thinking when they defined the standard this way?
>>
>> since any comparson involving those tuples will return NULL true is the
>> correct value for IS NULL
>
> I think you missed the point:
>
> SELECT r IS NULL, r IS NOT NULL
> FROM (VALUES (1,NULL)) r(a,b);
>
> returns FALSE for *both* columns. How can a row be both NULL *and*
> non-NULL?
>
>> if you are bothered by this behavior you are misusing NULL.
>
> I understand that this is the specified behavior, and hence PG is
> correctly following the spec--but it still bothers me.
not only that, but while pg's treats composite types with null members
as null according to the 'is null' operator (in accordance with the
spec), but as not null everywhere else. thus, for example, a 'null'
composite type is counted in the count() aggregate function. how
funky is that?
merlin
