On Jan 2, 2008 5:44 PM, <[EMAIL PROTECTED]> wrote:
> The current behavior of SQLite is to not do anything special
> with NULLs in an EXISTS operator. For example:
>
> CREATE TABLE t1(x);
> INSERT INTO t1 VALUES(NULL);
> SELECT EXISTS(SELECT x FROM t1);
>
> The final SELECT above returns 1 (true) because an entry exists
> in t1, even though that entry is NULL. This makes logical sense
> because if you wanted to know if there were non-null entries
> you would say:
>
> SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);
>
> But I have long ago learned that NULL values in SQL rarely
> make logical sense, so I figure I better check.
>
> Can somebody please confirm that this is the correct behavior
> and that EXISTS does not do any special treatment of NULL
> values? Can somebody tell me what MySQL, PostgreSQL, Oracle,
> and Firebird do in this case?
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
Firebird 2.0:
SQL> CREATE TABLE t1(x INTEGER);
SQL> INSERT INTO t1 VALUES(NULL);
SQL> SELECT EXISTS(SELECT x FROM t1);
Statement failed, SQLCODE = -104
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 8
-EXISTS
SQL> select count(*) from t1 where exists (select x from t1);
COUNT
============
1
--
Nemanja Čorlija <[EMAIL PROTECTED]>
