RE: comparing rows

> 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