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