NULL values are distinct. A comparison involving a NULL is always false.

Operations on a NULL value yield a NULL result.  This change makes SQLite
operate more like the SQL spec, but it may break existing applications that
assumed the old behavior.  All the old tests pass but we still need to add
new tests to better verify the new behavior.  Fix for ticket #44. (CVS 589)

FossilOrigin-Name: 9051173742f1b0e15a809d12a0c9c98fd2c4614d

5 files changed, 97 insertions, 24 deletions

diff --git a/test/expr.test b/test/expr.test
index 784b80298..0d93df662 100644
--- a/test/expr.test
+++ b/test/expr.test
@@ -11,7 +11,7 @@
 # This file implements regression tests for SQLite library.  The
 # focus of this file is testing expressions.
 #
-# $Id: expr.test,v 1.19 2002/03/24 13:13:29 drh Exp $
+# $Id: expr.test,v 1.20 2002/05/26 20:54:34 drh Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
@@ -22,8 +22,7 @@ execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
 execsql {INSERT INTO test1 VALUES(1,2,1.1,2.2,'hello','world')}
 proc test_expr {name settings expr result} {
   do_test $name [format {
-    execsql {UPDATE test1 SET %s}
-    execsql {SELECT %s FROM test1}
+    execsql {BEGIN; UPDATE test1 SET %s; SELECT %s FROM test1; ROLLBACK;}
   } $settings $expr] $result
 }
 
@@ -63,7 +62,7 @@ test_expr expr-1.33 {i1=1, i2=2} {i1=1 OR i2=1} {1}
 test_expr expr-1.34 {i1=1, i2=2} {i1=2 OR i2=2} {1}
 test_expr expr-1.35 {i1=1, i2=2} {i1-i2=-1} {1}
 test_expr expr-1.36 {i1=1, i2=0} {not i1} {0}
-test_expr expr-1.37 {i1=1, i2=NULL} {not i2} {1}
+test_expr expr-1.37 {i1=1, i2=0} {not i2} {1}
 test_expr expr-1.38 {i1=1} {-i1} {-1}
 test_expr expr-1.39 {i1=1} {+i1} {1}
 test_expr expr-1.40 {i1=1, i2=2} {+(i2+i1)} {3}
@@ -320,8 +319,8 @@ proc test_expr2 {name expr result} {
 test_expr2 expr-7.2  {a<10 AND a>8}                  {9}
 test_expr2 expr-7.3  {a<=10 AND a>=8}                {8 9 10}
 test_expr2 expr-7.4  {a>=8 AND a<=10}                {8 9 10}
-test_expr2 expr-7.5  {a>=20 OR a<=1}                 {{} 1 20}
-test_expr2 expr-7.6  {b!=4 AND a<=3}                 {{} 1 3}
+test_expr2 expr-7.5  {a>=20 OR a<=1}                 {1 20}
+test_expr2 expr-7.6  {b!=4 AND a<=3}                 {1 3}
 test_expr2 expr-7.7  {b==8 OR b==16 OR b==32}        {3 4 5}
 test_expr2 expr-7.8  {NOT b<>8 OR b==1024}           {3 10}
 test_expr2 expr-7.9  {b LIKE '10%'}                  {10 20}
@@ -332,13 +331,13 @@ test_expr2 expr-7.13 {b GLOB '*1[456]'}              {4}
 test_expr2 expr-7.14 {a ISNULL}                      {{}}
 test_expr2 expr-7.15 {a NOTNULL AND a<3}             {1 2}
 test_expr2 expr-7.16 {a AND a<3}                     {1 2}
-test_expr2 expr-7.17 {NOT a}                         {{}}
+test_expr2 expr-7.17 {NOT a}                         {}
 test_expr2 expr-7.18 {a==11 OR (b>1000 AND b<2000)}  {10 11}
-test_expr2 expr-7.19 {a<=1 OR a>=20}                 {{} 1 20}
-test_expr2 expr-7.20 {a<1 OR a>20}                   {{}}
-test_expr2 expr-7.21 {a>19 OR a<1}                   {{} 20}
+test_expr2 expr-7.19 {a<=1 OR a>=20}                 {1 20}
+test_expr2 expr-7.20 {a<1 OR a>20}                   {}
+test_expr2 expr-7.21 {a>19 OR a<1}                   {20}
 test_expr2 expr-7.22 {a!=1 OR a=100} \
-                         {{} 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}
+                         {2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}
 test_expr2 expr-7.23 {(a notnull AND a<4) OR a==8}   {1 2 3 8}
 test_expr2 expr-7.24 {a LIKE '2_' OR a==8}           {8 20}
 test_expr2 expr-7.25 {a GLOB '2?' OR a==8}           {8 20}
diff --git a/test/select4.test b/test/select4.test
index 3a32dc0de..2b47b3574 100644
--- a/test/select4.test
+++ b/test/select4.test
@@ -12,7 +12,7 @@
 # focus of this file is testing UNION, INTERSECT and EXCEPT operators
 # in SELECT statements.
 #
-# $Id: select4.test,v 1.6 2002/05/24 16:14:16 drh Exp $
+# $Id: select4.test,v 1.7 2002/05/26 20:54:35 drh Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
@@ -249,6 +249,48 @@ do_test select4-6.2 {
   }
 } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
 
+# NULLs are distinct.  Make sure the UNION operator recognizes this
+#
+do_test select4-6.3 {
+  execsql {
+    SELECT NULL UNION SELECT NULL UNION
+    SELECT 1 UNION SELECT 2 AS 'x'
+    ORDER BY x;
+  }
+} {{} {} 1 2}
+do_test select4-6.3 {
+  execsql {
+    SELECT NULL UNION ALL SELECT NULL UNION ALL
+    SELECT 1 UNION ALL SELECT 2 AS 'x'
+    ORDER BY x;
+  }
+} {{} {} 1 2}
+
+# Make sure the DISTINCT keyword treats NULLs as DISTINCT
+#
+do_test select4-6.4 {
+  execsql {
+    SELECT * FROM (
+       SELECT NULL, 1 UNION ALL SELECT NULL, 1
+    );
+  }
+} {{} 1 {} 1}
+do_test select4-6.5 {
+  execsql {
+    SELECT DISTINCT * FROM (
+       SELECT NULL, 1 UNION ALL SELECT NULL, 1
+    );
+  }
+} {{} 1 {} 1}
+do_test select4-6.6 {
+  execsql {
+    SELECT DISTINCT * FROM (
+       SELECT 1,2  UNION ALL SELECT 1,2
+    );
+  }
+} {1 2}
+
+
 # Make sure column names are correct when a compound select appears as
 # an expression in the WHERE clause.
 #
diff --git a/test/subselect.test b/test/subselect.test
index e2f2559f5..392ab5319 100644
--- a/test/subselect.test
+++ b/test/subselect.test
@@ -12,7 +12,7 @@
 # focus of this file is testing SELECT statements that are part of
 # expressions.
 #
-# $Id: subselect.test,v 1.4 2001/09/16 00:13:28 drh Exp $
+# $Id: subselect.test,v 1.5 2002/05/26 20:54:35 drh Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
@@ -55,9 +55,8 @@ do_test subselect-1.3c {
 # NULL as the result.  Check it out.
 #
 do_test subselect-1.4 {
-  execsql {INSERT INTO t1 VALUES(NULL,8)}
-  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=5)}
-} {8}
+  execsql {SELECT b from t1 where a = coalesce((SELECT a FROM t1 WHERE b=5),1)}
+} {2}
 
 # Try multiple subselects within a single expression.
 #
diff --git a/test/trigger2.test b/test/trigger2.test
index adce11450..ae103ea85 100644
--- a/test/trigger2.test
+++ b/test/trigger2.test
@@ -77,7 +77,7 @@ foreach tbl_defn [ list \
 
     CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 
       BEGIN
-      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
+      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
 	  old.a, old.b, 
 	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
 	  new.a, new.b);
@@ -85,7 +85,7 @@ foreach tbl_defn [ list \
 
     CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 
       BEGIN
-      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
+      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
 	  old.a, old.b, 
 	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
 	  new.a, new.b);
@@ -94,7 +94,7 @@ foreach tbl_defn [ list \
     CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
       WHEN old.a = 1
       BEGIN
-      INSERT INTO clog VALUES ( (SELECT max(idx) + 1 FROM clog), 
+      INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 
 	  old.a, old.b, 
 	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
 	  new.a, new.b);
@@ -120,7 +120,7 @@ foreach tbl_defn [ list \
     INSERT INTO tbl VALUES (300, 200);
     CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
       BEGIN
-      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
+      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
 	  old.a, old.b, 
 	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
 	  0, 0);
@@ -128,7 +128,7 @@ foreach tbl_defn [ list \
 
     CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
       BEGIN
-      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
+      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
 	  old.a, old.b, 
 	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
 	  0, 0);
@@ -148,7 +148,7 @@ foreach tbl_defn [ list \
     DELETE FROM rlog;
     CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
       BEGIN
-      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
+      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
 	  0, 0,
 	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
 	  new.a, new.b);
@@ -156,7 +156,7 @@ foreach tbl_defn [ list \
 
     CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
       BEGIN
-      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
+      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
 	  0, 0,
 	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
 	  new.a, new.b);
diff --git a/test/unique.test b/test/unique.test
index 146ddf028..27cb3e716 100644
--- a/test/unique.test
+++ b/test/unique.test
@@ -12,7 +12,7 @@
 # focus of this file is testing the CREATE UNIQUE INDEX statement,
 # and primary keys, and the UNIQUE constraint on table columns
 #
-# $Id: unique.test,v 1.3 2001/12/21 14:30:44 drh Exp $
+# $Id: unique.test,v 1.4 2002/05/26 20:54:35 drh Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
@@ -162,4 +162,37 @@ do_test unique-3.4 {
   }
 } {1 {constraint failed}}
 
+# Make sure NULLs are distinct as far as the UNIQUE tests are
+# concerned.
+#
+do_test unique-4.1 {
+  execsql {
+    CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));
+    INSERT INTO t4 VALUES(1,2,3);
+    INSERT INTO t4 VALUES(NULL, 2, NULL);
+    SELECT * FROM t4;
+  }
+} {1 2 3 {} 2 {}}
+do_test unique-4.2 {
+  catchsql {
+    INSERT INTO t4 VALUES(NULL, 3, 4);
+  }
+} {0 {}}
+do_test unique-4.3 {
+  execsql {
+    SELECT * FROM t4
+  }
+} {1 2 3 {} 2 {} {} 3 4}
+do_test unique-4.4 {
+  catchsql {
+    INSERT INTO t4 VALUES(2, 2, NULL);
+  }
+} {0 {}}
+do_test unique-4.5 {
+  execsql {
+    SELECT * FROM t4
+  }
+} {1 2 3 {} 2 {} {} 3 4 2 2 {}}
+
+
 finish_test