[sqlite] Remove obsolete reference version 3.8.7.4.

third_party/sqlite/sqlite-src-3080704/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.
-#
-#***********************************************************************
-#
-# This file implements tests of the "skip-scan" query strategy.
-#
-
-set testdir [file dirname $argv0]
-source $testdir/tester.tcl
-
-do_execsql_test skipscan1-1.1 {
-  CREATE TABLE t1(a TEXT, b INT, c INT, d INT);
-  CREATE INDEX t1abc ON t1(a,b,c);
-  INSERT INTO t1 VALUES('abc',123,4,5);
-  INSERT INTO t1 VALUES('abc',234,5,6);
-  INSERT INTO t1 VALUES('abc',234,6,7);
-  INSERT INTO t1 VALUES('abc',345,7,8);
-  INSERT INTO t1 VALUES('def',567,8,9);
-  INSERT INTO t1 VALUES('def',345,9,10);
-  INSERT INTO t1 VALUES('bcd',100,6,11);
-
-  /* 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;
-  DELETE FROM sqlite_stat1;
-  INSERT INTO sqlite_stat1 VALUES('t1','t1abc','10000 5000 2000 10');
-  ANALYZE sqlite_master;
-} {}
-
-# 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;
-} {/* 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;
-} {~/*ORDER BY*/}
-
-do_execsql_test skipscan1-1.3 {
-  SELECT a,b,c,d,'|' FROM t1 WHERE 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;
-} {/* 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;
-} {~/*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 {
-  EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c;
-} {~/*ORDER BY*/}
-
-do_execsql_test skipscan1-1.5 {
-  SELECT a,b,c,d,'|' FROM t1 WHERE c IN (6,7) ORDER BY a, b, c;
-} {abc 234 6 7 | abc 345 7 8 | bcd 100 6 11 |}
-do_execsql_test skipscan1-1.5eqp {
-  EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE c IN (6,7) ORDER BY a, b, c;
-} {/* USING INDEX t1abc (ANY(a) AND ANY(b) AND c=?)*/}
-do_execsql_test skipscan1-1.5sort {
-  EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE c IN (6,7) ORDER BY a, b, c;
-} {~/*ORDER BY*/}
-
-do_execsql_test skipscan1-1.6 {
-  SELECT a,b,c,d,'|' FROM t1 WHERE c BETWEEN 6 AND 7 ORDER BY a, b, c;
-} {abc 234 6 7 | abc 345 7 8 | bcd 100 6 11 |}
-do_execsql_test skipscan1-1.6eqp {
-  EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE c BETWEEN 6 AND 7 ORDER BY a, b, c;
-} {/* USING INDEX t1abc (ANY(a) AND ANY(b) AND c>? AND c<?)*/}
-do_execsql_test skipscan1-1.6sort {
-  EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE c BETWEEN 6 AND 7 ORDER BY a, b, c;
-} {~/*ORDER BY*/}
-
-do_execsql_test skipscan1-1.7 {
-  SELECT a,b,c,d,'|' FROM t1 WHERE b IN (234, 345) AND c BETWEEN 6 AND 7
-   ORDER BY a, b;
-} {abc 234 6 7 | abc 345 7 8 |}
-do_execsql_test skipscan1-1.7eqp {
-  EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE b IN (234, 345) AND c BETWEEN 6 AND 7
-   ORDER BY a, b;
-} {/* USING INDEX t1abc (ANY(a) AND b=? AND c>? AND c<?)*/}
-do_execsql_test skipscan1-1.7sort {
-  EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t1 WHERE b IN (234, 345) AND c BETWEEN 6 AND 7
-   ORDER BY a, b;
-} {~/*ORDER BY*/}
-
-
-# Joins
-#
-do_execsql_test skipscan1-1.51 {
-  CREATE TABLE t1j(x TEXT, y INTEGER);
-  INSERT INTO t1j VALUES('one',1),('six',6),('ninty-nine',99);
-  INSERT INTO sqlite_stat1 VALUES('t1j',null,'3');
-  ANALYZE sqlite_master;
-  SELECT x, a, b, c, d, '|' FROM t1j, t1 WHERE c=y ORDER BY +a;
-} {six abc 234 6 7 | six bcd 100 6 11 |}
-do_execsql_test skipscan1-1.51eqp {
-  EXPLAIN QUERY PLAN
-  SELECT x, a, b, c, d, '|' FROM t1j, t1 WHERE c=y ORDER BY +a;
-} {/* INDEX t1abc (ANY(a) AND ANY(b) AND c=?)*/}
-
-do_execsql_test skipscan1-1.52 {
-  SELECT x, a, b, c, d, '|' FROM t1j LEFT JOIN t1 ON c=y ORDER BY +y, +a;
-} {one {} {} {} {} | six abc 234 6 7 | six bcd 100 6 11 | ninty-nine {} {} {} {} |}
-do_execsql_test skipscan1-1.52eqp {
-  EXPLAIN QUERY PLAN
-  SELECT x, a, b, c, d, '|' FROM t1j LEFT JOIN t1 ON c=y ORDER BY +y, +a;
-} {/* INDEX t1abc (ANY(a) AND ANY(b) AND c=?)*/}
-
-do_execsql_test skipscan1-2.1 {
-  CREATE TABLE t2(a TEXT, b INT, c INT, d INT,
-                  PRIMARY KEY(a,b,c));
-  INSERT INTO t2 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
-  ** 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;
-} {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;
-} {/* 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;
-} {~/*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
-  ** 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-3.2 {
-  SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a;
-} {abc 345 7 8 | def 345 9 10 |}
-do_execsql_test skipscan1-3.2eqp {
-  EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a;
-} {/* PRIMARY KEY (ANY(a) AND b=?)*/}
-do_execsql_test skipscan1-3.2sort {
-  EXPLAIN QUERY PLAN
-  SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a;
-} {~/*ORDER BY*/}
-
-# Ticket 520070ec7fbaac: Array overrun in the skip-scan optimization
-# 2013-12-22
-#
-do_execsql_test skipscan1-4.1 {
-  CREATE TABLE t4(a,b,c,d,e,f,g,h,i);
-  CREATE INDEX t4all ON t4(a,b,c,d,e,f,g,h);
-  INSERT INTO t4 VALUES(1,2,3,4,5,6,7,8,9);
-  ANALYZE;
-  DELETE FROM sqlite_stat1;
-  INSERT INTO sqlite_stat1 
-    VALUES('t4','t4all','655360 163840 40960 10240 2560 640 160 40 10');
-  ANALYZE sqlite_master;
-  SELECT i FROM t4 WHERE a=1;
-  SELECT i FROM t4 WHERE b=2;
-  SELECT i FROM t4 WHERE c=3;
-  SELECT i FROM t4 WHERE d=4;
-  SELECT i FROM t4 WHERE e=5;
-  SELECT i FROM t4 WHERE f=6;
-  SELECT i FROM t4 WHERE g=7;
-  SELECT i FROM t4 WHERE h=8;
-} {9 9 9 9 9 9 9 9}
-
-# Make sure skip-scan cost computation in the query planner takes into
-# account the fact that the seek must occur multiple times.
-#
-# Prior to 2014-03-10, the costs were computed incorrectly which would
-# cause index t5i2 to be used instead of t5i1 on the skipscan1-5.3.
-#
-do_execsql_test skipscan1-5.1 {
-  CREATE TABLE t5(
-    id INTEGER PRIMARY KEY,
-    loc TEXT,
-    lang INTEGER,
-    utype INTEGER,
-    xa INTEGER,
-    xd INTEGER,
-    xh INTEGER
-  );
-  CREATE INDEX t5i1 on t5(loc, xh, xa, utype, lang);
-  CREATE INDEX t5i2 ON t5(xd,loc,utype,lang);
-  EXPLAIN QUERY PLAN
-    SELECT xh, loc FROM t5 WHERE loc >= 'M' AND loc < 'N';
-} {/.*COVERING INDEX t5i1 .*/}
-do_execsql_test skipscan1-5.2 {
-  ANALYZE;
-  DELETE FROM sqlite_stat1;
-  DROP TABLE IF EXISTS sqlite_stat4;
-  DROP TABLE IF EXISTS sqlite_stat3;
-  INSERT INTO sqlite_stat1 VALUES('t5','t5i1','2702931 3 2 2 2 2');
-  INSERT INTO sqlite_stat1 VALUES('t5','t5i2','2702931 686 2 2 2');
-  ANALYZE sqlite_master;
-} {}
-db cache flush
-do_execsql_test skipscan1-5.3 {
-  EXPLAIN QUERY PLAN
-    SELECT xh, loc FROM t5 WHERE loc >= 'M' AND loc < 'N';
-} {/.*COVERING INDEX t5i1 .*/}
-
-# The column used by the skip-scan needs to be sufficiently selective.
-# See the private email from Adi Zaimi to drh@sqlite.org on 2014-09-22.
-#
-db close
-forcedelete test.db
-sqlite3 db test.db
-do_execsql_test skipscan1-6.1 {
-  CREATE TABLE t1(a,b,c,d,e,f,g,h varchar(300));
-  CREATE INDEX t1ab ON t1(a,b);
-  ANALYZE sqlite_master;
-  -- Only two distinct values for the skip-scan column.  Skip-scan is not used.
-  INSERT INTO sqlite_stat1 VALUES('t1','t1ab','500000 250000 125000');
-  ANALYZE sqlite_master;
-  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
-} {~/ANY/}
-do_execsql_test skipscan1-6.2 {
-  -- Four distinct values for the skip-scan column.  Skip-scan is used.
-  UPDATE sqlite_stat1 SET stat='500000 250000 62500';
-  ANALYZE sqlite_master;
-  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
-} {/ANY.a. AND b=/}
-do_execsql_test skipscan1-6.3 {
-  -- Two distinct values for the skip-scan column again.  Skip-scan is not used.
-  UPDATE sqlite_stat1 SET stat='500000 125000 62500';
-  ANALYZE sqlite_master;
-  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
-} {~/ANY/}
-
-finish_test