# 2011 September 16 # # 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. The # focus of this script is testing correlated subqueries # # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix subquery2 ifcapable !subquery { finish_test return } do_test subquery2-1.1 { execsql { BEGIN; CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2); INSERT INTO t1 VALUES(3,4); INSERT INTO t1 VALUES(5,6); INSERT INTO t1 VALUES(7,8); CREATE TABLE t2(c,d); INSERT INTO t2 VALUES(1,1); INSERT INTO t2 VALUES(3,9); INSERT INTO t2 VALUES(5,25); INSERT INTO t2 VALUES(7,49); CREATE TABLE t3(e,f); INSERT INTO t3 VALUES(1,1); INSERT INTO t3 VALUES(3,27); INSERT INTO t3 VALUES(5,125); INSERT INTO t3 VALUES(7,343); COMMIT; } execsql { SELECT a FROM t1 WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); } } {1 3 5 7} do_test subquery2-1.2 { execsql { CREATE INDEX t1b ON t1(b); SELECT a FROM t1 WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); } } {1 3 5 7} do_test subquery2-1.11 { execsql { SELECT a FROM t1 WHERE +b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); } } {1} do_test subquery2-1.12 { execsql { SELECT a FROM t1 WHERE b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); } } {1} do_test subquery2-1.21 { execsql { SELECT a FROM t1 WHERE +b=(SELECT x+1 FROM (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f)) } } {1 3 5 7} do_test subquery2-1.22 { execsql { SELECT a FROM t1 WHERE b=(SELECT x+1 FROM (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f)) } } {1 3 5 7} #------------------------------------------------------------------------- # Test that ticket d6b36be38a has been fixed. do_execsql_test 2.1 { CREATE TABLE t4(a, b); CREATE TABLE t5(a, b); INSERT INTO t5 VALUES(3, 5); INSERT INTO t4 VALUES(1, 1); INSERT INTO t4 VALUES(2, 3); INSERT INTO t4 VALUES(3, 6); INSERT INTO t4 VALUES(4, 10); INSERT INTO t4 VALUES(5, 15); } do_execsql_test 2.2 { SELECT * FROM (SELECT * FROM t4 ORDER BY a LIMIT -1 OFFSET 1) LIMIT (SELECT a FROM t5) } {2 3 3 6 4 10} finish_test