Thread: MS Access vs IS NULL (was Re: [BUGS] Bug in SQL functions that use a NULL parameter directly)

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Because of Access's brokenness, the parser or some other layer of the
> code "fixes" explicit = NULL (ie, in the actually query string) into
> IS NULL which is the correct way to check for nulls.
> Because your original query was = $1, it doesn't do the mangling of the
> SQL to change into IS NULL when $1 is NULL.  The fact that we do that
> conversion at all actually breaks spec a little bit but we have little
> choice with broken clients.

It seems to me that we heard awhile ago that Access no longer generates
these non-spec-compliant queries --- ie, it does say IS NULL now rather
than the other thing.  If so, it seems to me that we ought to remove the
parser's = NULL hack, so that we have spec-compliant NULL behavior.

Anyone recall anything about that?  A quick search of my archives didn't
turn up the discussion that I thought I remembered.
        regards, tom lane

I don't have Office 2000, but I can confirm Access 97 generates such 
queries. The query-builder doesn't generate the 'key = NULL' query, but the 
use of the Forms interface does.

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    Tom Lane [SMTP:tgl@sss.pgh.pa.us]
Sent:    Sunday, January 14, 2001 2:00 PM
To:    Stephan Szabo
Cc:    pgsql-hackers@postgresql.org
Subject:    [HACKERS] MS Access vs IS NULL (was Re: [BUGS] Bug in SQL 
functions that use a NULL parameter directly)

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Because of Access's brokenness, the parser or some other layer of the
> code "fixes" explicit = NULL (ie, in the actually query string) into
> IS NULL which is the correct way to check for nulls.
> Because your original query was = $1, it doesn't do the mangling of the
> SQL to change into IS NULL when $1 is NULL.  The fact that we do that
> conversion at all actually breaks spec a little bit but we have little
> choice with broken clients.

It seems to me that we heard awhile ago that Access no longer generates
these non-spec-compliant queries --- ie, it does say IS NULL now rather
than the other thing.  If so, it seems to me that we ought to remove the
parser's = NULL hack, so that we have spec-compliant NULL behavior.

Anyone recall anything about that?  A quick search of my archives didn't
turn up the discussion that I thought I remembered.
        regards, tom lane

Mike Mascari <mascarm@mascari.com> writes:
> I don't have Office 2000, but I can confirm Access 97 generates such 
> queries. The query-builder doesn't generate the 'key = NULL' query, but the 
> use of the Forms interface does.

Yes, it was broken as of a couple years ago.  What I thought I
remembered hearing was that the current release (which I guess would
be Office 2000?) is fixed.  The first question is whether that is indeed
true.  The second question, assuming it's true, is how long to continue
violating the spec and confusing people in order to cater to old broken
versions of Access.  My vote is not too darn long, but others may differ.
        regards, tom lane

> Anyone recall anything about that?  A quick search of my archives didn't
> turn up the discussion that I thought I remembered.

Hmm. Maybe now we know what you dream about at night ;)
                    - Thomas

Tom Lane wrote:
> 
> Mike Mascari <mascarm@mascari.com> writes:
> > I don't have Office 2000, but I can confirm Access 97 generates such
> > queries. The query-builder doesn't generate the 'key = NULL' query, but the
> > use of the Forms interface does.
> 
> Yes, it was broken as of a couple years ago.  What I thought I
> remembered hearing was that the current release (which I guess would
> be Office 2000?) is fixed.  The first question is whether that is indeed
> true.  The second question, assuming it's true, is how long to continue
> violating the spec and confusing people in order to cater to old broken
> versions of Access.  My vote is not too darn long, but others may differ.

</Lurking>

I'm afraid so ... Lots of people (including large institutions) have
Office installed as a "standard" interchange tool, use Office 95 or 97,
but are tired of MS' endless "upgrade" tax and won't "upgrade" to Office
2000, except at gunpoint (i. e. disappearance of volume license
agreements for O95 or O97).

So I'm afraid these versions will have a loooong life ...

<Lurking>
                Emmanuel Charpentier

--
Emmanuel Charpentier