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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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