
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. :) 

Thanks!

  -mark

http://mark.stosberg.com/

Wed, 06 Jun 2001 20:00:45 -0500
Mark Stosberg <mark@summersault.com>
behavior of ' = NULL' vs. MySQL vs. Standards
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.



Wed, 6 Jun 2001 18:12:51 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: behavior of ' = NULL' vs. MySQL vs. Standards
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> 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.

Microsoft Access is the guilty party, IIRC.  I recently tried to stir up
some interest in changing this behavior back to the standard, but
apparently there are still too many people using broken versions of
Access.

A compromise answer might be to offer a SET variable that selects the
Microsoft-compatible misimplementation.  Would that fly?

			regards, tom lane

Wed, 06 Jun 2001 22:19:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: behavior of ' = NULL' vs. MySQL vs. Standards 

On Wed, 6 Jun 2001, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > 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.
> 
> Microsoft Access is the guilty party, IIRC.  I recently tried to stir up
> some interest in changing this behavior back to the standard, but
> apparently there are still too many people using broken versions of
> Access.
> 
> A compromise answer might be to offer a SET variable that selects the
> Microsoft-compatible misimplementation.  Would that fly?

It would for me.  I'd rather have the default be the spec correct behavior
and let people configure their server to follow the misinterpretation.
Is the conversion just the hack in the grammar rules for 
a_expr '=' a_expr?



Wed, 6 Jun 2001 20:12:46 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: behavior of ' = NULL' vs. MySQL vs. Standards 
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>> A compromise answer might be to offer a SET variable that selects the
>> Microsoft-compatible misimplementation.  Would that fly?

> It would for me.  I'd rather have the default be the spec correct behavior
> and let people configure their server to follow the misinterpretation.

Yes, I agree.

> Is the conversion just the hack in the grammar rules for 
> a_expr '=' a_expr?

AFAIK that's it.  It might be a little tricky to do cleanly,
though, since gram.y shouldn't look at SET vars.  Really, the
conversion would have to be done in analyze.c instead.

			regards, tom lane

Thu, 07 Jun 2001 06:00:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: behavior of ' = NULL' vs. MySQL vs. Standards 
> > 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.
> Microsoft Access is the guilty party, IIRC.  I recently tried to stir up
> some interest in changing this behavior back to the standard, but
> apparently there are still too many people using broken versions of
> Access.

Since according to the standard "column = NULL" is a near-useless
construct (equivalent to "FALSE") it does not seem to pollute the
grammar much to allow an M$ compatible interpretation. I was not happy
having it added (much better to ask that responsive, customer-focused
company to fix their language compliance) but now that it is there it
seems to be an isolated and manageable feature.

                      - Thomas

Thu, 07 Jun 2001 13:41:32 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: behavior of ' = NULL' vs. MySQL vs. Standards
Stephan Szabo wrote:
> 
> On Wed, 6 Jun 2001, Tom Lane wrote:
> 
> > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > > 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.

> I'd rather have the default be the spec correct behavior
> and let people configure their server to follow the misinterpretation.

I like that idea as well. Someone like me who didn't know that this
feature was in there for M$ could have assumed it _was_ standard
behavior, and started using it as a habit. Then when I started porting
my code to another database, I'd have an extra surprise in for me. :) 

Rather than being an option targeted at just this piece of grammer,
perhaps it could a piece of a potentially larger option of "stricter
standards compliance." I realize there are a number of useful extensions
to the SQL standard in Postgres (which I like and use.), but it seems
like there would be uses for minimizing non-standard behavior, as well. 

Thank you all for your contributions to Postgres-- I use it everyday. :) 

  -mark

http://mark.stosberg.com/

Thu, 07 Jun 2001 09:40:40 -0500
Mark Stosberg <mark@summersault.com>
Re: behavior of ' = NULL' vs. MySQL vs. Standards
Interesting - my experience is that Access, at least, generally treats
NULL's correctly:

(This was done under Access 2000):
create table foo (name text(20))
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values (NULL);
insert into foo values (NULL);
insert into foo values (NULL);
insert into foo values (NULL);


select count(*) from foo where name=NULL;
returns 0

select count(*) from foo where name is null;
returns 4

select count(*) from foo where name <> "bar";
returns 0

Cheers,
Andy

---------------------------------------------------------
   Andrew J. Perrin - Assistant Professor of Sociology
        University of North Carolina, Chapel Hill
269 Hamilton Hall CB#3210, Chapel Hill, NC 27599-3210 USA
   andrew_perrin@unc.edu - http://www.unc.edu/~aperrin

On Thu, 7 Jun 2001, Mark Stosberg wrote:

> Stephan Szabo wrote:
> > 
> > On Wed, 6 Jun 2001, Tom Lane wrote:
> > 
> > > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > > > 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.
> 
> > I'd rather have the default be the spec correct behavior
> > and let people configure their server to follow the misinterpretation.
> 
> I like that idea as well. Someone like me who didn't know that this
> feature was in there for M$ could have assumed it _was_ standard
> behavior, and started using it as a habit. Then when I started porting
> my code to another database, I'd have an extra surprise in for me. :) 
> 
> Rather than being an option targeted at just this piece of grammer,
> perhaps it could a piece of a potentially larger option of "stricter
> standards compliance." I realize there are a number of useful extensions
> to the SQL standard in Postgres (which I like and use.), but it seems
> like there would be uses for minimizing non-standard behavior, as well. 
> 
> Thank you all for your contributions to Postgres-- I use it everyday. :) 
> 
>   -mark
> 
> http://mark.stosberg.com/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 


Thu, 7 Jun 2001 10:59:06 -0400 (EDT)
ANDREW PERRIN <aperrin@email.unc.edu>
Re: behavior of ' = NULL' vs. MySQL vs. Standards
Tom,

>Microsoft Access is the guilty party, IIRC.  I recently tried to stir
> up
> some interest in changing this behavior back to the standard, but
> apparently there are still too many people using broken versions of
> Access.

Which verison?  I work with MSAccess 97 a lot, and the supported syntax
is "IS NULL".

> A compromise answer might be to offer a SET variable that selects the
> Microsoft-compatible misimplementation.  Would that fly?

Sounds good to me.  By default, I'd like to see "=NULL" turned OFF, as
I've been writing my SQL on the idea that "=NULL" will return NULL, and
if "=NULL" starts returning a value it's going to screw several things
up ...

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Thu, 07 Jun 2001 08:53:21 -0700
"Josh Berkus" <josh@agliodbs.com>
Re: behavior of ' = NULL' vs. MySQL vs. Standards 
Tom Lane <tgl@sss.pgh.pa.us> writes:

> A compromise answer might be to offer a SET variable that selects the
> Microsoft-compatible misimplementation.  Would that fly?

I'd say that's the best way to handle stuff like this.  If you
implement something that breaks the standard, to be compatible with
arrogant programmers at Microsoft who feel that they are above such
things, you're encouraging users to develop bad habits.  Better to
make them go "SET MICROSOFT BUG COMPATIBILITY ON" first.

To see the effect of leniency in re standards, just try to browse
around the web with a browser that rejects incorrect HTML!  :-)

-tih
-- 
The basic difference is this: hackers build things, crackers break them.

07 Jun 2001 21:17:40 +0200
Tom Ivar Helbekkmo <tih@kpnQwest.no>
Re: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards
"Josh Berkus" <josh@agliodbs.com> writes:
>> Microsoft Access is the guilty party, IIRC.

> Which verison?  I work with MSAccess 97 a lot, and the supported syntax
> is "IS NULL".

In our last go-round on this (pghackers around 14 Jan 2001),
Mike Mascari wrote:

: I don't have Office 2000, but I can confirm Access 97 generates such 
: queries. The query-builder doesn't generate the 'key = NULL' query, but the
: use of the Forms interface does.

			regards, tom lane

Fri, 08 Jun 2001 10:53:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: behavior of ' = NULL' vs. MySQL vs. Standards 
Tom, Mike:

> In our last go-round on this (pghackers around 14 Jan 2001),
> Mike Mascari wrote:
> 
> : I don't have Office 2000, but I can confirm Access 97 generates
> such 
> : queries. The query-builder doesn't generate the 'key = NULL' query,
> but the
> : use of the Forms interface does.

As someone who develops professionally for MSAccess<->ODBC Servers
(PostgreSQL and MS SQL Server) I'd say that we can ignore this.  There
are a *lot* of circumstances where the "Filter by Form" interface breaks
down; I don't think that it works properly with MS SQL Server 7.0,
either.

At most, rather than modifying the query parser and building extra
compile-time options, how about talking to the Great bridge folks about
modifying PgODBC?  That seems like the appropriate place to tell the
system that "= NULL" is equivalent to "IS NULL" -- if anywhere.

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Fri, 08 Jun 2001 08:33:07 -0700
"Josh Berkus" <josh@agliodbs.com>
Re: behavior of ' = NULL' vs. MySQL vs. Standards 
"Josh Berkus" <josh@agliodbs.com> writes:
>> : I don't have Office 2000, but I can confirm Access 97 generates such 
>> : queries. The query-builder doesn't generate the 'key = NULL' query,
>> : but the use of the Forms interface does.

> As someone who develops professionally for MSAccess<->ODBC Servers
> (PostgreSQL and MS SQL Server) I'd say that we can ignore this.  There
> are a *lot* of circumstances where the "Filter by Form" interface breaks
> down; I don't think that it works properly with MS SQL Server 7.0,
> either.

This is an interesting comment.  Does anyone else want to confirm or
refute it?  If Access' forms interface is so badly broken that few
people use it anyway, then I'd say that we should not break standards
compatibility just to support it.

			regards, tom lane

Fri, 08 Jun 2001 18:01:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: behavior of ' = NULL' vs. MySQL vs. Standards 