# 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.5 2008/07/01 18:26:51 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl 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} 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} } finish_test