Import SQLite 3.10.2.

third_party/sqlite/src/test/analyze9.test

 # 2013 August 3
 #
 # 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 1116 matching lines @@
   } {
     0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
   }
   do_eqp_test 25.4.2 { 
     SELECT * FROM t6 WHERE a < 20 AND (b BETWEEN ? AND 60)
   } {
     0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
   }
 }
 
+#-------------------------------------------------------------------------
+# Check that a problem in they way stat4 data is used has been 
+# resolved (see below).
+#
+reset_db
+do_test 26.1.1 {
+  db transaction {
+    execsql { 
+      CREATE TABLE t1(x, y, z);
+      CREATE INDEX t1xy ON t1(x, y);
+      CREATE INDEX t1z ON t1(z);
+    }
+    for {set i 0} {$i < 10000} {incr i} {
+      execsql { INSERT INTO t1(x, y) VALUES($i, $i) }
+    }
+    for {set i 0} {$i < 10} {incr i} {
+      execsql {
+        WITH cnt(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cnt WHERE x<100)
+        INSERT INTO t1(x, y) SELECT 10000+$i, x FROM cnt;
+        INSERT INTO t1(x, y) SELECT 10000+$i, 100;
+      }
+    }
+    execsql {
+      UPDATE t1 SET z = rowid / 20;
+      ANALYZE;
+    }
+  }
+} {}
+
+do_execsql_test 26.1.2 {
+  SELECT count(*) FROM t1 WHERE x = 10000 AND y < 50;
+} {49}
+do_execsql_test 26.1.3 {
+  SELECT count(*) FROM t1 WHERE z = 444;
+} {20}
+
+# The analyzer knows that any (z=?) expression matches 20 rows. So it
+# will use index "t1z" if the estimate of hits for (x=10000 AND y<50)
+# is greater than 20 rows.
+#
+# And it should be. The analyzer has a stat4 sample as follows:
+#
+#   sample=(x=10000, y=100) nLt=(10000 10099)
+#
+# There should be no other samples that start with (x=10000). So it knows 
+# that (x=10000 AND y<50) must match somewhere between 0 and 99 rows, but
+# know more than that. Guessing less than 20 is therefore unreasonable.
+#
+# At one point though, due to a problem in whereKeyStats(), the planner was
+# estimating that (x=10000 AND y<50) would match only 2 rows.
+#
+do_eqp_test 26.1.4 {
+  SELECT * FROM t1 WHERE x = 10000 AND y < 50 AND z = 444;
+} {
+  0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z=?)}
+}
+
+
+# This test - 26.2.* - tests that another manifestation of the same problem
+# is no longer present in the library. Assuming:
+# 
+#   CREATE INDEX t1xy ON t1(x, y)
+#
+# and that have samples for index t1xy as follows:
+#
+#
+#   sample=('A', 70)        nEq=(100, 2)        nLt=(900, 970)
+#   sample=('B', 70)        nEq=(100, 2)        nLt=(1000, 1070)    
+#
+# the planner should estimate that (x = 'B' AND y > 25) matches 76 rows
+# (70 * 2/3 + 30). Before, due to the problem, the planner was estimating 
+# that this matched 100 rows.
+# 
+reset_db
+do_execsql_test 26.2.1 {
+  BEGIN;
+    CREATE TABLE t1(x, y, z);
+    CREATE INDEX i1 ON t1(x, y);
+    CREATE INDEX i2 ON t1(z);
+  
+    WITH 
+    cnt(y) AS (SELECT 0 UNION ALL SELECT y+1 FROM cnt WHERE y<99),
+    letters(x) AS (
+      SELECT 'A' UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'
+    )
+    INSERT INTO t1(x, y) SELECT x, y FROM letters, cnt;
+  
+    WITH
+    letters(x) AS (
+      SELECT 'A' UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'
+    )
+    INSERT INTO t1(x, y) SELECT x, 70 FROM letters;
+  
+    WITH
+    cnt(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM cnt WHERE i<9999)
+    INSERT INTO t1(x, y) SELECT i, i FROM cnt;
+  
+    UPDATE t1 SET z = (rowid / 95);
+    ANALYZE;
+  COMMIT;
+}
+
+do_eqp_test 26.2.2 {
+  SELECT * FROM t1 WHERE x='B' AND y>25 AND z=?;
+} {
+  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x=? AND y>?)}
+}
+
+
 finish_test
+
+
+