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.

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