Skip site navigation (1) Skip section navigation (2)

RE: comparing rows

From: Magnus Hagander <mha(at)sollentuna(dot)net>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: RE: comparing rows
Date: 2000-08-03 08:15:20
Message-ID: 215896B6B5E1CF11BC5600805FFEA82103D97A8A@sirius.edu.sollentuna.se (view raw or flat)
Thread:
Lists: pgsql-hackers
> 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

Responses

pgsql-hackers by date

Next:From: Don BaccusDate: 2000-08-03 13:22:10
Subject: RE: comparing rows
Previous:From: Hiroshi InoueDate: 2000-08-03 05:36:53
Subject: RE: Raw constraint & pg_relcheck.rcsrc

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group