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