Re: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Standards

Mike Mascari wrote:
> 
> > > The best solution would be to have the ODBC translate instances
> of '=
> > > NULL' into IS NULL before submitting the query to PostgreSQL.
> 
> Tom Lane wrote:
> 
> > Does Access only talk to Postgres via ODBC?  (Seems plausible, but
> I
> > don't know.)  What about people copying-and-pasting queries
> generated
> > by Access; is it even possible?
> 
> 1. Yes, Access only talkes to Postgres via ODBC.
> 
> 2. The queries generate by Access which use the '= NULL' vs. 'IS
> NULL' language are only generated through the use of Access Forms,
> when some attempts to fetch a record through the forms interface
> where the key of the linked table is an empty edit control. When
> access generates queries, it correctly generates 'IS NULL' language.
> It is a *very specific problem* WRT the user of Access Forms.
> 

Could you send me an example using Access Forms ?
DAO seems to translate '= NULL' into 'IS NULL' properly.
Hmm parameter bindings( expr = ? ) could be the cause.

> > I doubt that this'd really help much, in any case; it merely moves
> the
> > necessary switch from the backend to ODBC.
> 
> It seems to me that is where it belongs. The ODBC driver is already
> doing translation of some Access functions not found in PostgreSQL
> such as LCASE->lower, etc.

It doesn't seem that easy.
Detecting '= NULL' isn't sufficient.
'SET .. expr = NULL' mustn't be tranlated into
'SET .. expr IS NULL'. The '= NULL' must be inside
a where clause.
Unfortunately the current psqlodbc driver parses
little.

regards,
Hiroshi Inoue