RE: MS Access vs IS NULL (was Re: [BUGS] Bug in SQL functions that use a NULL parameter directly)
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
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