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

Re: behavior of ' = NULL' vs. MySQL vs. Standards

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Mark Stosberg <mark(at)summersault(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: behavior of ' = NULL' vs. MySQL vs. Standards
Date: 2001-06-07 01:12:51
Message-ID: Pine.BSF.4.21.0106061809050.18346-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
On Wed, 6 Jun 2001, Mark Stosberg wrote:

> 
> Hello,
> 
> I'm a long time Postgres user who uses MySQL when I have to. I recently
> ran into an issue with MySQL where this construct didn't do what I expect:
> 
> WHERE date_column = NULL
> 
> I expected it to work like "date_column IS NULL" like it does it
> Postgres 7.0.2, but instead it returned an empty result set. 
> 
> After conversing with some folks on the MySQL list, it was mentioned that:
> 
>  * "NULL is *NOT* a value. It's an absence of a value, and doing *any*
> comparisons with NULL is invalid (the result must always be NULL, even
> if you say "foo = NULL")." 
> 
>  * Postgres handling is non-standard (even if it's intuitive.) 
> 
> My questions then are: 1.) What IS the standard for handling NULLs? and
> then 2.) If Postgres handling is different than the standard, what's the
> reason? 
> 
> To me, having " = NULL" be the same as " IS NULL" is intuitive and thus
> useful, but I also like appeal of using standards when possible. :) 

Yes, column = NULL should *never* return true according to the spec (it
should always return NULL in fact as stated).  The reason for breaking
with the spec is AFAIK to work with broken microsoft clients that seem to
think that =NULL is a meaningful test and generate queries using that.

In general, =NULL should be avoided in favor of IS NULL by users that are
generating their own queries.



In response to

Responses

pgsql-hackers by date

Next:From: Christopher Kings-LynneDate: 2001-06-07 02:10:12
Subject: RE: ORDER BY Problem...
Previous:From: Tatsuo IshiiDate: 2001-06-07 01:03:15
Subject: Re: psql bug or feature?

pgsql-sql by date

Next:From: Tom LaneDate: 2001-06-07 02:05:52
Subject: Re: [SQL] maximum number of rows in table - what about oid limits?
Previous:From: John ScottDate: 2001-06-07 01:08:14
Subject: Re: [SQL] maximum number of rows in table - what about oid limits?

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