Import SQLite 3.8.7.4.

third_party/sqlite/src/test/analyze3.test

Index: third_party/sqlite/src/test/analyze3.test
diff --git a/third_party/sqlite/src/test/analyze3.test b/third_party/sqlite/src/test/analyze3.test
index 2378ffaaba9a129212d932d94dfa08fb8050856f..e7416d5730337d821be10f6c5480faa6599aa510 100644
--- a/third_party/sqlite/src/test/analyze3.test
+++ b/third_party/sqlite/src/test/analyze3.test
@@ -17,7 +17,7 @@
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 
-ifcapable !stat2 {
+ifcapable !stat4&&!stat3 {
   finish_test
   return
 }
@@ -43,6 +43,8 @@ ifcapable !stat2 {
 # analyze3-5.*: Check that the query plans of applicable statements are
 #               invalidated if the values of SQL parameter are modified
 #               using the clear_bindings() or transfer_bindings() APIs.
+# 
+# analyze3-6.*: Test that the problem fixed by commit [127a5b776d] is fixed.
 #
 
 proc getvar {varname} { uplevel #0 set $varname }
@@ -70,7 +72,7 @@ proc sf_execsql {sql {db db}} {
 #   Show that there are two possible plans for querying the table with
 #   a range constraint on the indexed column - "full table scan" or "use 
 #   the index". When the range is specified using literal values, SQLite
-#   is able to pick the best plan based on the samples in sqlite_stat2.
+#   is able to pick the best plan based on the samples in sqlite_stat3.
 #
 # analyze3-1.1.4 - 3.1.9
 #   Show that using SQL variables produces the same results as using
@@ -93,14 +95,29 @@ do_test analyze3-1.1.1 {
     COMMIT;
     ANALYZE;
   }
-} {}
 
+  ifcapable stat4 {
+    execsql { SELECT count(*)>0 FROM sqlite_stat4; }
+  } else {
+    execsql { SELECT count(*)>0 FROM sqlite_stat3; }
+  }
+} {1}
+
+do_execsql_test analyze3-1.1.x {
+  SELECT count(*) FROM t1 WHERE x>200 AND x<300;
+  SELECT count(*) FROM t1 WHERE x>0 AND x<1100;
+} {99 1000}
+
+# The first of the following two SELECT statements visits 99 rows. So
+# it is better to use the index. But the second visits every row in 
+# the table (1000 in total) so it is better to do a full-table scan.
+#
 do_eqp_test analyze3-1.1.2 {
   SELECT sum(y) FROM t1 WHERE x>200 AND x<300
-} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~100 rows)}}
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
 do_eqp_test analyze3-1.1.3 {
   SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
-} {0 0 0 {SCAN TABLE t1 (~111 rows)}}
+} {0 0 0 {SCAN TABLE t1}}
 
 do_test analyze3-1.1.4 {
   sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
@@ -144,12 +161,17 @@ do_test analyze3-1.2.1 {
     ANALYZE;
   }
 } {}
+do_execsql_test analyze3-2.1.x {
+  SELECT count(*) FROM t2 WHERE x>1 AND x<2;
+  SELECT count(*) FROM t2 WHERE x>0 AND x<99;
+} {200 990}
 do_eqp_test analyze3-1.2.2 {
   SELECT sum(y) FROM t2 WHERE x>1 AND x<2
-} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~200 rows)}}
+} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?)}}
 do_eqp_test analyze3-1.2.3 {
   SELECT sum(y) FROM t2 WHERE x>0 AND x<99
-} {0 0 0 {SCAN TABLE t2 (~111 rows)}}
+} {0 0 0 {SCAN TABLE t2}}
+
 do_test analyze3-1.2.4 {
   sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
 } {161 0 4760}
@@ -191,12 +213,16 @@ do_test analyze3-1.3.1 {
     ANALYZE;
   }
 } {}
+do_execsql_test analyze3-1.3.x {
+  SELECT count(*) FROM t3 WHERE x>200 AND x<300;
+  SELECT count(*) FROM t3 WHERE x>0 AND x<1100
+} {99 1000}
 do_eqp_test analyze3-1.3.2 {
   SELECT sum(y) FROM t3 WHERE x>200 AND x<300
-} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~100 rows)}}
+} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?)}}
 do_eqp_test analyze3-1.3.3 {
   SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
-} {0 0 0 {SCAN TABLE t3 (~111 rows)}}
+} {0 0 0 {SCAN TABLE t3}}
 
 do_test analyze3-1.3.4 {
   sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
@@ -248,10 +274,10 @@ do_test analyze3-2.1 {
 } {}
 do_eqp_test analyze3-2.2 {
   SELECT count(a) FROM t1 WHERE b LIKE 'a%'
-} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?) (~30000 rows)}}
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?)}}
 do_eqp_test analyze3-2.3 {
   SELECT count(a) FROM t1 WHERE b LIKE '%a'
-} {0 0 0 {SCAN TABLE t1 (~500000 rows)}}
+} {0 0 0 {SCAN TABLE t1}}
 
 do_test analyze3-2.4 {
   sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }
@@ -310,7 +336,6 @@ do_test analyze3-3.1 {
   execsql COMMIT
   execsql ANALYZE
 } {}
-
 do_test analyze3-3.2.1 {
   set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b>?" -1 dummy]
   sqlite3_expired $S
@@ -330,7 +355,7 @@ do_test analyze3-3.2.5 {
 do_test analyze3-3.2.6 {
   sqlite3_bind_text $S 1 "abc" 3
   sqlite3_expired $S
-} {0}
+} {1}
 do_test analyze3-3.2.7 {
   sqlite3_finalize $S
 } {SQLITE_OK}
@@ -612,4 +637,29 @@ do_test analyze3-5.1.3 {
   sqlite3_finalize $S1
 } {SQLITE_OK}
 
+#-------------------------------------------------------------------------
+
+do_test analyze3-6.1 {
+  execsql { DROP TABLE IF EXISTS t1 }
+  execsql BEGIN
+  execsql { CREATE TABLE t1(a, b, c) }
+  for {set i 0} {$i < 1000} {incr i} {
+    execsql "INSERT INTO t1 VALUES([expr $i/100], 'x', [expr $i/10])"
+  }
+  execsql {
+    CREATE INDEX i1 ON t1(a, b);
+    CREATE INDEX i2 ON t1(c);
+  }
+  execsql COMMIT
+  execsql ANALYZE
+} {}
+
+do_eqp_test analyze3-6-3 {
+  SELECT * FROM t1 WHERE a = 5 AND c = 13;
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (c=?)}}
+
+do_eqp_test analyze3-6-2 {
+  SELECT * FROM t1 WHERE a = 5 AND b > 'w' AND c = 13;
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (c=?)}}
+
 finish_test