From 63847496f14c813a5d80efd5b7de0f1294ffe1e3 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 16:07:11 +0200 Subject: Adding upstream version 3.45.1. Signed-off-by: Daniel Baumann --- test/selectA.test | 1510 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 1510 insertions(+) create mode 100644 test/selectA.test (limited to 'test/selectA.test') diff --git a/test/selectA.test b/test/selectA.test new file mode 100644 index 0000000..7d72bb3 --- /dev/null +++ b/test/selectA.test @@ -0,0 +1,1510 @@ +# 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'); + INSERT INTO t1 VALUES(9.9, 'b', 'B'); + INSERT INTO t1 VALUES(NULL, 'C', 'c'); + INSERT INTO t1 VALUES('hello', 'd', 'D'); + INSERT INTO t1 VALUES(x'616263', 'e', 'e'); + SELECT * FROM t1; + } +} {1 a a 9.9 b B {} C c hello d D abc e e} +do_test selectA-1.1 { + execsql { + CREATE TABLE t2(x,y,z COLLATE NOCASE); + INSERT INTO t2 VALUES(NULL,'U','u'); + INSERT INTO t2 VALUES('mad', 'Z', 'z'); + INSERT INTO t2 VALUES(x'68617265', 'm', 'M'); + INSERT INTO t2 VALUES(5.2e6, 'X', 'x'); + INSERT INTO t2 VALUES(-23, 'Y', 'y'); + SELECT * FROM t2; + } +} {{} U u mad Z z hare m M 5200000.0 X x -23 Y y} +do_test selectA-1.2 { + execsql { + CREATE TABLE t3(a,b,c COLLATE NOCASE); + INSERT INTO t3 SELECT * FROM t1; + INSERT INTO t3 SELECT * FROM t2; + INSERT INTO t3 SELECT * FROM t1; + INSERT INTO t3 SELECT * FROM t2; + INSERT INTO t3 SELECT * FROM t1; + INSERT INTO t3 SELECT * FROM t2; + SELECT count(*) FROM t3; + } +} {30} + +do_test selectA-2.1 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY a,b,c + } +} {{} 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.1.1 { # Ticket #3314 + execsql { + SELECT t1.a, t1.b, t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY a,b,c + } +} {{} 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.1.2 { # Ticket #3314 + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY t1.a, t1.b, t1.c + } +} {{} 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.2 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-2.3 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY a,c,b + } +} {{} 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.4 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + 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.5 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE,a,c + } +} {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.6 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE DESC,a,c + } +} {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.7 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + 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.8 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + 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.9 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + 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.10 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY c COLLATE BINARY DESC,a,b + } +} {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.11 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY a,b,c + } +} {{} 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.12 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-2.13 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY a,c,b + } +} {{} 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.14 { + execsql { + SELECT x,y,z FROM t2 UNION ALL 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.15 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY b COLLATE NOCASE,a,c + } +} {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.16 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY b COLLATE NOCASE DESC,a,c + } +} {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.17 { + execsql { + SELECT x,y,z FROM t2 UNION ALL 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.18 { + execsql { + SELECT x,y,z FROM t2 UNION ALL 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.19 { + execsql { + SELECT x,y,z FROM t2 UNION ALL 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.20 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY c COLLATE BINARY DESC,a,b + } +} {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.21 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY a,b,c + } +} {{} 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.22 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-2.23 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY a,c,b + } +} {{} 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.24 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + 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.25 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE,a,c + } +} {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.26 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE DESC,a,c + } +} {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.27 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + 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.28 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + 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.29 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + 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.30 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY c COLLATE BINARY DESC,a,b + } +} {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.31 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 + ORDER BY a,b,c + } +} {{} 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.32 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-2.33 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 + ORDER BY a,c,b + } +} {{} 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 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 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 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 { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY a,b,c + } +} {hello d D abc e e} +do_test selectA-2.43 { + execsql { + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY a,b,c + } +} {hello d D abc e e} +do_test selectA-2.44 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY a,b,c + } +} {hello d D abc e e} +do_test selectA-2.45 { + execsql { + SELECT a,b,c FROM t1 INTERSECT 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.46 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY a,b,c + } +} {{} C c 1 a a 9.9 b B} +do_test selectA-2.47 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY a DESC + } +} {9.9 b B 1 a a {} C c} +do_test selectA-2.48 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY a DESC + } +} {abc e e hello d D} +do_test selectA-2.49 { + execsql { + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY a DESC + } +} {abc e e hello d D} +do_test selectA-2.50 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY a DESC + } +} {abc e e hello d D} +do_test selectA-2.51 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY a DESC + } +} {9.9 b B 1 a a {} C c} +do_test selectA-2.52 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY a DESC + } +} {9.9 b B 1 a a {} C c} +do_test selectA-2.53 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY b, a DESC + } +} {{} C c 1 a a 9.9 b B} +do_test selectA-2.54 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY b + } +} {hello d D abc e e} +do_test selectA-2.55 { + execsql { + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY b DESC, c + } +} {abc e e hello d D} +do_test selectA-2.56 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY b, c DESC, a + } +} {hello d D abc e e} +do_test selectA-2.57 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY b COLLATE NOCASE + } +} {1 a a 9.9 b B {} C c} +do_test selectA-2.58 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY b + } +} {{} C c 1 a a 9.9 b B} +do_test selectA-2.59 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY c, a DESC + } +} {1 a a 9.9 b B {} C c} +do_test selectA-2.60 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY c + } +} {hello d D abc e e} +do_test selectA-2.61 { + execsql { + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c + } +} {hello d D abc e e} +do_test selectA-2.62 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY c DESC, a + } +} {abc e e hello d D} +do_test selectA-2.63 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY c COLLATE NOCASE + } +} {1 a a 9.9 b B {} C c} +do_test selectA-2.64 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY c + } +} {1 a a 9.9 b B {} C c} +do_test selectA-2.65 { + execsql { + SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY c COLLATE NOCASE + } +} {1 a a 9.9 b B {} C c} +do_test selectA-2.66 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3 + ORDER BY c + } +} {1 a a 9.9 b B {} C c} +do_test selectA-2.67 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d' + ORDER BY c DESC, a + } +} {abc e e hello d D} +do_test selectA-2.68 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' + INTERSECT SELECT a,b,c FROM t3 + EXCEPT SELECT b,c,a FROM t3 + ORDER BY c DESC, a + } +} {abc e e hello d D} +do_test selectA-2.69 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' + INTERSECT SELECT a,b,c FROM t3 + EXCEPT SELECT b,c,a FROM t3 + ORDER BY c COLLATE NOCASE + } +} {1 a a 9.9 b B {} C c} +do_test selectA-2.70 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 + INTERSECT SELECT a,b,c FROM t3 + EXCEPT SELECT b,c,a FROM t3 + ORDER BY c + } +} {1 a a 9.9 b B {} C c} +do_test selectA-2.71 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' + INTERSECT SELECT a,b,c FROM t1 + INTERSECT SELECT a,b,c FROM t3 + EXCEPT SELECT b,c,a FROM t3 + INTERSECT SELECT a,b,c FROM t1 + EXCEPT SELECT x,y,z FROM t2 + INTERSECT SELECT a,b,c FROM t3 + EXCEPT SELECT y,x,z FROM t2 + INTERSECT SELECT a,b,c FROM t1 + EXCEPT SELECT c,b,a FROM t3 + ORDER BY c + } +} {1 a a 9.9 b B {} C c} +do_test selectA-2.72 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY a,b,c + } +} {{} 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.73 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-2.74 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY a,c,b + } +} {{} 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.75 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + 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.76 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE,a,c + } +} {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.77 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE DESC,a,c + } +} {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.78 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + 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.79 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + 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.80 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + 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.81 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY c COLLATE BINARY DESC,a,b + } +} {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.82 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 + ORDER BY a,b,c + } +} {{} 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.83 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-2.84 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 + ORDER BY a,c,b + } +} {{} 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 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 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 + 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.93 { + execsql { + SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1)); + } +} {A} +do_test selectA-2.94 { + execsql { + SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1)); + } +} {a} +do_test selectA-2.95 { + execsql { + SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1)); + } +} {{}} +do_test selectA-2.96 { + execsql { + SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1)); + } +} {m} + + +do_test selectA-3.0 { + execsql { + CREATE UNIQUE INDEX t1a ON t1(a); + CREATE UNIQUE INDEX t1b ON t1(b); + CREATE UNIQUE INDEX t1c ON t1(c); + CREATE UNIQUE INDEX t2x ON t2(x); + CREATE UNIQUE INDEX t2y ON t2(y); + CREATE UNIQUE INDEX t2z ON t2(z); + SELECT name FROM sqlite_master WHERE type='index' + } +} {t1a t1b t1c t2x t2y t2z} +do_test selectA-3.1 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY a,b,c + } +} {{} 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.1.1 { # Ticket #3314 + execsql { + SELECT t1.a,b,t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY a,t1.b,t1.c + } +} {{} 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.2 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-3.3 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY a,c,b + } +} {{} 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.4 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + 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.5 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE,a,c + } +} {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.6 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE DESC,a,c + } +} {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.7 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + 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.8 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + 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.9 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + 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.10 { + execsql { + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 + ORDER BY c COLLATE BINARY DESC,a,b + } +} {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.11 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY a,b,c + } +} {{} 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.12 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-3.13 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY a,c,b + } +} {{} 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.14 { + execsql { + SELECT x,y,z FROM t2 UNION ALL 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.15 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY b COLLATE NOCASE,a,c + } +} {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.16 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY b COLLATE NOCASE DESC,a,c + } +} {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.17 { + execsql { + SELECT x,y,z FROM t2 UNION ALL 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.18 { + execsql { + SELECT x,y,z FROM t2 UNION ALL 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.19 { + execsql { + SELECT x,y,z FROM t2 UNION ALL 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.20 { + execsql { + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 + ORDER BY c COLLATE BINARY DESC,a,b + } +} {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.21 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY a,b,c + } +} {{} 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.22 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-3.23 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY a,c,b + } +} {{} 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.24 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + 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.25 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE,a,c + } +} {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.26 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE DESC,a,c + } +} {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.27 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + 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.28 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + 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.29 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + 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.30 { + execsql { + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 + ORDER BY c COLLATE BINARY DESC,a,b + } +} {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.31 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 + ORDER BY a,b,c + } +} {{} 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.32 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-3.33 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 + ORDER BY a,c,b + } +} {{} 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 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 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 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 { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY a,b,c + } +} {hello d D abc e e} +do_test selectA-3.43 { + execsql { + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY a,b,c + } +} {hello d D abc e e} +do_test selectA-3.44 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY a,b,c + } +} {hello d D abc e e} +do_test selectA-3.45 { + execsql { + SELECT a,b,c FROM t1 INTERSECT 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.46 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY a,b,c + } +} {{} C c 1 a a 9.9 b B} +do_test selectA-3.47 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY a DESC + } +} {9.9 b B 1 a a {} C c} +do_test selectA-3.48 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY a DESC + } +} {abc e e hello d D} +do_test selectA-3.49 { + execsql { + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY a DESC + } +} {abc e e hello d D} +do_test selectA-3.50 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY a DESC + } +} {abc e e hello d D} +do_test selectA-3.51 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY a DESC + } +} {9.9 b B 1 a a {} C c} +do_test selectA-3.52 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY a DESC + } +} {9.9 b B 1 a a {} C c} +do_test selectA-3.53 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY b, a DESC + } +} {{} C c 1 a a 9.9 b B} +do_test selectA-3.54 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY b + } +} {hello d D abc e e} +do_test selectA-3.55 { + execsql { + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY b DESC, c + } +} {abc e e hello d D} +do_test selectA-3.56 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY b, c DESC, a + } +} {hello d D abc e e} +do_test selectA-3.57 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY b COLLATE NOCASE + } +} {1 a a 9.9 b B {} C c} +do_test selectA-3.58 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY b + } +} {{} C c 1 a a 9.9 b B} +do_test selectA-3.59 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY c, a DESC + } +} {1 a a 9.9 b B {} C c} +do_test selectA-3.60 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' + ORDER BY c + } +} {hello d D abc e e} +do_test selectA-3.61 { + execsql { + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c + } +} {hello d D abc e e} +do_test selectA-3.62 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY c DESC, a + } +} {abc e e hello d D} +do_test selectA-3.63 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY c COLLATE NOCASE + } +} {1 a a 9.9 b B {} C c} +do_test selectA-3.64 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 + ORDER BY c + } +} {1 a a 9.9 b B {} C c} +do_test selectA-3.65 { + execsql { + SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' + ORDER BY c COLLATE NOCASE + } +} {1 a a 9.9 b B {} C c} +do_test selectA-3.66 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3 + ORDER BY c + } +} {1 a a 9.9 b B {} C c} +do_test selectA-3.67 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d' + ORDER BY c DESC, a + } +} {abc e e hello d D} +do_test selectA-3.68 { + execsql { + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' + INTERSECT SELECT a,b,c FROM t3 + EXCEPT SELECT b,c,a FROM t3 + ORDER BY c DESC, a + } +} {abc e e hello d D} +do_test selectA-3.69 { + execsql { + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' + INTERSECT SELECT a,b,c FROM t3 + EXCEPT SELECT b,c,a FROM t3 + ORDER BY c COLLATE NOCASE + } +} {1 a a 9.9 b B {} C c} +do_test selectA-3.70 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 + INTERSECT SELECT a,b,c FROM t3 + EXCEPT SELECT b,c,a FROM t3 + ORDER BY c + } +} {1 a a 9.9 b B {} C c} +do_test selectA-3.71 { + execsql { + SELECT a,b,c FROM t1 WHERE b<'d' + INTERSECT SELECT a,b,c FROM t1 + INTERSECT SELECT a,b,c FROM t3 + EXCEPT SELECT b,c,a FROM t3 + INTERSECT SELECT a,b,c FROM t1 + EXCEPT SELECT x,y,z FROM t2 + INTERSECT SELECT a,b,c FROM t3 + EXCEPT SELECT y,x,z FROM t2 + INTERSECT SELECT a,b,c FROM t1 + EXCEPT SELECT c,b,a FROM t3 + ORDER BY c + } +} {1 a a 9.9 b B {} C c} +do_test selectA-3.72 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY a,b,c + } +} {{} 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.73 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-3.74 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY a,c,b + } +} {{} 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.75 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + 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.76 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE,a,c + } +} {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.77 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY b COLLATE NOCASE DESC,a,c + } +} {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.78 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + 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.79 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + 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.80 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + 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.81 { + execsql { + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 + ORDER BY c COLLATE BINARY DESC,a,b + } +} {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.82 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 + ORDER BY a,b,c + } +} {{} 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.83 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 + ORDER BY a DESC,b,c + } +} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} +do_test selectA-3.84 { + execsql { + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 + ORDER BY a,c,b + } +} {{} 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 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 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 + 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.93 { + execsql { + SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1)); + } +} {A} +do_test selectA-3.94 { + execsql { + SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1)); + } +} {a} +do_test selectA-3.95 { + execsql { + SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1)); + } +} {{}} +do_test selectA-3.96 { + execsql { + SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1)); + } +} {m} +do_test selectA-3.97 { + execsql { + 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))) + } +} {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 +} { + QUERY PLAN + `--MERGE (UNION ALL) + |--LEFT + | |--SCAN t5 USING INDEX i2 + | `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY + `--RIGHT + |--SCAN t4 USING INDEX i1 + `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY +} + +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 ./} + + +proc strip_rnd {explain} { + regexp -all {sqlite_sq_[0123456789ABCDEF]*} $explain sqlite_sq +} + +proc do_same_test {tn q1 args} { + set r2 [strip_rnd [db eval "EXPLAIN $q1"]] + set i 1 + foreach q $args { + set tst [subst -nocommands {strip_rnd [db eval "EXPLAIN $q"]}] + uplevel do_test $tn.$i [list $tst] [list $r2] + incr i + } +} + +do_execsql_test 5.0 { + CREATE TABLE t8(a, b); + CREATE TABLE t9(c, d); +} {} + +do_same_test 5.1 { + SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY a; +} { + SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY t8.a; +} { + SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY 1; +} { + SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY c; +} { + SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY t9.c; +} + +do_same_test 5.2 { + SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY a COLLATE NOCASE +} { + SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY t8.a COLLATE NOCASE +} { + SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY 1 COLLATE NOCASE +} { + SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY c COLLATE NOCASE +} { + SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY t9.c COLLATE NOCASE +} + +do_same_test 5.3 { + SELECT a, b FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY b, c COLLATE NOCASE +} { + SELECT a, b FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY 2, 1 COLLATE NOCASE +} { + SELECT a, b FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY d, a COLLATE NOCASE +} { + SELECT a, b FROM t8 EXCEPT SELECT * FROM t9 ORDER BY t9.d, c COLLATE NOCASE +} { + SELECT * FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY d, t8.a COLLATE NOCASE +} + +do_catchsql_test 5.4 { + SELECT * FROM t8 UNION SELECT * FROM t9 ORDER BY a+b COLLATE NOCASE +} {1 {1st ORDER BY term does not match any column in the result set}} + +do_execsql_test 6.1 { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER); + CREATE TABLE t2(b TEXT); + INSERT INTO t2(b) VALUES('12345'); + SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t2) WHERE a=a; +} {12345} + +# 2020-06-15 ticket 8f157e8010b22af0 +# +reset_db +do_execsql_test 7.1 { + CREATE TABLE t1(c1); INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc'); + CREATE TABLE t2(c2); INSERT INTO t2 VALUES(44),(55),(123); + CREATE TABLE t3(c3,c4); INSERT INTO t3 VALUES(66,1),(123,2),(77,3); + CREATE VIEW t4 AS SELECT c3 FROM t3; + CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4; +} +do_execsql_test 7.2 { + SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123; +} {123 123} +do_execsql_test 7.3 { + SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123; +} {123 123} +do_execsql_test 7.4 { + CREATE TABLE a(b); + CREATE VIEW c(d) AS SELECT b FROM a ORDER BY b; + SELECT sum(d) OVER( PARTITION BY(SELECT 0 FROM c JOIN a WHERE b =(SELECT b INTERSECT SELECT d FROM c) AND b = 123)) FROM c; +} {} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 8.0 { + CREATE TABLE x1(x); + CREATE TABLE t1(a, b, c, d); + CREATE INDEX t1a ON t1(a); + CREATE INDEX t1b ON t1(b); +} + +do_execsql_test 8.1 { + SELECT 'ABCD' FROM t1 + WHERE (a=? OR b=?) + AND (0 OR (SELECT 'xyz' INTERSECT SELECT a ORDER BY 1)) +} {} + +#------------------------------------------------------------------------- +# dbsqlfuzz a34f455c91ad75a0cf8cd9476841903f42930a7a +# +reset_db +do_execsql_test 9.0 { + CREATE TABLE t1(a COLLATE nocase); + CREATE TABLE t2(b COLLATE nocase); + + INSERT INTO t1 VALUES('ABC'); + INSERT INTO t2 VALUES('abc'); +} + +do_execsql_test 9.1 { + SELECT a FROM t1 INTERSECT SELECT b FROM t2; +} {ABC} + +do_execsql_test 9.2 { + SELECT * FROM ( + SELECT a FROM t1 INTERSECT SELECT b FROM t2 + ) WHERE a||'' = 'ABC'; +} {ABC} + + + +finish_test -- cgit v1.2.3