Import SQLite 3.7.6.3.

third_party/sqlite/src/test/where7.test

 # 2008 December 23
 #
 # 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 72 matching lines @@
   count_steps {
     SELECT a FROM t1 WHERE (b=3 OR c>=10)
   }
 } {2 4 5 scan 0 sort 0}
 do_test where7-1.9 {
   count_steps {
     SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4)
   }
 } {2 4 5 scan 0 sort 0}
 do_test where7-1.10 {
-breakpoint
   count_steps {
     SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4 OR b>10)
   }
 } {2 4 5 scan 0 sort 0}
-breakpoint
 do_test where7-1.11 {
   count_steps {
     SELECT a FROM t1 WHERE (d=5 AND b=3) OR c==100 ORDER BY a;
   }
 } {2 5 scan 0 sort 1}
 do_test where7-1.12 {
   count_steps {
     SELECT a FROM t1 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a
   }
 } {1 2 3 5 scan 0 sort 1}
-do_test where7-1.13.1 {
-  count_steps {
-    SELECT a FROM t1 WHERE (b BETWEEN 0 AND 2) OR (c BETWEEN 9 AND 999)
-    ORDER BY a DESC
-  }
-} {5 4 1 scan 4 sort 0}
-do_test where7-1.13.2 {
+do_test where7-1.13 {
   count_steps {
     SELECT a FROM t1 WHERE (b BETWEEN 0 AND 2) OR (c BETWEEN 9 AND 999)
     ORDER BY +a DESC
   }
 } {5 4 1 scan 0 sort 1}
 
 do_test where7-1.14 {
   count_steps {
     SELECT a FROM t1 WHERE (d=8 OR c=6 OR b=4) AND +a>0
   }
@@ skipping 23174 matching lines @@
   count_steps_sort {
      SELECT a FROM t3
       WHERE b=1092
          OR a=23
          OR (f GLOB '?defg*' AND f GLOB 'cdef*')
          OR d<0.0
          OR (d>=22.0 AND d<23.0 AND d NOT NULL)
          OR a=91
   }
 } {2 22 23 28 54 80 91 scan 0 sort 0}
-finish_test
+
+# test case for the performance regression fixed by
+# check-in 28ba6255282b on 2010-10-21 02:05:06
+#
+# The test case that follows is code from an actual
+# application with identifiers change and unused columns
+# remove.
+#
+do_execsql_test where7-3.1 {
+  CREATE TABLE t301 (
+      c8 INTEGER PRIMARY KEY,
+      c6 INTEGER,
+      c4 INTEGER,
+      c7 INTEGER,
+      FOREIGN KEY (c4) REFERENCES series(c4)
+  );
+  CREATE INDEX t301_c6 on t301(c6);
+  CREATE INDEX t301_c4 on t301(c4);
+  CREATE INDEX t301_c7 on t301(c7);
+  
+  CREATE TABLE t302 (
+      c1 INTEGER PRIMARY KEY,
+      c8 INTEGER,
+      c5 INTEGER,
+      c3 INTEGER,
+      c2 INTEGER,
+      c4 INTEGER,
+      FOREIGN KEY (c8) REFERENCES t301(c8)
+  );
+  CREATE INDEX t302_c3 on t302(c3);
+  CREATE INDEX t302_c8_c3 on t302(c8, c3);
+  CREATE INDEX t302_c5 on t302(c5);
+  
+  EXPLAIN QUERY PLAN
+  SELECT t302.c1 
+    FROM t302 JOIN t301 ON t302.c8 = t301.c8
+    WHERE t302.c2 = 19571
+      AND t302.c3 > 1287603136
+      AND (t301.c4 = 1407449685622784
+           OR t301.c8 = 1407424651264000)
+   ORDER BY t302.c5 LIMIT 200;
+} {
+  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} 
+  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
+  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
+  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
+}
 
 finish_test