Thread: behavior of ' = NULL' vs. MySQL vs. Standards
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/
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.
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
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?
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
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/
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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"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
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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"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
> > 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
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
Universityof 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
>
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.