1# 2011 September 16 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#************************************************************************* 11# This file implements regression tests for SQLite library. The 12# focus of this script is testing correlated subqueries 13# 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19ifcapable !subquery { 20 finish_test 21 return 22} 23 24do_test subquery2-1.1 { 25 execsql { 26 BEGIN; 27 CREATE TABLE t1(a,b); 28 INSERT INTO t1 VALUES(1,2); 29 INSERT INTO t1 VALUES(3,4); 30 INSERT INTO t1 VALUES(5,6); 31 INSERT INTO t1 VALUES(7,8); 32 CREATE TABLE t2(c,d); 33 INSERT INTO t2 VALUES(1,1); 34 INSERT INTO t2 VALUES(3,9); 35 INSERT INTO t2 VALUES(5,25); 36 INSERT INTO t2 VALUES(7,49); 37 CREATE TABLE t3(e,f); 38 INSERT INTO t3 VALUES(1,1); 39 INSERT INTO t3 VALUES(3,27); 40 INSERT INTO t3 VALUES(5,125); 41 INSERT INTO t3 VALUES(7,343); 42 COMMIT; 43 } 44 execsql { 45 SELECT a FROM t1 46 WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); 47 } 48} {1 3 5 7} 49do_test subquery2-1.2 { 50 execsql { 51 CREATE INDEX t1b ON t1(b); 52 SELECT a FROM t1 53 WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); 54 } 55} {1 3 5 7} 56 57do_test subquery2-1.11 { 58 execsql { 59 SELECT a FROM t1 60 WHERE +b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); 61 } 62} {1} 63do_test subquery2-1.12 { 64 execsql { 65 SELECT a FROM t1 66 WHERE b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); 67 } 68} {1} 69 70do_test subquery2-1.21 { 71 execsql { 72 SELECT a FROM t1 73 WHERE +b=(SELECT x+1 FROM 74 (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f)) 75 } 76} {1 3 5 7} 77do_test subquery2-1.22 { 78 execsql { 79 SELECT a FROM t1 80 WHERE b=(SELECT x+1 FROM 81 (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f)) 82 } 83} {1 3 5 7} 84 85 86finish_test 87