# 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