diff options
Diffstat (limited to 'components/external/SQLite-3.8.1/test/selectB.test')
| -rw-r--r-- | components/external/SQLite-3.8.1/test/selectB.test | 426 |
1 files changed, 0 insertions, 426 deletions
diff --git a/components/external/SQLite-3.8.1/test/selectB.test b/components/external/SQLite-3.8.1/test/selectB.test deleted file mode 100644 index 05ec9c6bdb..0000000000 --- a/components/external/SQLite-3.8.1/test/selectB.test +++ /dev/null @@ -1,426 +0,0 @@ -# 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 |