Thread: Re: [SQL] 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

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

> > 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

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.

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/