Import SQLite 3.10.2.

third_party/sqlite/src/test/whereJ.test

 # 2014-06-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.
 #
 #***********************************************************************
@@ skipping 622 matching lines @@
      px
   WHERE
      cx.code = '2990'
      AND cx.type=2
      AND px.cx_id = cx.cx_id
      AND px.px_tid = 0
      AND px.le_id = le.le_id;
 } {/.*SCAN TABLE cx.*SEARCH TABLE px.*SEARCH TABLE le.*/}
 
 
+# The following test is derived from a performance problem reported from
+# the field.  Notice the multiple indexes with the same initial tables,
+# and the unusual WHERE clause terms.
+#
+do_test 5.1 {
+  set res [db eval {
+    DROP TABLE IF EXISTS t1;
+    CREATE TABLE t1(a,b,c,d,e,f,g,h);
+    CREATE INDEX t1abc ON t1(a,b,c);
+    CREATE INDEX t1abe ON t1(a,b,e);
+    CREATE INDEX t1abf ON t1(a,b,f);
+    ANALYZE;
+    DROP TABLE IF EXISTS sqlite_stat4;
+    DROP TABLE IF EXISTS sqlite_stat3;
+    DELETE FROM sqlite_stat1;
+    INSERT INTO sqlite_stat1(tbl,idx,stat)
+      VALUES('t1','t1abc','2000000 8000 1600 800'),
+            ('t1','t1abe','2000000 8000 1600 150'),
+            ('t1','t1abf','2000000 8000 1600 150');
+    ANALYZE sqlite_master;
+  
+    EXPLAIN QUERY PLAN
+    SELECT * FROM t1
+     WHERE (a=1 OR a=2)
+       AND (b=3 OR b=4)
+       AND (d>=5 AND d<=5)
+       AND ((e>=7 AND e<=7) OR (f>=8 AND f<=8))
+       AND g>0;
+  }]
+} {~/ANY/}
+do_test 5.2 {set res} {/USING INDEX t1abe/}
+do_test 5.3 {set res} {/USING INDEX t1abf/}
+
+
+
 finish_test