Skip site navigation (1) Skip section navigation (2)

Re: null != null ???

From: Thomas Lockhart <lockhart(at)fourpalms(dot)org>
To: Fran Fabrizio <ffabrizio(at)mmrd(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: null != null ???
Date: 2001-10-26 15:52:30
Message-ID: 3BD986BE.81A735A4@fourpalms.org (view raw or flat)
Thread:
Lists: pgsql-general
...
> This is an odd result.  Both sides of that where clause evaluate to
> null, so it is null=null, yet this is not evaluating to true
> because I'm getting no rows back.

This is expected behavior. NULL means "unknown", so you can not know
that they are equivalent. But it is *not* the same as "NULL=NULL",
because...

>  Just to confirm that null=null
> evaluates to true (double-checking my sanity):
> monitoring=# select null=null;
> ----------
>  t
> (1 row)
> Ok, quite bizarre.

Yes, this is an explicit feature in our parser to help out poor MSAccess
souls who have a product which generates illegal SQL queries. We put in
a specific rule to convert "anything=NULL" to "anything IS NULL", which
*is* legal syntax.

Check the archives to discover that no one is very happy with this
feature, except folks trying to use ODBC via Access and some goofy forms
interface. Sorry that it has bit you, but basically you should not rely
on this hack at any time, and the behavior "expr=expr" returning false
when each expression evaluates to NULL conforms to SQL standards.

> Can someone smarter than me tell me what I am missing?

I may not be smarter, just "been there, done that"; hopefully you will
find the information useful anyway ;)

                  - Thomas

In response to

Responses

pgsql-general by date

Next:From: Vivek KheraDate: 2001-10-26 15:52:39
Subject: Re: DBD::Pg
Previous:From: Fran FabrizioDate: 2001-10-26 15:19:43
Subject: null != null ???

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group