Import SQLite 3.8.7.4.

third_party/sqlite/src/test/selectA.test

 # 2008 June 24
 #
 # 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 compound-SELECT merge
 # optimization.  Or, in other words, making sure that all
 # possible combinations of UNION, UNION ALL, EXCEPT, and
 # INTERSECT work together with an ORDER BY clause (with or w/o
 # explicit sort order and explicit collating secquites) and
 # with and without optional LIMIT and OFFSET clauses.
 #
 # $Id: selectA.test,v 1.6 2008/08/21 14:24:29 drh Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
+set testprefix selectA
 
 ifcapable !compound {
   finish_test
   return
 }
 
 do_test selectA-1.0 {
   execsql {
     CREATE TABLE t1(a,b,c COLLATE NOCASE);
     INSERT INTO t1 VALUES(1,'a','a');
@@ skipping 240 matching lines @@
 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
 do_test selectA-2.34 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
     ORDER BY b,a,c
   }
 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
 do_test selectA-2.35 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY b COLLATE NOCASE,a,c
+    ORDER BY y COLLATE NOCASE,x,z
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-2.36 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY b COLLATE NOCASE DESC,a,c
+    ORDER BY y COLLATE NOCASE DESC,x,z
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-2.37 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
     ORDER BY c,b,a
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-2.38 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
     ORDER BY c,a,b
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-2.39 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
     ORDER BY c DESC,a,b
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-2.40 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY c COLLATE BINARY DESC,a,b
+    ORDER BY z COLLATE BINARY DESC,x,y
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
 do_test selectA-2.41 {
   execsql {
     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
     ORDER BY a,b,c
   }
 } {{} C c 1 a a 9.9 b B}
 do_test selectA-2.42 {
   execsql {
@@ skipping 270 matching lines @@
 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
 do_test selectA-2.85 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
     ORDER BY b,a,c
   }
 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
 do_test selectA-2.86 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
-    ORDER BY b COLLATE NOCASE,a,c
+    ORDER BY y COLLATE NOCASE,x,z
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-2.87 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
     ORDER BY y COLLATE NOCASE DESC,x,z
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-2.88 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
     ORDER BY c,b,a
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-2.89 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
     ORDER BY c,a,b
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-2.90 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
     ORDER BY c DESC,a,b
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-2.91 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
-    ORDER BY c COLLATE BINARY DESC,a,b
+    ORDER BY z COLLATE BINARY DESC,x,y
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
 do_test selectA-2.92 {
   execsql {
     SELECT x,y,z FROM t2
     INTERSECT SELECT a,b,c FROM t3
     EXCEPT SELECT c,b,a FROM t1
     UNION SELECT a,b,c FROM t3
     INTERSECT SELECT a,b,c FROM t3
     EXCEPT SELECT c,b,a FROM t1
@@ skipping 240 matching lines @@
 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
 do_test selectA-3.34 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
     ORDER BY b,a,c
   }
 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
 do_test selectA-3.35 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY b COLLATE NOCASE,a,c
+    ORDER BY y COLLATE NOCASE,x,z
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-3.36 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY b COLLATE NOCASE DESC,a,c
+    ORDER BY y COLLATE NOCASE DESC,x,z
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-3.37 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
     ORDER BY c,b,a
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-3.38 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
     ORDER BY c,a,b
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-3.39 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
     ORDER BY c DESC,a,b
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-3.40 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY c COLLATE BINARY DESC,a,b
+    ORDER BY z COLLATE BINARY DESC,x,y
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
 do_test selectA-3.41 {
   execsql {
     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
     ORDER BY a,b,c
   }
 } {{} C c 1 a a 9.9 b B}
 do_test selectA-3.42 {
   execsql {
@@ skipping 270 matching lines @@
 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
 do_test selectA-3.85 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
     ORDER BY b,a,c
   }
 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
 do_test selectA-3.86 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
-    ORDER BY b COLLATE NOCASE,a,c
+    ORDER BY y COLLATE NOCASE,x,z
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-3.87 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
     ORDER BY y COLLATE NOCASE DESC,x,z
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-3.88 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
     ORDER BY c,b,a
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-3.89 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
     ORDER BY c,a,b
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-3.90 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
     ORDER BY c DESC,a,b
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-3.91 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
-    ORDER BY c COLLATE BINARY DESC,a,b
+    ORDER BY z COLLATE BINARY DESC,x,y
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
 do_test selectA-3.92 {
   execsql {
     SELECT x,y,z FROM t2
     INTERSECT SELECT a,b,c FROM t3
     EXCEPT SELECT c,b,a FROM t1
     UNION SELECT a,b,c FROM t3
     INTERSECT SELECT a,b,c FROM t3
     EXCEPT SELECT c,b,a FROM t1
@@ skipping 27 matching lines @@
       SELECT x,y,z FROM t2
       INTERSECT SELECT a,b,c FROM t3
       EXCEPT SELECT c,b,a FROM t1
       UNION SELECT a,b,c FROM t3
       INTERSECT SELECT a,b,c FROM t3
       EXCEPT SELECT c,b,a FROM t1
       UNION SELECT a,b,c FROM t3
       ORDER BY y COLLATE NOCASE DESC,x,z)))
   }
 } {MAD}
+do_execsql_test selectA-3.98 {
+  WITH RECURSIVE
+    xyz(n) AS (
+      SELECT upper((SELECT x FROM (
+        SELECT x,y,z FROM t2
+        INTERSECT SELECT a,b,c FROM t3
+        EXCEPT SELECT c,b,a FROM t1
+        UNION SELECT a,b,c FROM t3
+        INTERSECT SELECT a,b,c FROM t3
+        EXCEPT SELECT c,b,a FROM t1
+        UNION SELECT a,b,c FROM t3
+        ORDER BY y COLLATE NOCASE DESC,x,z)))
+      UNION ALL
+      SELECT n || '+' FROM xyz WHERE length(n)<5
+    )
+  SELECT n FROM xyz ORDER BY +n;
+} {MAD MAD+ MAD++}
+
+#-------------------------------------------------------------------------
+# At one point the following code exposed a temp register reuse problem.
+#
+proc f {args} { return 1 }
+db func f f
+
+do_execsql_test 4.1.1 {
+  CREATE TABLE t4(a, b);
+  CREATE TABLE t5(c, d);
+
+  INSERT INTO t5 VALUES(1, 'x');
+  INSERT INTO t5 VALUES(2, 'x');
+  INSERT INTO t4 VALUES(3, 'x');
+  INSERT INTO t4 VALUES(4, 'x');
+
+  CREATE INDEX i1 ON t4(a);
+  CREATE INDEX i2 ON t5(c);
+}
+
+do_eqp_test 4.1.2 {
+  SELECT c, d FROM t5 
+  UNION ALL
+  SELECT a, b FROM t4 WHERE f()==f()
+  ORDER BY 1,2
+} {
+  1 0 0 {SCAN TABLE t5 USING INDEX i2} 
+  1 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
+  2 0 0 {SCAN TABLE t4 USING INDEX i1} 
+  2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
+  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
+}
+
+do_execsql_test 4.1.3 {
+  SELECT c, d FROM t5 
+  UNION ALL
+  SELECT a, b FROM t4 WHERE f()==f()
+  ORDER BY 1,2
+} {
+  1 x 2 x 3 x 4 x
+}
+
+do_execsql_test 4.2.1 {
+  CREATE TABLE t6(a, b);
+  CREATE TABLE t7(c, d);
+
+  INSERT INTO t7 VALUES(2, 9);
+  INSERT INTO t6 VALUES(3, 0);
+  INSERT INTO t6 VALUES(4, 1);
+  INSERT INTO t7 VALUES(5, 6);
+  INSERT INTO t6 VALUES(6, 0);
+  INSERT INTO t7 VALUES(7, 6);
+
+  CREATE INDEX i6 ON t6(a);
+  CREATE INDEX i7 ON t7(c);
+}
+
+do_execsql_test 4.2.2 {
+  SELECT c, f(d,c,d,c,d) FROM t7
+  UNION ALL
+  SELECT a, b FROM t6 
+  ORDER BY 1,2
+} {/2 . 3 . 4 . 5 . 6 . 7 ./}
+
 
 finish_test