Import SQLite 3.10.2.

third_party/sqlite/src/test/where.test

Index: third_party/sqlite/src/test/where.test
diff --git a/third_party/sqlite/src/test/where.test b/third_party/sqlite/src/test/where.test
index f560708cca0e92165d245af23e55cfad92811665..0b5bb934d06b9c5c39c5ab453677d314ab3dae17 100644
--- a/third_party/sqlite/src/test/where.test
+++ b/third_party/sqlite/src/test/where.test
@@ -42,8 +42,8 @@ do_test where-1.0 {
   }
 
   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);
@@ -65,9 +65,15 @@ proc count sql {
 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}
@@ -101,12 +107,21 @@ do_test where-1.3.1 {
 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}
@@ -143,6 +158,9 @@ do_test where-1.10 {
 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.
@@ -150,12 +168,18 @@ do_test where-1.11 {
 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}
@@ -168,6 +192,9 @@ do_test where-1.17 {
 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}
@@ -180,6 +207,9 @@ do_test where-1.21 {
 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}
@@ -382,22 +412,22 @@ ifcapable subquery {
     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;
@@ -435,7 +465,7 @@ ifcapable subquery {
     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;
@@ -455,17 +485,17 @@ ifcapable subquery {
     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)