Import SQLite 3.10.2.

third_party/sqlite/src/test/where4.test

 # 2006 October 27
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #***********************************************************************
 # This file implements regression tests for SQLite library.  The
 # focus of this file is testing the use of indices in WHERE clauses.
 # This file was created when support for optimizing IS NULL phrases
 # was added.  And so the principle purpose of this file is to test
 # that IS NULL phrases are correctly optimized.  But you can never
 # have too many tests, so some other tests are thrown in as well.
 #
 # $Id: where4.test,v 1.6 2007/12/10 05:03:48 danielk1977 Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
+set testprefix where4
 
 ifcapable !tclvar||!bloblit {
   finish_test
   return
 }
 
 # Build some test data
 #
 do_test where4-1.0 {
   execsql {
@@ skipping 18 matching lines @@
 }
 
 # Verify that queries use an index.  We are using the special variable
 # "sqlite_search_count" which tallys the number of executions of MoveTo
 # and Next operators in the VDBE.  By verifing that the search count is
 # small we can be assured that indices are being used properly.
 #
 do_test where4-1.1 {
   count {SELECT rowid FROM t1 WHERE w IS NULL}
 } {7 2}
+do_test where4-1.1b {
+  unset -nocomplain null
+  count {SELECT rowid FROM t1 WHERE w IS $null}
+} {7 2}
 do_test where4-1.2 {
   count {SELECT rowid FROM t1 WHERE +w IS NULL}
 } {7 6}
 do_test where4-1.3 {
   count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL}
 } {2 2}
 do_test where4-1.4 {
   count {SELECT rowid FROM t1 WHERE w=1 AND +x IS NULL}
 } {2 3}
 do_test where4-1.5 {
   count {SELECT rowid FROM t1 WHERE w=1 AND x>0}
 } {1 2}
 do_test where4-1.6 {
   count {SELECT rowid FROM t1 WHERE w=1 AND x<9}
 } {1 2}
 do_test where4-1.7 {
   count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y=3}
 } {2 2}
 do_test where4-1.8 {
   count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y>2}
 } {2 2}
 do_test where4-1.9 {
   count {SELECT rowid FROM t1 WHERE w='a' AND x IS NULL AND y='c'}
 } {4 2}
 do_test where4-1.10 {
   count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL}
 } {6 2}
 do_test where4-1.11 {
   count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=123}
-} {1}
+} {0}
 do_test where4-1.12 {
   count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=x'7A'}
 } {6 2}
 do_test where4-1.13 {
   count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL}
 } {7 2}
 do_test where4-1.14 {
   count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y IS NULL}
 } {7 2}
 do_test where4-1.15 {
@@ skipping 24 matching lines @@
 # optimize the query because the second column might be NULL because
 # the right table did not match - something the index does not know
 # about.
 #
 do_test where4-3.1 {
   execsql {
     CREATE TABLE t2(a);
     INSERT INTO t2 VALUES(1);
     INSERT INTO t2 VALUES(2);
     INSERT INTO t2 VALUES(3);
-    CREATE TABLE t3(x,y,UNIQUE(x,y));
+    CREATE TABLE t3(x,y,UNIQUE("x",'y' ASC)); -- Goofy syntax allowed
     INSERT INTO t3 VALUES(1,11);
     INSERT INTO t3 VALUES(2,NULL);
  
     SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL;
   }
 } {2 2 {} 3 {} {}}
 do_test where4-3.2 {
   execsql {
     SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL;
   }
 } {2 2 {} 3 {} {}}
+do_test where4-3.3 {
+  execsql {
+    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE NULL is y;
+  }
+} {2 2 {} 3 {} {}}
+do_test where4-3.4 {
+  unset -nocomplain null
+  execsql {
+    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS $null;
+  }
+} {2 2 {} 3 {} {}}
 
 # Ticket #2189.  Probably the same bug as #2177.
 #
 do_test where4-4.1 {
   execsql {
     CREATE TABLE test(col1 TEXT PRIMARY KEY);
     INSERT INTO test(col1) values('a');
     INSERT INTO test(col1) values('b');
     INSERT INTO test(col1) values('c');
     CREATE TABLE test2(col1 TEXT PRIMARY KEY);
@@ skipping 21 matching lines @@
     SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
       WHERE t1.col1 IS NULL;
   }
 } {}
 
 # Ticket #2273.  Problems with IN operators and NULLs.
 #
 ifcapable subquery {
 do_test where4-5.1 {
   execsql {
-    CREATE TABLE t4(x,y,z,PRIMARY KEY(x,y));
+    -- Allow the 'x' syntax for backwards compatibility
+    CREATE TABLE t4(x,y,z,PRIMARY KEY('x' ASC, "y" ASC));
   }
   execsql {
     SELECT *
       FROM t2 LEFT JOIN t4 b1
               LEFT JOIN t4 b2 ON b2.x=b1.x AND b2.y IN (b1.y);
   }
 } {1 {} {} {} {} {} {} 2 {} {} {} {} {} {} 3 {} {} {} {} {} {}}
 do_test where4-5.2 {
   execsql {
     INSERT INTO t4 VALUES(1,1,11);
@@ skipping 65 matching lines @@
 do_test where4-7.2 {
   execsql {
     SELECT sum((
       SELECT d FROM t8 WHERE a = i AND b = i AND c < NULL
     )) FROM t7;
   }
 } {{}}
 
 }; #ifcapable subquery
 
+#-------------------------------------------------------------------------
+# Verify that "IS ?" with a NULL bound to the variable also functions
+# correctly.
+
+unset -nocomplain null
+
+do_execsql_test 8.1 {
+  CREATE TABLE u9(a UNIQUE, b);
+  INSERT INTO u9 VALUES(NULL, 1);
+  INSERT INTO u9 VALUES(NULL, 2);
+}
+do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS NULL  } {{} 1 {} 2}
+do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS $null } {{} 1 {} 2}
+
+
+
+
 finish_test