Import SQLite 3.8.7.4.

third_party/sqlite/src/test/where9.test

 # 2008 December 30
 #
 # 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.  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
 }
 
 # Evaluate SQL.  Return the result set followed by the
 # and the number of full-scan steps.
 #
 proc count_steps {sql} {
   set r [db eval $sql]
   lappend r scan [db status step] sort [db status sort]
@@ skipping 196 matching lines @@
   count_steps {
     SELECT a FROM t4
      WHERE (b NOT NULL AND c NOT NULL AND d IS NULL)
         OR (b IS NULL AND c NOT NULL AND d NOT NULL)
         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
     ORDER BY a
   }
 } {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)
     ORDER BY a
   }
 } {90 91 92 97 scan 98 sort 0}
 
 do_test where9-1.4 {
   count_steps {
     SELECT a FROM t1
@@ skipping 109 matching lines @@
   }
 } {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1}
 
 
 ifcapable explain {
   do_execsql_test where9-3.1 {
     EXPLAIN QUERY PLAN
     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
     SELECT coalesce(t2.a,9999)
     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=?)}
   }
 } 
 
 # Make sure that INDEXED BY and multi-index OR clauses play well with
 # one another.
 #
 do_test where9-4.1 {
   count_steps {
     SELECT a FROM t1
      WHERE b>1000
@@ skipping 25 matching lines @@
      ORDER BY +a
   }
 } {92 93 97 scan 0 sort 1}
 do_test where9-4.5 {
   catchsql {
     SELECT a FROM t1 INDEXED BY t1b
      WHERE +b>1000
        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
      WHERE b>1000
        AND (c=31031 OR d IS NULL)
      ORDER BY +a
   }
 } {92 93 97 scan 98 sort 1}
 do_test where9-4.7 {
   catchsql {
     SELECT a FROM t1 INDEXED BY t1c
      WHERE b>1000
        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
      WHERE b>1000
        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
   # the former is an equality test which is expected to return fewer rows.
   #
   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.
   #
   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
   # an OR.
   #
   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>?)}
   }
 }
 
 ############################################################################
 # Make sure OR-clauses work correctly on UPDATE and DELETE statements.
 
 do_test where9-6.2.1 {
   db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85}
 } {99 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99}
 
@@ skipping 200 matching lines @@
         OR c=28028
         OR (d>=82 AND d<83)
         OR (e>2802 AND e<2803) 
         OR f='fghijklmn'
         OR g='hgfedcb'
   }
 } {scan 0 sort 0}   ;#  Add 100 to rowids 5 31 57 82 83 84 85 86 87
 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}
 
 do_test where9-6.6.1 {
   count_steps {
     BEGIN;
     DELETE FROM t1
      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
         OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
@@ skipping 55 matching lines @@
   db eval {
     SELECT count(*) FROM t1 UNION ALL
     SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
     ROLLBACK;
   }
 } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
 
 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