Обсуждение: x = NULL

The x = NULL hack keeps biting people.  Innocent people should not be
exposed to incorrect behaviour because of (supposed) MS Access breakage.
I strongly urge that we do one of the following:

1) Provide a tunable knob to turn this on (cf. KSQO)

2) Confine this to the ODBC driver somehow (which could be done via #1)

Actually, last time we discussed this there was some confusion whether
Access actually had the bug in question.  That might be worth figuring
out.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

> The x = NULL hack keeps biting people.  Innocent people should not be
> exposed to incorrect behaviour because of (supposed) MS Access breakage.
> I strongly urge that we do one of the following:
>
> 1) Provide a tunable knob to turn this on (cf. KSQO)
>
> 2) Confine this to the ODBC driver somehow (which could be done via #1)
>
> Actually, last time we discussed this there was some confusion whether
> Access actually had the bug in question.  That might be worth figuring
> out.
>

A while back I tested Oracle and MSSQL7 for this -- neither support it. See:
http://fts.postgresql.org/db/mw/msg.html?mid=1021527

I just checked MS Access 2000 -- it also returns no records on x = NULL
versus the correct answer with x IS NULL, at least for a simple query. IIRC,
someone mentioned that the original issue was limited to the use of filtered
forms in Access, or something like that. But ISTM, that if neither Oracle
nor even MSSQL support the syntax, then PostgreSQL should not either.

-- Joe

> > The x = NULL hack keeps biting people.  Innocent people 
> should not be 
> > exposed to incorrect behaviour because of (supposed) MS Access 
> > breakage. I strongly urge that we do one of the following:
> >
> > 1) Provide a tunable knob to turn this on (cf. KSQO)
> >
> > 2) Confine this to the ODBC driver somehow (which could be done via 
> > #1)
> >
> > Actually, last time we discussed this there was some 
> confusion whether 
> > Access actually had the bug in question.  That might be 
> worth figuring 
> > out.
> >
> 
> A while back I tested Oracle and MSSQL7 for this -- neither 
> support it. See: http://fts.postgresql.org/db/mw/msg.html?mid=1021527
> 
> I just checked MS Access 2000 -- it also returns no records 
> on x = NULL versus the correct answer with x IS NULL, at 
> least for a simple query. IIRC, someone mentioned that the 
> original issue was limited to the use of filtered forms in 
> Access, or something like that. But ISTM, that if neither 
> Oracle nor even MSSQL support the syntax, then PostgreSQL 
> should not either.

MSSQL supports =NULL syntax if you set ANSI_NULLS=OFF (can be set per
connection, and yuo can also specify which is default for a certain
database or installation).
I beleive that ANSI_NULLS=OFF was default in SQL Server <= 6.5, and
ANSI_NULLS=ON are default in >= 7.0.

And yes, if you create a Query with Access, it uses IS NULL / IS NOT
NULL. The issue was with filtered forms only.

//Magnus

I'm getting tired of this, so unless someone can present a reason not to,
I'll implement a GUC parameter to turn this off -- and turn it off by
default.

I wrote:

> The x = NULL hack keeps biting people.  Innocent people should not be
> exposed to incorrect behaviour because of (supposed) MS Access breakage.
> I strongly urge that we do one of the following:
>
> 1) Provide a tunable knob to turn this on (cf. KSQO)
>
> 2) Confine this to the ODBC driver somehow (which could be done via #1)
>
> Actually, last time we discussed this there was some confusion whether
> Access actually had the bug in question.  That might be worth figuring
> out.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Peter Eisentraut <peter_e@gmx.net> writes:
> I'm getting tired of this, so unless someone can present a reason not to,
> I'll implement a GUC parameter to turn this off -- and turn it off by
> default.

You'll have to push the switch-driven transformation into analyze.c ---
it is not okay for gram.y to look at GUC parameters.  But as long as you
do it correctly, I'm for it.
        regards, tom lane