[sqlite] Remove obsolete reference version 3.8.7.4.

third_party/sqlite/sqlite-src-3080704/test/skipscan2.test

-# 2013-11-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 tests of the "skip-scan" query strategy.
-#
-# The test cases in this file are derived from the description of
-# the skip-scan query strategy in the "optoverview.html" document.
-#
-
-set testdir [file dirname $argv0]
-source $testdir/tester.tcl
-
-do_execsql_test skipscan2-1.1 {
-  CREATE TABLE people(
-    name TEXT PRIMARY KEY,
-    role TEXT NOT NULL,
-    height INT NOT NULL, -- in cm
-    CHECK( role IN ('student','teacher') )
-  );
-  CREATE INDEX people_idx1 ON people(role, height);
-} {}
-do_execsql_test skipscan2-1.2 {
-  INSERT INTO people VALUES('Alice','student',156);
-  INSERT INTO people VALUES('Bob','student',161);
-  INSERT INTO people VALUES('Cindy','student',155);
-  INSERT INTO people VALUES('David','student',181);
-  INSERT INTO people VALUES('Emily','teacher',158);
-  INSERT INTO people VALUES('Fred','student',163);
-  INSERT INTO people VALUES('Ginny','student',169);
-  INSERT INTO people VALUES('Harold','student',172);
-  INSERT INTO people VALUES('Imma','student',179);
-  INSERT INTO people VALUES('Jack','student',181);
-  INSERT INTO people VALUES('Karen','student',163);
-  INSERT INTO people VALUES('Logan','student',177);
-  INSERT INTO people VALUES('Megan','teacher',159);
-  INSERT INTO people VALUES('Nathan','student',163);
-  INSERT INTO people VALUES('Olivia','student',161);
-  INSERT INTO people VALUES('Patrick','teacher',180);
-  INSERT INTO people VALUES('Quiana','student',182);
-  INSERT INTO people VALUES('Robert','student',159);
-  INSERT INTO people VALUES('Sally','student',166);
-  INSERT INTO people VALUES('Tom','student',171);
-  INSERT INTO people VALUES('Ursula','student',170);
-  INSERT INTO people VALUES('Vance','student',179);
-  INSERT INTO people VALUES('Willma','student',175);
-  INSERT INTO people VALUES('Xavier','teacher',185);
-  INSERT INTO people VALUES('Yvonne','student',149);
-  INSERT INTO people VALUES('Zach','student',170);
-}
-
-# Without ANALYZE, a skip-scan is not used
-#
-do_execsql_test skipscan2-1.3 {
-  SELECT name FROM people WHERE height>=180 ORDER BY +name;
-} {David Jack Patrick Quiana Xavier}
-do_execsql_test skipscan2-1.3eqp {
-  EXPLAIN QUERY PLAN
-  SELECT name FROM people WHERE height>=180 ORDER BY +name;
-} {~/*INDEX people_idx1 */}
-
-# Now do an ANALYZE.  A skip-scan can be used after ANALYZE.
-#
-do_execsql_test skipscan2-1.4 {
-  ANALYZE;
-  -- We do not have enough people above to actually force the use
-  -- of a skip-scan.  So make a manual adjustment to the stat1 table
-  -- to make it seem like there are many more.
-  UPDATE sqlite_stat1 SET stat='10000 5000 20' WHERE idx='people_idx1';
-  UPDATE sqlite_stat1 SET stat='10000 1' WHERE idx='sqlite_autoindex_people_1';
-  ANALYZE sqlite_master;
-}
-db cache flush
-do_execsql_test skipscan2-1.5 {
-  SELECT name FROM people WHERE height>=180 ORDER BY +name;
-} {David Jack Patrick Quiana Xavier}
-do_execsql_test skipscan2-1.5eqp {
-  EXPLAIN QUERY PLAN
-  SELECT name FROM people WHERE height>=180 ORDER BY +name;
-} {/*INDEX people_idx1 */}
-
-# Same answer with other formulations of the same query
-#
-do_execsql_test skipscan2-1.6 {
-  SELECT name FROM people
-   WHERE role IN (SELECT DISTINCT role FROM people)
-     AND height>=180 ORDER BY +name;
-} {David Jack Patrick Quiana Xavier}
-do_execsql_test skipscan2-1.7 {
-  SELECT name FROM people WHERE role='teacher' AND height>=180
-  UNION ALL
-  SELECT name FROM people WHERE role='student' AND height>=180
-  ORDER BY 1;
-} {David Jack Patrick Quiana Xavier}
-
-# Add 8 more people, bringing the total to 34.  Then the number of
-# duplicates in the left-column of the index will be 17 and 
-# skip-scan should not be used after an (unfudged) ANALYZE.
-#
-do_execsql_test skipscan2-1.8 {
-  INSERT INTO people VALUES('Angie','student',166);
-  INSERT INTO people VALUES('Brad','student',176);
-  INSERT INTO people VALUES('Claire','student',168);
-  INSERT INTO people VALUES('Donald','student',162);
-  INSERT INTO people VALUES('Elaine','student',177);
-  INSERT INTO people VALUES('Frazier','student',159);
-  INSERT INTO people VALUES('Grace','student',179);
-  INSERT INTO people VALUES('Horace','student',166);
-  ANALYZE;
-  SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';
-} {{34 17 2}}
-db cache flush
-do_execsql_test skipscan2-1.9 {
-  SELECT name FROM people WHERE height>=180 ORDER BY +name;
-} {David Jack Patrick Quiana Xavier}
-do_execsql_test skipscan2-1.9eqp {
-  EXPLAIN QUERY PLAN
-  SELECT name FROM people WHERE height>=180 ORDER BY +name;
-} {~/*INDEX people_idx1 */}
-
-# Add 2 more people, bringing the total to 36.  Then the number of
-# duplicates in the left-column of the index will be 18 and 
-# skip-scan will be used after an (unfudged) ANALYZE.
-#
-do_execsql_test skipscan2-1.10 {
-  INSERT INTO people VALUES('Ingrad','student',155);
-  INSERT INTO people VALUES('Jacob','student',179);
-  ANALYZE;
-  SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';
-} {{36 18 2}}
-db cache flush
-do_execsql_test skipscan2-1.11 {
-  SELECT name FROM people WHERE height>=180 ORDER BY +name;
-} {David Jack Patrick Quiana Xavier}
-do_execsql_test skipscan2-1.11eqp {
-  EXPLAIN QUERY PLAN
-  SELECT name FROM people WHERE height>=180 ORDER BY +name;
-} {/*INDEX people_idx1 */}
-
-
-# Repeat using a WITHOUT ROWID table.
-#
-do_execsql_test skipscan2-2.1 {
-  CREATE TABLE peoplew(
-    name TEXT PRIMARY KEY,
-    role TEXT NOT NULL,
-    height INT NOT NULL, -- in cm
-    CHECK( role IN ('student','teacher') )
-  ) WITHOUT ROWID;
-  CREATE INDEX peoplew_idx1 ON peoplew(role, height);
-  INSERT INTO peoplew(name,role,height)
-     SELECT name, role, height FROM  people;
-  ALTER TABLE people RENAME TO old_people;
-  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
-} {David Jack Patrick Quiana Xavier}
-do_execsql_test skipscan2-2.2 {
-  SELECT name FROM peoplew
-   WHERE role IN (SELECT DISTINCT role FROM peoplew)
-     AND height>=180 ORDER BY +name;
-} {David Jack Patrick Quiana Xavier}
-do_execsql_test skipscan2-2.2 {
-  SELECT name FROM peoplew WHERE role='teacher' AND height>=180
-  UNION ALL
-  SELECT name FROM peoplew WHERE role='student' AND height>=180
-  ORDER BY 1;
-} {David Jack Patrick Quiana Xavier}
-
-# Now do an ANALYZE.  A skip-scan can be used after ANALYZE.
-#
-do_execsql_test skipscan2-2.4 {
-  ANALYZE;
-}
-db cache flush
-do_execsql_test skipscan2-2.5 {
-  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
-} {David Jack Patrick Quiana Xavier}
-do_execsql_test skipscan2-2.5eqp {
-  EXPLAIN QUERY PLAN
-  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
-} {/*INDEX peoplew_idx1 */}
-
-# A skip-scan on a PK index of a WITHOUT ROWID table.
-#
-do_execsql_test skipscan2-3.1 {
-  CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
-}
-do_test skipscan2-3.2 {
-  for {set i 0} {$i < 1000} {incr i} {
-    execsql { INSERT INTO t3 VALUES($i%2, $i, 'xyz') }
-  }
-  execsql { ANALYZE }
-} {}
-do_eqp_test skipscan2-3.3eqp {
-  SELECT * FROM t3 WHERE b=42;
-} {0 0 0 {SEARCH TABLE t3 USING PRIMARY KEY (ANY(a) AND b=?)}}
-
-
-finish_test