[sql] Import SQLite 3.17.0.

third_party/sqlite/src/test/skipscan1.test

 # 2013-11-13
 #
 # 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 25 matching lines @@
 } {}
 
 # Simple queries that leave the first one or two columns of the
 # index unconstrainted.
 #
 do_execsql_test skipscan1-1.2 {
   SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
 } {abc 345 7 8 | def 345 9 10 |}
 do_execsql_test skipscan1-1.2eqp {
   EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
+  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a;
 } {/* USING INDEX t1abc (ANY(a) AND b=?)*/}
 do_execsql_test skipscan1-1.2sort {
   EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
+  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a;
 } {~/*ORDER BY*/}
 
 do_execsql_test skipscan1-1.3 {
-  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a DESC;
+  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC;
 } {def 345 9 10 | abc 345 7 8 |}
 do_execsql_test skipscan1-1.3eqp {
   EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
+  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC;
 } {/* USING INDEX t1abc (ANY(a) AND b=?)*/}
 do_execsql_test skipscan1-1.3sort {
   EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a;
+  SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC;
 } {~/*ORDER BY*/}
 
 do_execsql_test skipscan1-1.4 {
   SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c;
 } {abc 234 6 7 | bcd 100 6 11 |}
 do_execsql_test skipscan1-1.4eqp {
   EXPLAIN QUERY PLAN
   SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c;
 } {/* USING INDEX t1abc (ANY(a) AND ANY(b) AND c=?)*/}
 do_execsql_test skipscan1-1.4sort {
@@ skipping 70 matching lines @@
 
   /* Fake the sqlite_stat1 table so that the query planner believes
   ** the table contains thousands of rows and that the first few
   ** columns are not selective. */
   ANALYZE;
   UPDATE sqlite_stat1 SET stat='10000 5000 2000 10' WHERE idx NOT NULL;
   ANALYZE sqlite_master;
 } {}
 
 do_execsql_test skipscan1-2.2 {
-  SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a;
+  SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a;
 } {abc 345 7 8 | def 345 9 10 |}
 do_execsql_test skipscan1-2.2eqp {
   EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a;
+  SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a;
 } {/* USING INDEX sqlite_autoindex_t2_1 (ANY(a) AND b=?)*/}
 do_execsql_test skipscan1-2.2sort {
   EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a;
+  SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a;
 } {~/*ORDER BY*/}
 
 
 do_execsql_test skipscan1-3.1 {
   CREATE TABLE t3(a TEXT, b INT, c INT, d INT,
                   PRIMARY KEY(a,b,c)) WITHOUT ROWID;
   INSERT INTO t3 SELECT * FROM t1;
 
   /* Fake the sqlite_stat1 table so that the query planner believes
   ** the table contains thousands of rows and that the first few
@@ skipping 143 matching lines @@
   SELECT * FROM t1
    WHERE (y = 'AB' AND x <= 4)
       OR (y = 'EF' AND x = 5);
 } {/ANY/}
 do_execsql_test skipscan1-8.2 {
   SELECT * FROM t1
    WHERE y = 'AB' OR (y = 'CD' AND x = 2)
   ORDER BY +x;
 } {1 AB 2 CD}
 
+# Segfault reported on the mailing list by Keith Medcalf on 2016-09-18.
+# A skip-scan with a "column IN (SELECT ...)" on the second term of the
+# index.
+#
+do_execsql_test skipscan1-9.2 {
+  CREATE TABLE t9a(a,b,c);
+  CREATE INDEX t9a_ab ON t9a(a,b);
+  CREATE TABLE t9b(x,y);
+  ANALYZE sqlite_master;
+  INSERT INTO sqlite_stat1 VALUES('t9a','t9a_ab','1000000 250000 1');
+  ANALYZE sqlite_master;
+  EXPLAIN QUERY PLAN
+  SELECT * FROM t9a WHERE b IN (SELECT x FROM t9b WHERE y!=5);
+} {/USING INDEX t9a_ab .ANY.a. AND b=./}
+
 finish_test