1 # 2001 September 15 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 file is testing SELECT statements that are part of 13 # expressions. 14 # 15 # $Id: subselect.test,v 1.16 2008/08/04 03:51:24 danielk1977 Exp $ 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 # Omit this whole file if the library is build without subquery support. 21 ifcapable !subquery { 22 finish_test 23 return 24 } 25 26 # Basic sanity checking. Try a simple subselect. 27 # 28 do_test subselect-1.1 { 29 execsql { 30 CREATE TABLE t1(a int, b int); 31 INSERT INTO t1 VALUES(1,2); 32 INSERT INTO t1 VALUES(3,4); 33 INSERT INTO t1 VALUES(5,6); 34 } 35 execsql {SELECT * FROM t1 WHERE a = (SELECT count(*) FROM t1)} 36 } {3 4} 37 38 # Try a select with more than one result column. 39 # 40 do_test subselect-1.2 { 41 set v [catch {execsql {SELECT * FROM t1 WHERE a = (SELECT * FROM t1)}} msg] 42 lappend v $msg 43 } {1 {only a single result allowed for a SELECT that is part of an expression}} 44 45 # A subselect without an aggregate. 46 # 47 do_test subselect-1.3a { 48 execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=2)} 49 } {2} 50 do_test subselect-1.3b { 51 execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=4)} 52 } {4} 53 do_test subselect-1.3c { 54 execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)} 55 } {6} 56 do_test subselect-1.3d { 57 execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)} 58 } {} 59 ifcapable compound { 60 do_test subselect-1.3e { 61 execsql { 62 SELECT b FROM t1 63 WHERE a = (SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY 1); 64 } 65 } {2} 66 } 67 68 # What if the subselect doesn't return any value. We should get 69 # NULL as the result. Check it out. 70 # 71 do_test subselect-1.4 { 72 execsql {SELECT b from t1 where a = coalesce((SELECT a FROM t1 WHERE b=5),1)} 73 } {2} 74 75 # Try multiple subselects within a single expression. 76 # 77 do_test subselect-1.5 { 78 execsql { 79 CREATE TABLE t2(x int, y int); 80 INSERT INTO t2 VALUES(1,2); 81 INSERT INTO t2 VALUES(2,4); 82 INSERT INTO t2 VALUES(3,8); 83 INSERT INTO t2 VALUES(4,16); 84 } 85 execsql { 86 SELECT y from t2 87 WHERE x = (SELECT sum(b) FROM t1 where a notnull) - (SELECT sum(a) FROM t1) 88 } 89 } {8} 90 91 # Try something useful. Delete every entry from t2 where the 92 # x value is less than half of the maximum. 93 # 94 do_test subselect-1.6 { 95 execsql {DELETE FROM t2 WHERE x < 0.5*(SELECT max(x) FROM t2)} 96 execsql {SELECT x FROM t2 ORDER BY x} 97 } {2 3 4} 98 99 # Make sure sorting works for SELECTs there used as a scalar expression. 100 # 101 do_test subselect-2.1 { 102 execsql { 103 SELECT (SELECT a FROM t1 ORDER BY a), (SELECT a FROM t1 ORDER BY a DESC) 104 } 105 } {1 5} 106 do_test subselect-2.2 { 107 execsql { 108 SELECT 1 IN (SELECT a FROM t1 ORDER BY a); 109 } 110 } {1} 111 do_test subselect-2.3 { 112 execsql { 113 SELECT 2 IN (SELECT a FROM t1 ORDER BY a DESC); 114 } 115 } {0} 116 117 # Verify that the ORDER BY clause is honored in a subquery. 118 # 119 ifcapable compound { 120 do_test subselect-3.1 { 121 execsql { 122 CREATE TABLE t3(x int); 123 INSERT INTO t3 SELECT a FROM t1 UNION ALL SELECT b FROM t1; 124 SELECT * FROM t3 ORDER BY x; 125 } 126 } {1 2 3 4 5 6} 127 } ;# ifcapable compound 128 ifcapable !compound { 129 do_test subselect-3.1 { 130 execsql { 131 CREATE TABLE t3(x int); 132 INSERT INTO t3 SELECT a FROM t1; 133 INSERT INTO t3 SELECT b FROM t1; 134 SELECT * FROM t3 ORDER BY x; 135 } 136 } {1 2 3 4 5 6} 137 } ;# ifcapable !compound 138 139 do_test subselect-3.2 { 140 execsql { 141 SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x LIMIT 2); 142 } 143 } {3} 144 do_test subselect-3.3 { 145 execsql { 146 SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x DESC LIMIT 2); 147 } 148 } {11} 149 do_test subselect-3.4 { 150 execsql { 151 SELECT (SELECT x FROM t3 ORDER BY x); 152 } 153 } {1} 154 do_test subselect-3.5 { 155 execsql { 156 SELECT (SELECT x FROM t3 ORDER BY x DESC); 157 } 158 } {6} 159 do_test subselect-3.6 { 160 execsql { 161 SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1); 162 } 163 } {1} 164 do_test subselect-3.7 { 165 execsql { 166 SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1); 167 } 168 } {6} 169 do_test subselect-3.8 { 170 execsql { 171 SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1 OFFSET 2); 172 } 173 } {3} 174 do_test subselect-3.9 { 175 execsql { 176 SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2); 177 } 178 } {4} 179 do_test subselect-3.10 { 180 execsql { 181 SELECT x FROM t3 WHERE x IN 182 (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2); 183 } 184 } {4} 185 186 # Ticket #2295. 187 # Make sure type affinities work correctly on subqueries with 188 # an ORDER BY clause. 189 # 190 do_test subselect-4.1 { 191 execsql { 192 CREATE TABLE t4(a TEXT, b TEXT); 193 INSERT INTO t4 VALUES('a','1'); 194 INSERT INTO t4 VALUES('b','2'); 195 INSERT INTO t4 VALUES('c','3'); 196 SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b); 197 } 198 } {a b c} 199 do_test subselect-4.2 { 200 execsql { 201 SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b LIMIT 1); 202 } 203 } {a} 204 do_test subselect-4.3 { 205 execsql { 206 SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b DESC LIMIT 1); 207 } 208 } {c} 209 210 finish_test 211
