> 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

Thu, 3 Aug 2000 10:15:20 +0200 
Magnus Hagander <mha@sollentuna.net>
RE: comparing rows 
> 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

Thu, 03 Aug 2000 13:44:22 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: comparing rows
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 Service and other goodies at
  http://donb.photo.net.

Thu, 03 Aug 2000 06:52:53 -0700
Don Baccus <dhogaza@pacifier.com>
Re: comparing rows
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

Thu, 03 Aug 2000 09:55:35 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: comparing rows 
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 like
	CASE 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

Thu, 03 Aug 2000 10:07:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: comparing rows 
> 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, Pennsylvania 19026

Thu, 3 Aug 2000 10:22:37 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: comparing rows
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

Thu, 03 Aug 2000 10:23:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: comparing rows 
> 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, Pennsylvania 19026

Thu, 3 Aug 2000 10:24:28 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: comparing rows
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 Service and other goodies at
  http://donb.photo.net.

Thu, 03 Aug 2000 07:32:36 -0700
Don Baccus <dhogaza@pacifier.com>
Re: comparing rows 
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 Service and other goodies at
  http://donb.photo.net.

Thu, 03 Aug 2000 07:35:13 -0700
Don Baccus <dhogaza@pacifier.com>
Re: comparing rows
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 Service and other goodies at
  http://donb.photo.net.

Thu, 03 Aug 2000 07:37:17 -0700
Don Baccus <dhogaza@pacifier.com>
Re: comparing rows 
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

Thu, 03 Aug 2000 10:40:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: comparing rows 
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 ...



Thu, 3 Aug 2000 13:23:47 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: comparing rows 
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

Thu, 03 Aug 2000 15:36:50 -0400
Mike Mascari <mascarm@mascari.com>
Re: comparing rows
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

Thu, 03 Aug 2000 16:12:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: comparing rows 
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 ...



Thu, 3 Aug 2000 17:20:36 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: comparing rows 
> 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

Fri, 04 Aug 2000 10:07:38 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: comparing rows
> > 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

Fri, 04 Aug 2000 06:43:45 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: comparing rows
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*



Fri, 4 Aug 2000 11:43:17 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: comparing rows
> 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

Fri, 04 Aug 2000 14:58:55 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: comparing rows
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?


Fri, 4 Aug 2000 12:41:10 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: comparing rows
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/

Fri, 4 Aug 2000 14:08:18 -0400
Christopher Masto <chris@netmonger.net>
Re: comparing rows
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

Sat, 05 Aug 2000 21:54:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: comparing rows 