Import SQLite 3.8.7.4.

third_party/sqlite/src/test/analyze3.test

 # 2009 August 06
 #
 # 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 regression tests for SQLite library. This file 
 # implements tests for range and LIKE constraints that use bound variables
 # instead of literal constant arguments.
 #
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 
-ifcapable !stat2 {
+ifcapable !stat4&&!stat3 {
   finish_test
   return
 }
 
 #----------------------------------------------------------------------
 # Test Organization:
 #
 # analyze3-1.*: Test that the values of bound parameters are considered 
 #               in the same way as constants when planning queries that
 #               use range constraints.
 #
 # analyze3-2.*: Test that the values of bound parameters are considered 
 #               in the same way as constants when planning queries that
 #               use LIKE expressions in the WHERE clause.
 #
 # analyze3-3.*: Test that binding to a variable does not invalidate the 
 #               query plan when there is no way in which replanning the
 #               query may produce a superior outcome.
 #
 # analyze3-4.*: Test that SQL or authorization callback errors occuring
 #               within sqlite3Reprepare() are handled correctly.
 #
 # 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 }
 db function var getvar
 
 proc eqp {sql {db db}} {
   uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
 }
 
 proc sf_execsql {sql {db db}} {
   set ::sqlite_search_count 0
   set r [uplevel [list execsql $sql $db]]
 
   concat $::sqlite_search_count [$db status step] $r
 }
 
 #-------------------------------------------------------------------------
 #
 # analyze3-1.1.1: 
 #   Create a table with two columns. Populate the first column (affinity 
 #   INTEGER) with integer values from 100 to 1100. Create an index on this 
 #   column. ANALYZE the table.
 #
 # analyze3-1.1.2 - 3.1.3
 #   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
 #   literal values to constrain the range scan.
 #
 #   These tests also check that the compiler code considers column 
 #   affinities when estimating the number of rows scanned by the "use 
 #   index strategy".
 #
 do_test analyze3-1.1.1 {
   execsql {
     BEGIN;
     CREATE TABLE t1(x INTEGER, y);
     CREATE INDEX i1 ON t1(x);
   }
   for {set i 0} {$i < 1000} {incr i} {
     execsql { INSERT INTO t1 VALUES($i+100, $i) }
   }
   execsql {
     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 }
 } {199 0 14850}
 do_test analyze3-1.1.5 {
   set l [string range "200" 0 end]
   set u [string range "300" 0 end]
   sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
 } {199 0 14850}
 do_test analyze3-1.1.6 {
@@ skipping 23 matching lines @@
 do_test analyze3-1.2.1 {
   execsql {
     BEGIN;
       CREATE TABLE t2(x TEXT, y);
       INSERT INTO t2 SELECT * FROM t1;
       CREATE INDEX i2 ON t2(x);
     COMMIT;
     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}
 do_test analyze3-1.2.5 {
   set l [string range "12" 0 end]
   set u [string range "20" 0 end]
   sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
 } {161 0 text text 4760}
 do_test analyze3-1.2.6 {
   set l [expr int(12)]
@@ skipping 21 matching lines @@
 do_test analyze3-1.3.1 {
   execsql {
     BEGIN;
       CREATE TABLE t3(y TEXT, x INTEGER);
       INSERT INTO t3 SELECT y, x FROM t1;
       CREATE INDEX i3 ON t3(x);
     COMMIT;
     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 }
 } {199 0 14850}
 do_test analyze3-1.3.5 {
   set l [string range "200" 0 end]
   set u [string range "300" 0 end]
   sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
 } {199 0 14850}
 do_test analyze3-1.3.6 {
@@ skipping 31 matching lines @@
     set t ""
     append t [lindex {a b c d e f g h i j} [expr $i/100]]
     append t [lindex {a b c d e f g h i j} [expr ($i/10)%10]]
     append t [lindex {a b c d e f g h i j} [expr ($i%10)]]
     execsql { INSERT INTO t1 VALUES($i, $t) }
   }
   execsql COMMIT
 } {}
 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%' }
 } {101 0 100}
 do_test analyze3-2.5 {
   sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' }
 } {999 999 100}
 
 do_test analyze3-2.4 {
   set like "a%"
@@ skipping 38 matching lines @@
     BEGIN;
     CREATE TABLE t1(a, b, c);
     CREATE INDEX i1 ON t1(b);
   }
   for {set i 0} {$i < 100} {incr i} {
     execsql { INSERT INTO t1 VALUES($i, $i, $i) }
   }
   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
 } {0}
 do_test analyze3-3.2.2 {
   sqlite3_bind_text $S 1 "abc" 3
   sqlite3_expired $S
 } {1}
 do_test analyze3-3.2.4 {
   sqlite3_finalize $S
 } {SQLITE_OK}
 
 do_test analyze3-3.2.5 {
   set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b=?" -1 dummy]
   sqlite3_expired $S
 } {0}
 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}
 
 do_test analyze3-3.4.1 {
   set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
   sqlite3_expired $S
 } {0}
 do_test analyze3-3.4.2 {
   sqlite3_bind_text $S 1 "abc" 3
@@ skipping 261 matching lines @@
   while { "SQLITE_ROW" == [sqlite3_step $S1] } {
     lappend R [sqlite3_column_text $S1 0]
   }
   concat [sqlite3_reset $S1] $R
 } {SQLITE_OK aaa abb acc}
 do_test analyze3-5.1.3 {
   sqlite3_finalize $S2
   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