Import SQLite 3.10.2.

third_party/sqlite/src/test/where.test

 # 2001 September 15
 #
 # 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.
 #
 #***********************************************************************
@@ skipping 24 matching lines @@
       INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
     }
   } else {
     set maxy [execsql {select max(y) from t1}]
     execsql "
       INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1;
     "
   }
 
   execsql {
-    CREATE INDEX i1w ON t1(w);
-    CREATE INDEX i1xy ON t1(x,y);
+    CREATE INDEX i1w ON t1("w");  -- Verify quoted identifier names
+    CREATE INDEX i1xy ON t1(`x`,'y' ASC); -- Old MySQL compatibility
     CREATE INDEX i2p ON t2(p);
     CREATE INDEX i2r ON t2(r);
     CREATE INDEX i2qs ON t2(q, s);
   }
 } {}
 
 # Do an SQL statement.  Append the search count to the end of the result.
 #
 proc count sql {
   set ::sqlite_search_count 0
   return [concat [execsql $sql] $::sqlite_search_count]
 }
 
 # 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 where-1.1.1 {
   count {SELECT x, y, w FROM t1 WHERE w=10}
 } {3 121 10 3}
+do_test where-1.1.1b {
+  count {SELECT x, y, w FROM t1 WHERE w IS 10}
+} {3 121 10 3}
 do_eqp_test where-1.1.2 {
   SELECT x, y, w FROM t1 WHERE w=10
 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
+do_eqp_test where-1.1.2b {
+  SELECT x, y, w FROM t1 WHERE w IS 10
+} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 do_test where-1.1.3 {
   db status step
 } {0}
 do_test where-1.1.4 {
   db eval {SELECT x, y, w FROM t1 WHERE +w=10}
 } {3 121 10}
 do_test where-1.1.5 {
   db status step
 } {99}
 do_eqp_test where-1.1.6 {
@@ skipping 13 matching lines @@
 } {3 144 11 3}
 do_test where-1.2.2 {
   count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}
 } {3 144 11 3}
 do_test where-1.3.1 {
   count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
 } {3 144 11 3}
 do_test where-1.3.2 {
   count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
 } {3 144 11 3}
+do_test where-1.3.3 {
+  count {SELECT x, y, w AS abc FROM t1 WHERE 11 IS abc}
+} {3 144 11 3}
 do_test where-1.4.1 {
   count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
 } {11 3 144 3}
+do_test where-1.4.1b {
+  count {SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2}
+} {11 3 144 3}
 do_eqp_test where-1.4.2 {
   SELECT w, x, y FROM t1 WHERE 11=w AND x>2
 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
+do_eqp_test where-1.4.2b {
+  SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2
+} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 do_test where-1.4.3 {
   count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
 } {11 3 144 3}
 do_eqp_test where-1.4.4 {
   SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 do_test where-1.5 {
   count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
 } {3 144 3}
 do_eqp_test where-1.5.2 {
@@ skipping 16 matching lines @@
 } {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?)*}
 do_test where-1.9 {
   count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
 } {3 144 3}
 do_test where-1.10 {
   count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
 } {3 121 3}
 do_test where-1.11 {
   count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
 } {3 100 3}
+do_test where-1.11b {
+  count {SELECT x, y FROM t1 WHERE x IS 3 AND y IS 100 AND w<10}
+} {3 100 3}
 
 # New for SQLite version 2.1: Verify that that inequality constraints
 # are used correctly.
 #
 do_test where-1.12 {
   count {SELECT w FROM t1 WHERE x=3 AND y<100}
 } {8 3}
+do_test where-1.12b {
+  count {SELECT w FROM t1 WHERE x IS 3 AND y<100}
+} {8 3}
 do_test where-1.13 {
   count {SELECT w FROM t1 WHERE x=3 AND 100>y}
 } {8 3}
 do_test where-1.14 {
   count {SELECT w FROM t1 WHERE 3=x AND y<100}
 } {8 3}
+do_test where-1.14b {
+  count {SELECT w FROM t1 WHERE 3 IS x AND y<100}
+} {8 3}
 do_test where-1.15 {
   count {SELECT w FROM t1 WHERE 3=x AND 100>y}
 } {8 3}
 do_test where-1.16 {
   count {SELECT w FROM t1 WHERE x=3 AND y<=100}
 } {8 9 5}
 do_test where-1.17 {
   count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
 } {8 9 5}
 do_test where-1.18 {
   count {SELECT w FROM t1 WHERE x=3 AND y>225}
 } {15 3}
+do_test where-1.18b {
+  count {SELECT w FROM t1 WHERE x IS 3 AND y>225}
+} {15 3}
 do_test where-1.19 {
   count {SELECT w FROM t1 WHERE x=3 AND 225<y}
 } {15 3}
 do_test where-1.20 {
   count {SELECT w FROM t1 WHERE x=3 AND y>=225}
 } {14 15 5}
 do_test where-1.21 {
   count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
 } {14 15 5}
 do_test where-1.22 {
   count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
 } {11 12 5}
+do_test where-1.22b {
+  count {SELECT w FROM t1 WHERE x IS 3 AND y>121 AND y<196}
+} {11 12 5}
 do_test where-1.23 {
   count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
 } {10 11 12 13 9}
 do_test where-1.24 {
   count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
 } {11 12 5}
 do_test where-1.25 {
   count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
 } {10 11 12 13 9}
 
@@ skipping 182 matching lines @@
   } {1 0 4 2 1 9 3 1 16 4}
   do_test where-5.2 {
     count {
       SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
     }
   } {1 0 4 2 1 9 3 1 16 102}
   do_test where-5.3a {
     count {
       SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
     }
-  } {1 0 4 2 1 9 3 1 16 13}
+  } {1 0 4 2 1 9 3 1 16 12}
   do_test where-5.3b {
     count {
       SELECT * FROM t1 WHERE w IN (3,-1,1,2) order by 1;
     }
-  } {1 0 4 2 1 9 3 1 16 13}
+  } {1 0 4 2 1 9 3 1 16 12}
   do_test where-5.3c {
     count {
       SELECT * FROM t1 WHERE w IN (3,2,-1,1,2) order by 1;
     }
-  } {1 0 4 2 1 9 3 1 16 13}
+  } {1 0 4 2 1 9 3 1 16 12}
   do_test where-5.3d {
     count {
       SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1 DESC;
     }
-  } {3 1 16 2 1 9 1 0 4 12}
+  } {3 1 16 2 1 9 1 0 4 11}
   do_test where-5.4 {
     count {
       SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
     }
   } {1 0 4 2 1 9 3 1 16 102}
   do_test where-5.5 {
     count {
       SELECT * FROM t1 WHERE rowid IN 
          (select rowid from t1 where rowid IN (-1,2,4))
       ORDER BY 1;
@@ skipping 17 matching lines @@
     count {
       SELECT * FROM t1 WHERE w+0 IN 
          (select rowid from t1 where rowid IN (-1,2,4))
       ORDER BY 1;
     }
   } {2 1 9 4 2 25 103}
   do_test where-5.9 {
     count {
       SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
     }
-  } {2 1 9 3 1 16 7}
+  } {2 1 9 3 1 16 6}
   do_test where-5.10 {
     count {
       SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
     }
   } {2 1 9 3 1 16 199}
   do_test where-5.11 {
     count {
       SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
     }
   } {79 6 6400 89 6 8100 199}
   do_test where-5.12 {
     count {
       SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
     }
   } {79 6 6400 89 6 8100 7}
   do_test where-5.13 {
     count {
       SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
     }
-  } {2 1 9 3 1 16 7}
+  } {2 1 9 3 1 16 6}
   do_test where-5.14 {
     count {
       SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
     }
-  } {2 1 9 8}
+  } {2 1 9 5}
   do_test where-5.15 {
     count {
       SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
     }
-  } {2 1 9 3 1 16 11}
+  } {2 1 9 3 1 16 9}
   do_test where-5.100 {
     db eval {
       SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
        ORDER BY x, y
     }
   } {2 1 9 54 5 3025 62 5 3969}
   do_test where-5.101 {
     db eval {
       SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
        ORDER BY x DESC, y DESC
@@ skipping 848 matching lines @@
   INSERT INTO t181 VALUES(2);
   SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
 } {1 2}
 do_execsql_test where-18.6 {
   INSERT INTO t181 VALUES(2);
   SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL;
 } {1 2}
 
 
 finish_test