Обсуждение: comparing rows

Can anyone explain the ERROR below? Is this a bug or a feature?
This is 7.0.2.

test=# select (1,2,3) = (1,2,3);?column? 
----------t
(1 row)

test=# select (1,2,3) = (1,2,9);?column? 
----------f
(1 row)

test=# select (1,2,3) = (1,2,null);?column? 
----------
(1 row)

test=# select (1,2,null) = (1,2,null);
ERROR:  Unable to identify an operator '=' for types 'unknown' and 'unknown'You will have to retype this query using an
explicitcast
 
--
Tatsuo Ishii

Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> test=# select (1,2,null) = (1,2,null);
> ERROR:  Unable to identify an operator '=' for types 'unknown' and 'unknown'
>     You will have to retype this query using an explicit cast

Well, there's no basis for deciding what the datatype of the third
column is.

The only reason you don't get the same error from the non-row case

regression=# select null = null;?column?
----------t
(1 row)

is that we have an ugly, horrible kluge in the parser to (mis) interpret
"foo = null" as meaning "foo ISNULL", in order to be compatible with
broken Microsoft SQL implementations.  If you try any other operator
you get

regression=# select null <> null;
ERROR:  Unable to identify an operator '<>' for types 'unknown' and 'unknown'       You will have to retype this query
usingan explicit cast
 

I'd certainly not vote to propagate the "= null" kluge into the
row-equality code...
        regards, tom lane

> Well, there's no basis for deciding what the datatype of the third
> column is.
> 
> The only reason you don't get the same error from the non-row case
> 
> regression=# select null = null;
>  ?column?
> ----------
>  t
> (1 row)
> 
> is that we have an ugly, horrible kluge in the parser to 
> (mis) interpret
> "foo = null" as meaning "foo ISNULL", in order to be compatible with
> broken Microsoft SQL implementations.  If you try any other operator
> you get

Not compatible with the current release of MS SQL/MSDE :-) At least not
fully.

Isn't it just plain *wrong* to state that null = null? After all, NULL is
unknown, and is not equal to anything, no?

I just checked on MS SQL Server 7.0, and using the query "select CASE WHEN
null=null THEN 'Yes' ELSE 'No' END" (just doing null=null produces a syntax
error), it returns "No" (that is, null != null).

However, if I turn *off* "ANSI nulls, paddings and warnings", it produces
"Yes" (null=null). The default can be changed on a per-database basis. By
default, any ODBC/OLEDB client turns *on* ANSI Nulls when it connects.


Summary of MS:
When it runs in ANSI mode, null != null.
When it runs in backwards compatible mode , null=null.


Perhaps it would be more correct for postgresql to provide an option for
"non-ANSI null handling" as well? And leaving ANSI compliant handling as the
default?

//Magnus

> Not compatible with the current release of MS SQL/MSDE :-) At least not
> fully.

The original issue was that "foo = NULL" is *not* a legal SQL92
statement. But of course M$ garbage like Access (and presumably other
products) generated "foo = NULL" in their automated ODBC queries,
despite the fact that "foo IS NULL" is defined in the standard and would
have been an obvious choice :(

> Isn't it just plain *wrong* to state that null = null? After all, NULL is
> unknown, and is not equal to anything, no?

Right. But also wrong from an SQL92 compliance standpoint. afaict this
is still true for SQL99.

btw, it appears that SQL99 (haven't checked SQL92) specifies that

test=# select (1,2,3) = (1,2,null);?column? 
----------
(1 row)

should return FALSE, not NULL. I haven't looked to see if this is
consistant with other comparisons involving NULL.

> I just checked on MS SQL Server 7.0, and using the query "select CASE WHEN
> null=null THEN 'Yes' ELSE 'No' END" (just doing null=null produces a syntax
> error), it returns "No" (that is, null != null).
> However, if I turn *off* "ANSI nulls, paddings and warnings", it produces
> "Yes" (null=null). The default can be changed on a per-database basis. By
> default, any ODBC/OLEDB client turns *on* ANSI Nulls when it connects.
> Summary of MS:
> When it runs in ANSI mode, null != null.

*sigh* If it actually *had* an ANSI mode, then "foo = NULL" would be
rejected. Period.

> When it runs in backwards compatible mode , null=null.
> Perhaps it would be more correct for postgresql to provide an option for
> "non-ANSI null handling" as well? And leaving ANSI compliant handling as the
> default?

afaict the option will be "M$" vs "published standards" support, and it
seems the wrong way to head. Especially since M$ will try break any
compliance we may achieve. Better to ask your friendly M$ supplier to
support standards ;)
                    - Thomas

Magnus Hagander <mha@sollentuna.net> writes:
> Summary of MS:
> When it runs in ANSI mode, null != null.
> When it runs in backwards compatible mode , null=null.

Oh, that's interesting, they fixed their bug.

foo = NULL should produce NULL (*not* true, and *not* false either,
though it'd act like false in a CASE test).

Here is one vote for ripping out our parser kluge and making null act
like it should according to the spec.  Comments?
        regards, tom lane

At 01:44 PM 8/3/00 +0000, Thomas Lockhart wrote:
>> Not compatible with the current release of MS SQL/MSDE :-) At least not
>> fully.
>
>The original issue was that "foo = NULL" is *not* a legal SQL92
>statement. 

Ummm...I think this is wrong, and I have Date on my side (I just
checked).  foo = NULL is NULL, just like foo + NULL is NULL.

If foo = NULL is illegal, doesn't this make it rather difficult to
say things like table1.i = table2.i for tables that contain NULLs
in column i?

Oracle allows it, and returns NULL.  Oracle's fairly trustworthy
on low-level standards compliance over things like NULL (not
entirely, though - it treats '' as NULL in the VALUES list of
an INSERT, though not elsewhere, for instance).

NULL = NULL is also perfectly legal, returning NULL.  Before one
gets too excited, NULL <> NULL and NULL < NULL etc all return
NULL, too, along with NULL = <anything> so it's not terribly
useful, which is why IS NULL exists.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

At 10:15 AM 8/3/00 +0200, Magnus Hagander wrote:

>Isn't it just plain *wrong* to state that null = null? After all, NULL is
>unknown, and is not equal to anything, no?

According to the standard, yes, it is wrong.  

>Perhaps it would be more correct for postgresql to provide an option for
>"non-ANSI null handling" as well? And leaving ANSI compliant handling as the
>default?

I would think so.  



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

> Magnus Hagander <mha@sollentuna.net> writes:
> > Summary of MS:
> > When it runs in ANSI mode, null != null.
> > When it runs in backwards compatible mode , null=null.
> 
> Oh, that's interesting, they fixed their bug.
> 
> foo = NULL should produce NULL (*not* true, and *not* false either,
> though it'd act like false in a CASE test).
Ok.
Hmm. I wonder how I check if it produces NULL :-) If I just do:
SELECT null=null
I get a syntax error. Is there some other way for me to check what it does?
It appears that "null=null" isn't a valid expression (neither is "1=1" or
something like that). Basically, it does not appear to be possible to SELECT
the return value of a comparison at all - and CASE will apparantly not work
in this case. I don't think it's capable of handling anything other than
TRUE/FALSE as the result of a comparison.

//Magnus

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> btw, it appears that SQL99 (haven't checked SQL92) specifies that

> test=# select (1,2,3) = (1,2,null);
>  ?column? 
> ----------
> (1 row)

> should return FALSE, not NULL.

What?  If so, they broke it pretty badly.  This should be equivalent to
1 = 1 AND 2 = 2 AND 3 = NULL, which should reduce to TRUE AND TRUE AND NULL,
which should reduce to NULL.  Anything else is not self-consistent.

>> Summary of MS:
>> When it runs in ANSI mode, null != null.

> *sigh* If it actually *had* an ANSI mode, then "foo = NULL" would be
> rejected. Period.

Well, mumble, that is an overly literal interpretation of the spec if
you ask me.  It is not unreasonable to allow NULL as a literal constant,
especially since it doesn't create any issues that you can't get to with
100%-plain-vanilla-SQL92 constructs likeCASE WHEN TRUE THEN NULL END
Where MS blew it was in not following SQL92-compatible semantics of
operations on nulls.  (We can't throw *too* many stones, since we had
a number of problems with logical ops on nulls too, up till 7.0 ...)

> afaict the option will be "M$" vs "published standards" support, and it
> seems the wrong way to head.

I don't want an option either.  I want to change our code (back to) SQL
compliant semantics of NULL comparisons, ie remove the parser kluge.
        regards, tom lane

> > Summary of MS:
> > When it runs in ANSI mode, null != null.
> > When it runs in backwards compatible mode , null=null.
> 
> Oh, that's interesting, they fixed their bug.
> 
> foo = NULL should produce NULL (*not* true, and *not* false either,
> though it'd act like false in a CASE test).

Hmm. I think I can answer my own question by using the dreaded manual. This
is what Books On-Line for MS has to say:

"
The result of a comparison operator has the Boolean data type, which has
three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean
data type are known as Boolean expressions.

Unlike other SQL Server data types, a Boolean data type cannot be specified
as the data type of a table column or variable, and cannot be returned in a
result set.

When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions
returns UNKNOWN. When SET ANSI_NULLS is OFF, the same rules apply, except an
equals operator returns TRUE if both expressions are NULL. For example, NULL
= NULL returns TRUE if SET ANSI_NULLS is OFF.
"

So apparantly:
a) I can't check it :-)
b) It does return UNKNOWN (NULL?) for <anything>=NULL


//Magnus

Don Baccus <dhogaza@pacifier.com> writes:
> If foo = NULL is illegal, doesn't this make it rather difficult to
> say things like table1.i = table2.i for tables that contain NULLs
> in column i?

Thomas is not saying that the *operation* is illegal.  His point is
purely a syntactic one: SQL92 allows the keyword "NULL" only in certain
specified contexts, and out on its own as a component of an arithmetic
expression ain't one of them.  You could legally write the same thing in
at least two ways, though:
foo = (CASE WHEN TRUE THEN NULL END)
foo = (CAST NULL AS somedatatype)

It may be that the SQL92 authors intended this restriction to avoid
having to figure out what datatype an unadorned NULL is.  Unfortunately
they blew it in the CASE case :-(, so you still have to have a way of
deciding that.

As far as I can see, allowing NULL as a general-purpose literal is a
perfectly reasonable spec extension that *everybody* does, including us.
Thomas may be the only person anywhere who is bothered by it ;-)
        regards, tom lane

> Magnus Hagander <mha@sollentuna.net> writes:
> > Summary of MS:
> > When it runs in ANSI mode, null != null.
> > When it runs in backwards compatible mode , null=null.
> 
> Oh, that's interesting, they fixed their bug.
> 
> foo = NULL should produce NULL (*not* true, and *not* false either,
> though it'd act like false in a CASE test).
> 
> Here is one vote for ripping out our parser kluge and making null act
> like it should according to the spec.  Comments?

I thought our only kludge was allowing = NULL because we got tons of MS
folks complaining in the past before we did this.  Are you thinking that
the newer MS versions will not give us a problem.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

> Oracle allows it, and returns NULL.  Oracle's fairly trustworthy
> on low-level standards compliance over things like NULL (not
> entirely, though - it treats '' as NULL in the VALUES list of
> an INSERT, though not elsewhere, for instance).

So you can't get a zero-length string into a column in Oracle. 
Interesting.


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I thought our only kludge was allowing = NULL because we got tons of MS
> folks complaining in the past before we did this.  Are you thinking that
> the newer MS versions will not give us a problem.

Well, I'm not sure.  Magnus is saying that the newer MS servers default
to spec-compliant semantics --- ie, foo = NULL will yield NULL.  But
IIRC the original complaints were because MS tools like Access would
*generate* this expression and expect it to behave like foo IS NULL.

Can MS have fixed all their apps already?  Seems unlikely.  Maybe we
have to leave the kluge in there awhile longer.
        regards, tom lane

At 10:07 AM 8/3/00 -0400, Tom Lane wrote:
>Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> btw, it appears that SQL99 (haven't checked SQL92) specifies that
>
>> test=# select (1,2,3) = (1,2,null);
>>  ?column? 
>> ----------
> 
>> (1 row)
>
>> should return FALSE, not NULL.
>
>What?  If so, they broke it pretty badly. 

Date disagrees with Thomas on this one, too.

On the other hand, select (2,2,3) = (1,2,null) would be false
because 1 <> 2, therefore the result is not unknown.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

At 10:23 AM 8/3/00 -0400, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>> If foo = NULL is illegal, doesn't this make it rather difficult to
>> say things like table1.i = table2.i for tables that contain NULLs
>> in column i?
>
>Thomas is not saying that the *operation* is illegal.  His point is
>purely a syntactic one: SQL92 allows the keyword "NULL" only in certain
>specified contexts, and out on its own as a component of an arithmetic
>expression ain't one of them. 

I misunderstood, didn't check for the use of the literal, just the
semantics of the operations.

>As far as I can see, allowing NULL as a general-purpose literal is a
>perfectly reasonable spec extension that *everybody* does, including us.
>Thomas may be the only person anywhere who is bothered by it ;-)

Thomas, do you have a reference into the standard?



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

At 10:22 AM 8/3/00 -0400, Bruce Momjian wrote:
>> Oracle allows it, and returns NULL.  Oracle's fairly trustworthy
>> on low-level standards compliance over things like NULL (not
>> entirely, though - it treats '' as NULL in the VALUES list of
>> an INSERT, though not elsewhere, for instance).
>
>So you can't get a zero-length string into a column in Oracle. 
>Interesting.

The killer is it makes porting from Oracle to Postgres or another
DB that does it right a pain in the ass.

I've got a lot of experience with this particular issue due to the
OpenACS web toolkit (arsDigita's stuff ported from Oracle to Postgres).



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

On Thu, 3 Aug 2000, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I thought our only kludge was allowing = NULL because we got tons of MS
> > folks complaining in the past before we did this.  Are you thinking that
> > the newer MS versions will not give us a problem.
> 
> Well, I'm not sure.  Magnus is saying that the newer MS servers default
> to spec-compliant semantics --- ie, foo = NULL will yield NULL.  But
> IIRC the original complaints were because MS tools like Access would
> *generate* this expression and expect it to behave like foo IS NULL.
> 
> Can MS have fixed all their apps already?  Seems unlikely.  Maybe we
> have to leave the kluge in there awhile longer.

I kind of agree with your original comment about removing the kludge,
since the kludge does go against the spec ...

... a couple of thoughts to that effect:

1. this won't be 'in effect' until v7.1 comes out anyway
2. v7.0.2 will still have the kludge and will be available if ppl *really*  need it, no?

My vote is to go with Tom on this and remove the kludge ...

The Hermit Hacker wrote:
> 
> On Thu, 3 Aug 2000, Tom Lane wrote:
>
> > Can MS have fixed all their apps already?  Seems unlikely.  Maybe we
> > have to leave the kluge in there awhile longer.
> 
> I kind of agree with your original comment about removing the kludge,
> since the kludge does go against the spec ...
> 
> ... a couple of thoughts to that effect:
> 
> 1. this won't be 'in effect' until v7.1 comes out anyway
> 2. v7.0.2 will still have the kludge and will be available if ppl *really*
>    need it, no?
> 
> My vote is to go with Tom on this and remove the kludge ...

Please don't. It seems true that Microsoft has enabled a mode for
SQL Server, but the main problem was Access. And Access 95/97 has
a huge installed base of users that would not be able to use its
automated query tools with PostgreSQL. If the interfaces' list is
any indication, most people use a architecture like:

PostgreSQL Server <--------- Access Front-End      |      |      V Web Server

I hate kludges. But I'd prefer compatibility with Microsoft
tools.

Just my opinion, 

Mike Mascari

Mike Mascari <mascarm@mascari.com> writes:
> Please don't. It seems true that Microsoft has enabled a mode for
> SQL Server, but the main problem was Access. And Access 95/97 has
> a huge installed base of users that would not be able to use its
> automated query tools with PostgreSQL.

That was what I was afraid of :-(.  Question though: if MS has changed
the default behavior of their server to be (more) SQL-compliant, aren't
these folks being forced to update their Access installs anyway?
Presumably those old versions do not know how to select the non-default
behavior of SQL Server, so they're gonna be incompatible with newer
servers despite the nominal presence of a workaround.

I've got no objection to leaving in the kluge for another release or two
if there's still a big installed base that needs it --- but I don't want
to leave it there indefinitely for the benefit of a few tail-end
Charlies.  Seems like folks running obsolete Access code are unlikely
to pick up the latest Postgres either, so will it really matter if we
change?
        regards, tom lane

On Thu, 3 Aug 2000, Tom Lane wrote:

> Mike Mascari <mascarm@mascari.com> writes:
> > Please don't. It seems true that Microsoft has enabled a mode for
> > SQL Server, but the main problem was Access. And Access 95/97 has
> > a huge installed base of users that would not be able to use its
> > automated query tools with PostgreSQL.
> 
> That was what I was afraid of :-(.  Question though: if MS has changed
> the default behavior of their server to be (more) SQL-compliant, aren't
> these folks being forced to update their Access installs anyway?
> Presumably those old versions do not know how to select the non-default
> behavior of SQL Server, so they're gonna be incompatible with newer
> servers despite the nominal presence of a workaround.
> 
> I've got no objection to leaving in the kluge for another release or two
> if there's still a big installed base that needs it --- but I don't want
> to leave it there indefinitely for the benefit of a few tail-end
> Charlies.  Seems like folks running obsolete Access code are unlikely
> to pick up the latest Postgres either, so will it really matter if we
> change?

that was my opinion ... if these ppl are already using it for talking to
PostgreSQL., they have to be running a current release of it with the
kludge inplace ... we're only preventing PostgreSQL admin from being able
to upgrade while their clients are still stuck with the older Access ...

> btw, it appears that SQL99 (haven't checked SQL92) specifies that
> 
> test=# select (1,2,3) = (1,2,null);
>  ?column? 
> ----------
>  
> (1 row)
> 
> should return FALSE, not NULL. I haven't looked to see if this is
> consistant with other comparisons involving NULL.

This is interesting. If "(1,2,3) = (1,2,null)" is legal from the point
of view of the standard, isn't "(1,2,null) = (1,2,null)" legal
comparison too? no?

BTW, I would like to object the "null = null" support in
PostgreSQL. It is against the standard. That's enough reason to reject
the syntax.
--
Tatsuo Ishii

> > btw, it appears that SQL99 (haven't checked SQL92) specifies that
> > test=# select (1,2,3) = (1,2,null);
> >  ?column?
> > ----------
> 
> > (1 row)
> 
> > should return FALSE, not NULL.
> What?  If so, they broke it pretty badly.  This should be equivalent to
> 1 = 1 AND 2 = 2 AND 3 = NULL, which should reduce to TRUE AND TRUE AND NULL,
> which should reduce to NULL.  Anything else is not self-consistent.

Hmm. I could have sworn I looked this up (and was suprised at the
result). But I'm not finding the example anywhere, and Section 8.2
General Rule 1 seems to indicate that we do the right thing here
already.

Also, I *think* we have settled on the following facts:

1) "3 = NULL" is typical of an expression generated by M$ Access.

2) "3 = NULL" is *not* legal SQL9x syntax, which specifies "3 IS NULL"
for the comparison "does three have a value of NULL?".

3) New versions of M$ Access continue to generate bogus queries
containing these comparisons.

4) Postgres will continue to understand (at least) the special case of
"column/value = NULL" to retain compatibility with M$.

Oh, and

5) Thomas will continue to complain about M$ for shipping products with
gratuitous deviations from published standards. ;)
                   - Thomas

> Mike Mascari <mascarm@mascari.com> writes:
> > Please don't. It seems true that Microsoft has enabled a mode for
> > SQL Server, but the main problem was Access. And Access 95/97 has
> > a huge installed base of users that would not be able to use its
> > automated query tools with PostgreSQL.
> 
> That was what I was afraid of :-(.  Question though: if MS has changed
> the default behavior of their server to be (more) 
> SQL-compliant, aren't
> these folks being forced to update their Access installs anyway?
> Presumably those old versions do not know how to select the 
> non-default
> behavior of SQL Server, so they're gonna be incompatible with newer
> servers despite the nominal presence of a workaround.

Can somebody enlighten me as to *when* Access creates a query that has
"=null" in it?

I just tried to reproduce it, and I failed. 

I type        Access 97SR2    Access 2000SR1
------        ------------    --------------
=NULL            Is Null        =Null
IS NULL        Is Null        Is Null
<>NULL        Is Not Null        Is Not Null
IS NOT NULL        Is Not Null        Is Not Null


Seems to me that Access 2000 *allows* you to write =Null, but if you write
"Is Null" (correct), it will leave it alone. And <>NULL is always converted
to Is Not Null.

I don't have any Access 95 around - but it's so old that hardly anybody
should be using it anymore, right? :-)


Problem is that it's too easy to change your MS SQL Installation to work in
"the old way". Simply have the clients click in a box when they create the
ODBC datasource. The downside of backwards compatibilty is not forcing
people to fix their stuff :-)


//Magnus

Magnus Hagander wrote:
> 
> Can somebody enlighten me as to *when* Access creates a query that has
> "=null" in it?
> 
> I just tried to reproduce it, and I failed.
> 

The problem appears (just from a quick look at the mail archive)
to manifest itself when Access generates queries to handle Forms
processing. Below is a sample issue that people had when using
Access 97 w/PostgreSQL:

------------------------------------------------------
From: mmw@paradise.net.nz 
To: pgsql-interfaces@postgresql.org 
Subject: Access ODBC worries 
Date: Thu, 12 Aug 1999 16:44:41 +1200 (NZST) 

Hi All

I have been battling with a problem now for days. I have a linked
form in Access 97 to linked tables to a PGSQL backend via ODBC.
The code works well, but after the form has been on the screen
for about 20 minutes idling or doing something (makes no
difference) then all hell breaks loose.  The computer tries to
requery the form data from the backend in a very strange way. 
The odbc log file output at the bottom of this message explains
the problem in greater detail.

...

<ODBC log follows>

...

------------------------------------------------------------
hdbc=91652364, stmt=91555964, result=0
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=92605260, parameters_allocated=1
statement_type=0, statement='SELECT "Toplevel time
recordal"."oid" FROM "Toplevel time
recordal" WHERE ("File link" =  ? ) ORDER BY "Startdate"  DESC '
stmt_with_params='SELECT "Toplevel time recordal"."oid" FROM
"Toplevel time recordal"
WHERE ("File link" =  NULL ) ORDER BY "Startdate"  DESC '
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
cursor_name='SQL_CUR0575087C'
----------------QResult Info -------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: 
parser: parse error
at or near "null"'

------------------------------------------------------

I'll attempt to provide an easily repeatable operation, but the
problem *does* exist in Access 97.

Mike Mascari

On Fri, 4 Aug 2000, Thomas Lockhart wrote:

> > > btw, it appears that SQL99 (haven't checked SQL92) specifies that
> > > test=# select (1,2,3) = (1,2,null);
> > >  ?column?
> > > ----------
> > 
> > > (1 row)
> > 
> > > should return FALSE, not NULL.
> > What?  If so, they broke it pretty badly.  This should be equivalent to
> > 1 = 1 AND 2 = 2 AND 3 = NULL, which should reduce to TRUE AND TRUE AND NULL,
> > which should reduce to NULL.  Anything else is not self-consistent.
> 
> Hmm. I could have sworn I looked this up (and was suprised at the
> result). But I'm not finding the example anywhere, and Section 8.2
> General Rule 1 seems to indicate that we do the right thing here
> already.
> 
> Also, I *think* we have settled on the following facts:
> 
> 1) "3 = NULL" is typical of an expression generated by M$ Access.
> 
> 2) "3 = NULL" is *not* legal SQL9x syntax, which specifies "3 IS NULL"
> for the comparison "does three have a value of NULL?".
> 
> 3) New versions of M$ Access continue to generate bogus queries
> containing these comparisons.
> 
> 4) Postgres will continue to understand (at least) the special case of
> "column/value = NULL" to retain compatibility with M$.

Stupid question here ... but ... can't this kludge be "faked" in the ODBC
driver itself, vs in the server? *raised eyebrow*

> Stupid question here ... but ... can't this kludge be "faked" in the ODBC
> driver itself, vs in the server? *raised eyebrow*

Right now, the ODBC driver does not do full parsing of the input
queries, so imho it would be difficult to reliably identify the correct
string substitution.
                   - Thomas

On Fri, 4 Aug 2000, Thomas Lockhart wrote:

> > Stupid question here ... but ... can't this kludge be "faked" in the ODBC
> > driver itself, vs in the server? *raised eyebrow*
> 
> Right now, the ODBC driver does not do full parsing of the input
> queries, so imho it would be difficult to reliably identify the correct
> string substitution.

Ah, there went that idea ... to bad we couldn't put some sort of check in
the server "if through odbc driver, allow this kludge" :)

You say 'right now' ... is full parsing something that the ODBC driver
should be done?

On Fri, Aug 04, 2000 at 12:41:10PM -0300, The Hermit Hacker wrote:
> On Fri, 4 Aug 2000, Thomas Lockhart wrote:
> 
> > > Stupid question here ... but ... can't this kludge be "faked" in the ODBC
> > > driver itself, vs in the server? *raised eyebrow*
> > 
> > Right now, the ODBC driver does not do full parsing of the input
> > queries, so imho it would be difficult to reliably identify the correct
> > string substitution.
> 
> Ah, there went that idea ... to bad we couldn't put some sort of check in
> the server "if through odbc driver, allow this kludge" :)

Could the ODBC driver have a little checkbox that causes it to magically
issue a "SET BROKEN_MICROSOFT_NULL" immediately after it connects?
-- 
Christopher Masto         Senior Network Monkey      NetMonger Communications
chris@netmonger.net        info@netmonger.net        http://www.netmonger.net

Free yourself, free your machine, free the daemon -- http://www.freebsd.org/

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> Stupid question here ... but ... can't this kludge be "faked" in the ODBC
>> driver itself, vs in the server? *raised eyebrow*

> Right now, the ODBC driver does not do full parsing of the input
> queries, so imho it would be difficult to reliably identify the correct
> string substitution.

However, the ODBC driver's parsing is desperately inadequate anyway
(it fails to handle nested function calls properly).  It might well be
that fixing that will entail doing enough work that "= NULL" could be
recognized without much more work.

On the third hand, pushing the kluge out to the ODBC driver doesn't make
it any less a kluge...
        regards, tom lane