Import SQLite 3.8.7.4.

third_party/sqlite/src/test/where8.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.
 #
 #***********************************************************************
 # This file implements regression tests for SQLite library. The focus
 # is testing of where.c. More specifically, the focus is the optimization
 # of WHERE clauses that feature the OR operator.
 #
-# $Id: where8.test,v 1.9 2009/07/31 06:14:52 danielk1977 Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 
 # Test organization:
 #
 #   where8-1.*: Tests to demonstrate simple cases work with a single table
 #               in the FROM clause.
 #
 #   where8-2.*: Tests surrounding virtual tables and the OR optimization.
@@ skipping 55 matching lines @@
   execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a }
 } {I III 0 1}
 
 do_test where8-1.8 {
   # 18 searches. 9 on the index cursor and 9 on the table cursor.
   execsql_status2 { SELECT c FROM t1 WHERE a > 1 AND c LIKE 'I%' }
 } {II III IV IX 0 0 18}
 
 do_test where8-1.9 {
   execsql_status2 { SELECT c FROM t1 WHERE a >= 9 OR b <= 'eight' }
-} {IX X VIII 0 0 6}
+} {IX X VIII 0 0 7}
 
 do_test where8-1.10 {
   execsql_status2 { 
     SELECT c FROM t1 WHERE (a >= 9 AND c != 'X') OR b <= 'eight' 
   }
-} {IX VIII 0 0 6}
+} {IX VIII 0 0 7}
 
 do_test where8-1.11 {
   execsql_status2 { 
     SELECT c FROM t1 WHERE (a >= 4 AND a <= 6) OR b = 'nine' 
   }
 } {IV V VI IX 0 0 10}
 
 do_test where8-1.12.1 {
   execsql_status2 { 
     SELECT c FROM t1 WHERE a IN(1, 2, 3) OR a = 5
@@ skipping 96 matching lines @@
 } {2 6 3 6 0 0}
 
 do_test where8-3.4 {
   execsql_status {
     SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = a
   }
 } {2 2 3 3 0 0}
 
 do_test where8-3.5 {
   execsql_status {
-    SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen')
+    SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = +a OR e = 'sixteen')
+     ORDER BY +a, +d;
   }
-} {2 2 2 4 3 3 3 4 0 0}
+} {2 2 2 4 3 3 3 4 0 1}
 
 do_test where8-3.6 {
   # The first part of the WHERE clause in this query, (a=2 OR a=3) is
   # transformed into "a IN (2, 3)". This is why the sort is required.
   #
   execsql_status {
     SELECT a, d 
     FROM t1, t2 
-    WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen')
+    WHERE (a = 2 OR a = 3) AND (d = +a OR e = 'sixteen')
     ORDER BY t1.rowid
   }
 } {2 2 2 4 3 3 3 4 0 1}
 do_test where8-3.7 {
   execsql_status {
     SELECT a, d 
     FROM t1, t2 
     WHERE a = 2 AND (d = a OR e = 'sixteen')
     ORDER BY t1.rowid
   }
-} {2 2 2 4 0 0}
+} {/2 2 2 4 0 [01]/}
 do_test where8-3.8 {
   execsql_status {
     SELECT a, d 
     FROM t1, t2 
     WHERE (a = 2 OR b = 'three') AND (d = a OR e = 'sixteen')
     ORDER BY t1.rowid
   }
 } {2 2 2 4 3 3 3 4 0 1}
 
 do_test where8-3.9 {
@@ skipping 14 matching lines @@
 
 do_test where8-3.11 {
   execsql_status {
     SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a
   }
 } {1 1 2 2 3 3 4 2 4 4 0 0}
 do_test where8-3.12 {
   execsql_status {
     SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a
   }
-} {1 1 2 2 3 3 4 2 4 4 0 0}
+} {1 1 2 2 3 3 4 2 4 4 9 0}
 do_test where8-3.13 {
   execsql_status {
     SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5
   }
 } {1 1 2 2 3 3 4 2 4 4 9 0}
 
 do_test where8-3.14 {
   execsql_status {
     SELECT c FROM t1 WHERE a > (SELECT d FROM t2 WHERE e = b) OR a = 5
   }
 } {IV V 9 0}
 
 do_test where8-3.15 {
   execsql_status {
     SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = (
       SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d
     )
     ORDER BY c
   }
 } {I I I I I I I I I I II II II II II II II II II II III III III III III 9 1}
 
+
+do_test where8-3.21 {
+  execsql_status {
+    SELECT a, d FROM t1, (t2) WHERE (a=d OR b=e) AND a<5 ORDER BY a
+  }
+} {1 1 2 2 3 3 4 2 4 4 0 0}
+do_test where8-3.21.1 {
+  execsql_status {
+    SELECT a, d FROM t1, ((t2)) AS t3 WHERE (a=d OR b=e) AND a<5 ORDER BY a
+  }
+} {1 1 2 2 3 3 4 2 4 4 0 0}
+if {[permutation] != "no_optimization"} {
+do_test where8-3.21.2 {
+  execsql_status {
+    SELECT a, d FROM t1, ((SELECT * FROM t2)) AS t3 WHERE (a=d OR b=e) AND a<5 ORDER BY a
+  }
+} {1 1 2 2 3 3 4 2 4 4 0 0}
+}
+do_test where8-3.22 {
+  execsql_status {
+    SELECT a, d FROM ((((((t1))), (((t2))))))
+     WHERE (a=d OR b=e) AND a<5 ORDER BY a
+  }
+} {1 1 2 2 3 3 4 2 4 4 0 0}
+if {[permutation] != "no_optimization"} {
+do_test where8-3.23 {
+  execsql_status {
+    SELECT * FROM ((SELECT * FROM t2)) AS t3;
+  }
+} {1 {} I 2 four IV 3 {} IX 4 sixteen XVI 5 {} XXV 6 thirtysix XXXVI 7 fortynine XLIX 8 sixtyeight LXIV 9 eightyone LXXXIX 10 {} C 9 0}
+}
+
 #-----------------------------------------------------------------------
 # The following tests - where8-4.* - verify that adding or removing 
 # indexes does not change the results returned by various queries.
 #
 do_test where8-4.1 {
   execsql {
     BEGIN;
     CREATE TABLE t3(a INTEGER, b REAL, c TEXT);
     CREATE TABLE t4(f INTEGER, g REAL, h TEXT);
     INSERT INTO t3 VALUES('hills', NULL, 1415926535);
@@ skipping 406 matching lines @@
   }
   execsql {
     SELECT a, x FROM tA LEFT JOIN tB ON (
       a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND
       i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND
       (p = 1 OR p = 2 OR p = 3)
     )
   }
 } {1 {}}
 
+# The OR optimization and WITHOUT ROWID
+#
+do_execsql_test where8-6.1 {
+  CREATE TABLE t600(a PRIMARY KEY, b) WITHOUT rowid;
+  CREATE INDEX t600b ON t600(b);
+  INSERT INTO t600 VALUES('state','screen'),('exact','dolphin'),('green','mercury');
+  SELECT a, b, '|' FROM t600 WHERE a=='state' OR b='mercury' ORDER BY +a;
+} {green mercury | state screen |}
+
 finish_test