Postgres Pro
Downloads
Home   >   mailing lists

Thread: null != null ???

null != null ???

From
Fran Fabrizio
Date:
I have a situation where the left and right side of a where clause both
evaluate to null independently, but when compared to
each other, they don't equate.  Observe:

monitoring=# SELECT count(*) FROM current_status c WHERE c.subunit_id is
null;
 count
-------
     1
(1 row)

So there's one row in current_status that has a subunit_id equal to
null.  Good so far.

monitoring=# select
subunitId(NULL::varchar,unitId('MYSQL'::varchar,componentId('INFRASTRUCTURE'::varchar)));

 subunitid
-----------

(1 row)

monitoring=# select
subunitId(NULL::varchar,unitId('MYSQL'::varchar,componentId('INFRASTRUCTURE'::varchar)))
is null;
 ?column?
----------
 t
(1 row)

Ok, so my function call to subunitId evaluates to null.  Just to confirm
that it's returning null and not one or more blanks, the
second query indeed shows that it is equal to null.  Still good.
However....

monitoring=# SELECT count(*) FROM current_status c WHERE
c.subunit_id=subunitId(NULL::varchar,unitId('MYSQL'::varchar,componentId('INFRASTRUCTURE'::varchar)));

 count
-------
     0
(1 row)

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.  Just to confirm that null=null
evaluates to true (double-checking my sanity):

monitoring=# select null=null;
 ?column?
----------
 t
(1 row)

Ok, quite bizarre.  And now for the grand finale:

monitoring=# SELECT count(*) FROM current_status c WHERE c.subunit_id is
null AND
subunitId(NULL::varchar,unitId('MYSQL'::varchar,componentId('INFRASTRUCTURE'::varchar)))
is null;
 count
-------
     1
(1 row)

If you compare them independently to null, you get the match on one
row.  You compare them to each other, you get
no rows.  And yet, I've just shown that null=null should evaluate to
true.

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

-Fran

Re: null != null ???

From
Thomas Lockhart
Date:
...
> 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

Re: null != null ???

From
Tom Lane
Date:
Fran Fabrizio <ffabrizio@mmrd.com> writes:
> Can someone smarter than me tell me what I am missing?

Comparisons involving NULL yield NULL.  Any book on SQL should
explain this ...

            regards, tom lane

Re: null != null ???

From
Fran Fabrizio
Date:
> Fran Fabrizio <ffabrizio@mmrd.com> writes:
> > Can someone smarter than me tell me what I am missing?
>
> Comparisons involving NULL yield NULL.  Any book on SQL should
> explain this ...

...except 'select null=null' doesn't follow this rule (it yields 'true')
so one
can understand my confusion.  Thanks to Thomas for the explanation
and history of this interesting quirk.

-Fran

Re: null != null ???

From
Tom Lane
Date:
Thomas Lockhart <lockhart@fourpalms.org> writes:
> 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.

It should also be noted that as of 7.2, this transformation is optional
(see "transform_null_equals" runtime parameter), and the default is NOT
to make the transformation --- ie, as of 7.2 the default behavior is
SQL-spec-compliant:

regression=# select null = null;
 ?column?
----------

(1 row)

regression=# set transform_null_equals TO 1;
SET VARIABLE
regression=# select null = null;
 ?column?
----------
 t
(1 row)

regression=#

            regards, tom lane

Re: null != null ???

From
"Brett W. McCoy"
Date:
On Fri, 26 Oct 2001, Fran Fabrizio wrote:

> I have a situation where the left and right side of a where clause both
> evaluate to null independently, but when compared to
> each other, they don't equate.  Observe:

It seems to me that a null value cannot be compared to a null value
because NULL means 'no value' -- it would be like comparing two undefined
variables in Perl (undef) or two NULL pointers in C.  It will never be
true because you can't equate nothing to nothing.

-- Brett
                                          http://www.chapelperilous.net/
------------------------------------------------------------------------
God created a few perfect heads.  The rest he covered with hair.

Re: null != null ???

From
Rich Shepard
Date:
On Fri, 26 Oct 2001, Fran Fabrizio wrote:

> I have a situation where the left and right side of a where clause both
> evaluate to null independently, but when compared to
> each other, they don't equate.  Observe:

> 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

Fran,

  This is exactly how it should be. Instead of 'null' use the word
'unknown'. If you have two unknown values can you say whether or not they
are the same? Of course not! Therefore, comparisons of nulls is meaningless.

HTH,

Rich

Dr. Richard B. Shepard, President

                       Applied Ecosystem Services, Inc. (TM)
            2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
 + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
                         http://www.appl-ecosys.com

Re: null != null ???

From
Alvaro Herrera
Date:
On Fri, 26 Oct 2001, Rich Shepard wrote:

> On Fri, 26 Oct 2001, Fran Fabrizio wrote:
>
> > I have a situation where the left and right side of a where clause both
> > evaluate to null independently, but when compared to
> > each other, they don't equate.  Observe:

Maybe you can do the comparison by doing

SELECT (blah) IS NULL AND (blah2) IS NULL;

If that's what you want.

--
Alvaro Herrera (<alvherre[@]atentus.com>)
"La virtud es el justo medio entre dos defectos" (Aristoteles)