/trunk/test/selectB.test |
@@ -0,0 +1,381 @@ |
# 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. |
# |
# $Id: selectB.test,v 1.10 2009/04/02 16:59:47 drh Exp $ |
|
set testdir [file dirname $argv0] |
source $testdir/tester.tcl |
|
ifcapable !compound { |
finish_test |
return |
} |
|
proc test_transform {testname sql1 sql2 results} { |
set ::vdbe1 [list] |
set ::vdbe2 [list] |
db eval "explain $sql1" { lappend ::vdbe1 $opcode } |
db eval "explain $sql2" { lappend ::vdbe2 $opcode } |
|
do_test $testname.transform { |
set ::vdbe1 |
} $::vdbe2 |
|
set ::sql1 $sql1 |
do_test $testname.sql1 { |
execsql $::sql1 |
} $results |
|
set ::sql2 $sql2 |
do_test $testname.sql2 { |
execsql $::sql2 |
} $results |
} |
|
do_test selectB-1.1 { |
execsql { |
CREATE TABLE t1(a, b, c); |
CREATE TABLE t2(d, e, f); |
|
INSERT INTO t1 VALUES( 2, 4, 6); |
INSERT INTO t1 VALUES( 8, 10, 12); |
INSERT INTO t1 VALUES(14, 16, 18); |
|
INSERT INTO t2 VALUES(3, 6, 9); |
INSERT INTO t2 VALUES(12, 15, 18); |
INSERT INTO t2 VALUES(21, 24, 27); |
} |
} {} |
|
for {set ii 1} {$ii <= 2} {incr ii} { |
|
if {$ii == 2} { |
do_test selectB-2.1 { |
execsql { |
CREATE INDEX i1 ON t1(a); |
CREATE INDEX i2 ON t2(d); |
} |
} {} |
} |
|
test_transform selectB-$ii.2 { |
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) |
} { |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 |
} {2 8 14 3 12 21} |
|
test_transform selectB-$ii.3 { |
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 |
} { |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 |
} {2 3 8 12 14 21} |
|
test_transform selectB-$ii.4 { |
SELECT * FROM |
(SELECT a FROM t1 UNION ALL SELECT d FROM t2) |
WHERE a>10 ORDER BY 1 |
} { |
SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1 |
} {12 14 21} |
|
test_transform selectB-$ii.5 { |
SELECT * FROM |
(SELECT a FROM t1 UNION ALL SELECT d FROM t2) |
WHERE a>10 ORDER BY a |
} { |
SELECT a FROM t1 WHERE a>10 |
UNION ALL |
SELECT d FROM t2 WHERE d>10 |
ORDER BY a |
} {12 14 21} |
|
test_transform selectB-$ii.6 { |
SELECT * FROM |
(SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12) |
WHERE a>10 ORDER BY a |
} { |
SELECT a FROM t1 WHERE a>10 |
UNION ALL |
SELECT d FROM t2 WHERE d>12 AND d>10 |
ORDER BY a |
} {14 21} |
|
test_transform selectB-$ii.7 { |
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 |
LIMIT 2 |
} { |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 |
} {2 3} |
|
test_transform selectB-$ii.8 { |
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 |
LIMIT 2 OFFSET 3 |
} { |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3 |
} {12 14} |
|
test_transform selectB-$ii.9 { |
SELECT * FROM ( |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1 |
) |
} { |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1 |
} {2 8 14 3 12 21 6 12 18} |
|
test_transform selectB-$ii.10 { |
SELECT * FROM ( |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1 |
) ORDER BY 1 |
} { |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1 |
ORDER BY 1 |
} {2 3 6 8 12 12 14 18 21} |
|
test_transform selectB-$ii.11 { |
SELECT * FROM ( |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1 |
) WHERE a>=10 ORDER BY 1 LIMIT 3 |
} { |
SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10 |
UNION ALL SELECT c FROM t1 WHERE c>=10 |
ORDER BY 1 LIMIT 3 |
} {12 12 14} |
|
test_transform selectB-$ii.12 { |
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2) |
} { |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2 |
} {2 8} |
|
# An ORDER BY in a compound subqueries defeats flattening. Ticket #3773 |
# test_transform selectB-$ii.13 { |
# SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC) |
# } { |
# SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC |
# } {2 3 8 12 14 21} |
# |
# test_transform selectB-$ii.14 { |
# SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC) |
# } { |
# SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC |
# } {21 14 12 8 3 2} |
# |
# test_transform selectB-$ii.14 { |
# SELECT * FROM ( |
# SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC |
# ) LIMIT 2 OFFSET 2 |
# } { |
# SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC |
# LIMIT 2 OFFSET 2 |
# } {12 8} |
# |
# test_transform selectB-$ii.15 { |
# SELECT * FROM ( |
# SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC |
# ) |
# } { |
# SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC |
# } {2 4 3 6 8 10 12 15 14 16 21 24} |
} |
|
do_test selectB-3.0 { |
execsql { |
DROP INDEX i1; |
DROP INDEX i2; |
} |
} {} |
|
for {set ii 3} {$ii <= 4} {incr ii} { |
|
if {$ii == 4} { |
do_test selectB-4.0 { |
execsql { |
CREATE INDEX i1 ON t1(a); |
CREATE INDEX i2 ON t1(b); |
CREATE INDEX i3 ON t1(c); |
CREATE INDEX i4 ON t2(d); |
CREATE INDEX i5 ON t2(e); |
CREATE INDEX i6 ON t2(f); |
} |
} {} |
} |
|
do_test selectB-$ii.1 { |
execsql { |
SELECT DISTINCT * FROM |
(SELECT c FROM t1 UNION ALL SELECT e FROM t2) |
ORDER BY 1; |
} |
} {6 12 15 18 24} |
|
do_test selectB-$ii.2 { |
execsql { |
SELECT c, count(*) FROM |
(SELECT c FROM t1 UNION ALL SELECT e FROM t2) |
GROUP BY c ORDER BY 1; |
} |
} {6 2 12 1 15 1 18 1 24 1} |
do_test selectB-$ii.3 { |
execsql { |
SELECT c, count(*) FROM |
(SELECT c FROM t1 UNION ALL SELECT e FROM t2) |
GROUP BY c HAVING count(*)>1; |
} |
} {6 2} |
do_test selectB-$ii.4 { |
execsql { |
SELECT t4.c, t3.a FROM |
(SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3 |
WHERE t3.a=14 |
ORDER BY 1 |
} |
} {6 14 6 14 12 14 15 14 18 14 24 14} |
|
do_test selectB-$ii.5 { |
execsql { |
SELECT d FROM t2 |
EXCEPT |
SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) |
} |
} {} |
do_test selectB-$ii.6 { |
execsql { |
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) |
EXCEPT |
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) |
} |
} {} |
do_test selectB-$ii.7 { |
execsql { |
SELECT c FROM t1 |
EXCEPT |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
} |
} {12} |
do_test selectB-$ii.8 { |
execsql { |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
EXCEPT |
SELECT c FROM t1 |
} |
} {9 15 24 27} |
do_test selectB-$ii.9 { |
execsql { |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
EXCEPT |
SELECT c FROM t1 |
ORDER BY c DESC |
} |
} {27 24 15 9} |
|
do_test selectB-$ii.10 { |
execsql { |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
UNION |
SELECT c FROM t1 |
ORDER BY c DESC |
} |
} {27 24 18 15 12 9 6} |
do_test selectB-$ii.11 { |
execsql { |
SELECT c FROM t1 |
UNION |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
ORDER BY c |
} |
} {6 9 12 15 18 24 27} |
do_test selectB-$ii.12 { |
execsql { |
SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2 |
ORDER BY c |
} |
} {6 9 12 15 18 18 24 27} |
do_test selectB-$ii.13 { |
execsql { |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
UNION |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
ORDER BY 1 |
} |
} {6 9 15 18 24 27} |
|
do_test selectB-$ii.14 { |
execsql { |
SELECT c FROM t1 |
INTERSECT |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
ORDER BY 1 |
} |
} {6 18} |
do_test selectB-$ii.15 { |
execsql { |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
INTERSECT |
SELECT c FROM t1 |
ORDER BY 1 |
} |
} {6 18} |
do_test selectB-$ii.16 { |
execsql { |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
INTERSECT |
SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) |
ORDER BY 1 |
} |
} {6 9 15 18 24 27} |
|
do_test selectB-$ii.17 { |
execsql { |
SELECT * FROM ( |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 |
) LIMIT 2 |
} |
} {2 8} |
|
do_test selectB-$ii.18 { |
execsql { |
SELECT * FROM ( |
SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2 |
) LIMIT 2 |
} |
} {14 3} |
|
do_test selectB-$ii.19 { |
execsql { |
SELECT * FROM ( |
SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2 |
) |
} |
} {0 1 0 1} |
|
do_test selectB-$ii.20 { |
execsql { |
SELECT DISTINCT * FROM ( |
SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2 |
) |
} |
} {0 1} |
|
do_test selectB-$ii.21 { |
execsql { |
SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b |
} |
} {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27} |
|
do_test selectB-$ii.21 { |
execsql { |
SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1; |
} |
} {3 12 21 345} |
} |
|
finish_test |