Import SQLite 3.8.7.4.

third_party/sqlite/src/test/where9.test

Index: third_party/sqlite/src/test/where9.test
diff --git a/third_party/sqlite/src/test/where9.test b/third_party/sqlite/src/test/where9.test
index 9a180116a83947fc9a2ccb44d5c0c9798b1d5509..d073074d439bf447c130b38f9eab29a184b0b50b 100644
--- a/third_party/sqlite/src/test/where9.test
+++ b/third_party/sqlite/src/test/where9.test
@@ -11,12 +11,11 @@
 # This file implements regression tests for SQLite library.  The
 # focus of this file is testing the multi-index OR clause optimizer.
 #
-# $Id: where9.test,v 1.9 2009/06/05 17:09:12 drh Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 
-ifcapable !or_opt {
+ifcapable !or_opt||!compound {
   finish_test
   return
 }
@@ -233,7 +232,7 @@ do_test where9-1.3.3 {
 } {90 91 92 97 scan 98 sort 0}
 do_test where9-1.3.4 {
   count_steps {
-    SELECT a FROM t4
+    SELECT a FROM (t4)
      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
@@ -363,9 +362,9 @@ ifcapable explain {
     SELECT t2.a FROM t1, t2
     WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
   } {
-    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
-    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
-    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
+    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} 
+    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} 
+    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)}
   }
   do_execsql_test where9-3.2 {
     EXPLAIN QUERY PLAN
@@ -373,9 +372,9 @@ ifcapable explain {
     FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
     WHERE t1.a=80
   } {
-    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
-    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
-    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
+    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} 
+    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} 
+    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)}
   }
 } 
 
@@ -421,7 +420,7 @@ do_test where9-4.5 {
        AND (c=31031 OR d IS NULL)
      ORDER BY +a
   }
-} {1 {cannot use index: t1b}}
+} {1 {no query solution}}
 do_test where9-4.6 {
   count_steps {
     SELECT a FROM t1 NOT INDEXED
@@ -437,7 +436,7 @@ do_test where9-4.7 {
        AND (c=31031 OR d IS NULL)
      ORDER BY +a
   }
-} {1 {cannot use index: t1c}}
+} {1 {no query solution}}
 do_test where9-4.8 {
   catchsql {
     SELECT a FROM t1 INDEXED BY t1d
@@ -445,7 +444,7 @@ do_test where9-4.8 {
        AND (c=31031 OR d IS NULL)
      ORDER BY +a
   }
-} {1 {cannot use index: t1d}}
+} {1 {no query solution}}
 
 ifcapable explain {
   # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
@@ -454,8 +453,8 @@ ifcapable explain {
   do_execsql_test where9-5.1 {
     EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
   } {
-    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~10 rows)} 
-    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~10 rows)}
+    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)} 
+    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?)}
   }
 
   # In contrast, b=1000 is preferred over any OR-clause.
@@ -463,7 +462,7 @@ ifcapable explain {
   do_execsql_test where9-5.2 {
     EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
   } {
-    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~5 rows)}
+    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}
   }
 
   # Likewise, inequalities in an AND are preferred over inequalities in
@@ -472,7 +471,7 @@ ifcapable explain {
   do_execsql_test where9-5.3 {
     EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
   } {
-    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~125000 rows)}
+    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?)}
   }
 }
 
@@ -693,7 +692,7 @@ do_test where9-6.5.3 {
 do_test where9-6.5.4 {
   db eval {
     SELECT count(*) FROM t1 UNION ALL
-    SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87);
+    SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87) ORDER BY rowid;
     ROLLBACK;
   }
 } {99 105 131 157 182 183 184 185 186 187}
@@ -769,18 +768,221 @@ do_test where9-6.7.4 {
 do_test where9-6.8.1 {
   catchsql {
     DELETE FROM t1 INDEXED BY t1b
-     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
+     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   }
-} {1 {cannot use index: t1b}}
+} {1 {no query solution}}
 do_test where9-6.8.2 {
   catchsql {
     UPDATE t1 INDEXED BY t1b SET a=a+100
-     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
+     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   }
-} {1 {cannot use index: t1b}}
+} {1 {no query solution}}
+
+set solution_possible 0
+ifcapable stat4||stat3 {
+  if {[permutation] != "no_optimization"} { set solution_possible 1 }
+}
+if $solution_possible {
+  # When STAT3 is enabled, the "b NOT NULL" terms get translated
+  # into b>NULL, which can be satified by the index t1b.  It is a very
+  # expensive way to do the query, but it works, and so a solution is possible.
+  do_test where9-6.8.3-stat4 {
+    catchsql {
+      UPDATE t1 INDEXED BY t1b SET a=a+100
+       WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
+          OR (b NOT NULL AND c IS NULL AND d NOT NULL)
+          OR (b NOT NULL AND c NOT NULL AND d IS NULL)
+    }
+  } {0 {}}
+  do_test where9-6.8.4-stat4 {
+    catchsql {
+      DELETE FROM t1 INDEXED BY t1b
+       WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
+          OR (b NOT NULL AND c IS NULL AND d NOT NULL)
+          OR (b NOT NULL AND c NOT NULL AND d IS NULL)
+    }
+  } {0 {}}
+} else {
+  do_test where9-6.8.3 {
+    catchsql {
+      UPDATE t1 INDEXED BY t1b SET a=a+100
+       WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
+          OR (b NOT NULL AND c IS NULL AND d NOT NULL)
+          OR (b NOT NULL AND c NOT NULL AND d IS NULL)
+    }
+  } {1 {no query solution}}
+  do_test where9-6.8.4 {
+    catchsql {
+      DELETE FROM t1 INDEXED BY t1b
+       WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
+          OR (b NOT NULL AND c IS NULL AND d NOT NULL)
+          OR (b NOT NULL AND c NOT NULL AND d IS NULL)
+    }
+  } {1 {no query solution}}
+}
+############################################################################
+# Test cases where terms inside an OR series are combined with AND terms
+# external to the OR clause.  In other words, cases where
+#
+#              x AND (y OR z)
+#
+# is able to use indices on x,y and x,z, or indices y,x and z,x.
+#
+do_test where9-7.0 {
+  execsql {
+    CREATE TABLE t5(a, b, c, d, e, f, g, x, y);
+    INSERT INTO t5
+     SELECT a, b, c, e, d, f, g,
+            CASE WHEN (a&1)!=0 THEN 'y' ELSE 'n' END,
+            CASE WHEN (a&2)!=0 THEN 'y' ELSE 'n' END
+       FROM t1;
+    CREATE INDEX t5xb ON t5(x, b);
+    CREATE INDEX t5xc ON t5(x, c);
+    CREATE INDEX t5xd ON t5(x, d);
+    CREATE INDEX t5xe ON t5(x, e);
+    CREATE INDEX t5xf ON t5(x, f);
+    CREATE INDEX t5xg ON t5(x, g);
+    CREATE INDEX t5yb ON t5(y, b);
+    CREATE INDEX t5yc ON t5(y, c);
+    CREATE INDEX t5yd ON t5(y, d);
+    CREATE INDEX t5ye ON t5(y, e);
+    CREATE INDEX t5yf ON t5(y, f);
+    CREATE INDEX t5yg ON t5(y, g);
+    CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
+    INSERT INTO t6 SELECT * FROM t5;
+    ANALYZE t5;
+  }
+  ifcapable stat3 {
+    sqlite3 db2 test.db
+    db2 eval { DROP TABLE IF EXISTS sqlite_stat3 }
+    db2 close
+  }
+} {}
+do_test where9-7.1.1 {
+  count_steps {
+    SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
+  }
+} {79 81 83 scan 0 sort 1}
+do_test where9-7.1.2 {
+  execsql {
+    SELECT a FROM t6 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
+  }
+} {79 81 83}
+do_test where9-7.1.3 {
+  count_steps {
+    SELECT a FROM t5 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
+  }
+} {80 scan 0 sort 1}
+do_test where9-7.1.4 {
+  execsql {
+    SELECT a FROM t6 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
+  }
+} {80}
+do_test where9-7.2.1 {
+  count_steps {
+    SELECT a FROM t5 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
+  }
+} {83 scan 0 sort 1}
+do_test where9-7.2.2 {
+  execsql {
+    SELECT a FROM t6 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
+  }
+} {83}
+do_test where9-7.3.1 {
+  count_steps {
+    SELECT a FROM t5 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
+  }
+} {79 81 scan 0 sort 1}
+do_test where9-7.3.2 {
+  execsql {
+    SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
+  }
+} {79 81}
+
+# Fix for ticket [b7c8682cc17f32903f03a610bd0d35ffd3c1e6e4]
+# "Incorrect result from LEFT JOIN with OR in the WHERE clause"
+#
+do_test where9-8.1 {
+  db eval {
+    CREATE TABLE t81(a INTEGER PRIMARY KEY, b, c, d);
+    CREATE TABLE t82(x INTEGER PRIMARY KEY, y);
+    CREATE TABLE t83(p INTEGER PRIMARY KEY, q);
+    
+    INSERT INTO t81 VALUES(2,3,4,5);
+    INSERT INTO t81 VALUES(3,4,5,6);
+    INSERT INTO t82 VALUES(2,4);
+    INSERT INTO t83 VALUES(5,55);
+    
+    SELECT *
+      FROM t81 LEFT JOIN t82 ON y=b JOIN t83
+     WHERE c==p OR d==p
+     ORDER BY +a;
+  }
+} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
+do_test where9-8.2 {
+  db eval {
+    SELECT *
+      FROM t81 LEFT JOIN (t82) ON y=b JOIN t83
+     WHERE c==p OR d==p
+     ORDER BY +a;
+  }
+} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
+do_test where9-8.3 {
+  db eval {
+    SELECT *
+      FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83
+     WHERE c==p OR d==p
+     ORDER BY +a;
+  }
+} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
+
+# Fix for ticket [f2369304e47167e3e644e2f1fe9736063391d7b7]
+# Incorrect results when OR is used in the ON clause of a LEFT JOIN 
+#
+do_test where9-9.1 {
+  db eval {
+    CREATE TABLE t91(x); INSERT INTO t91 VALUES(1);
+    CREATE TABLE t92(y INTEGER PRIMARY KEY,a,b);
+    INSERT INTO t92 VALUES(1,2,3);
+    SELECT 1 FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
+    SELECT 2 FROM t91 LEFT JOIN t92 ON a=2 AND b=3;
+    SELECT 3 FROM t91 LEFT JOIN t92 ON (a=2 OR b=3) AND y IS NULL;
+    SELECT 4 FROM t91 LEFT JOIN t92 ON (a=2 AND b=3) AND y IS NULL;
+    CREATE TEMP TABLE x9 AS SELECT * FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
+    SELECT 5 FROM x9 WHERE y IS NULL;
+    SELECT 6 FROM t91 LEFT JOIN t92 ON a=2 OR b=3 WHERE y IS NULL;
+    SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL;
+    SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL;
+    SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL;
+  }
+} {1 2 3 4 8 9}
+
+# Fix for ticket [bc878246eafe0f52c519e29049b2fe4a99491b27]
+# Incorrect result when OR is used in a join to the right of a LEFT JOIN
+#
+do_test where9-10.1 {
+  db eval {
+    CREATE TABLE t101 (id INTEGER PRIMARY KEY);
+    INSERT INTO t101 VALUES (1);
+    SELECT * FROM t101 AS t0
+         LEFT JOIN t101 AS t1 ON t1.id BETWEEN 10 AND 20
+         JOIN t101 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
+  }
+} {1 {} 1}
+do_test where9-10.2 {
+  db eval {
+    CREATE TABLE t102 (id TEXT UNIQUE NOT NULL);
+    INSERT INTO t102 VALUES ('1');
+    SELECT * FROM t102 AS t0
+         LEFT JOIN t102 AS t1 ON t1.id GLOB 'abc%'
+         JOIN t102 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
+  }
+} {1 {} 1}
+
+
 
 finish_test