diff options
Diffstat (limited to 'test/selectB.test')
| -rw-r--r-- | test/selectB.test | 426 |
1 files changed, 426 insertions, 0 deletions
diff --git a/test/selectB.test b/test/selectB.test new file mode 100644 index 0000000..05ec9c6 --- /dev/null +++ b/test/selectB.test @@ -0,0 +1,426 @@ +# 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 <= 6} {incr ii} { + + switch $ii { + 4 { + optimization_control db query-flattener off + } + 5 { + optimization_control db query-flattener on + do_test selectB-5.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); + } + } {} + } + 6 { + optimization_control db query-flattener off + } + } + + 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 1 0} + + 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.22 { + execsql { + SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1; + } + } {3 12 21 345} + + do_test selectB-$ii.23 { + execsql { + SELECT x, y FROM ( + SELECT a AS x, b AS y FROM t1 + UNION ALL + SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 JOIN t2 ON (c=d) + UNION ALL + SELECT a*100, b*100 FROM t1 + ) ORDER BY 1; + } + } {2 4 8 10 14 16 80.1 180.1 200 400 800 1000 1400 1600} + + do_test selectB-$ii.24 { + execsql { + SELECT x, y FROM ( + SELECT a AS x, b AS y FROM t1 + UNION ALL + SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d) + UNION ALL + SELECT a*100, b*100 FROM t1 + ) ORDER BY 1; + } + } {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600} + + do_test selectB-$ii.25 { + execsql { + SELECT x+y FROM ( + SELECT a AS x, b AS y FROM t1 + UNION ALL + SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d) + UNION ALL + SELECT a*100, b*100 FROM t1 + ) WHERE y+x NOT NULL ORDER BY 1; + } + } {6 18 30 260.2 600 1800 3000} +} + +finish_test |