gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/subquery2.test (about) 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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 set ::testprefix subquery2 19 20 ifcapable !subquery { 21 finish_test 22 return 23 } 24 25 do_test subquery2-1.1 { 26 execsql { 27 BEGIN; 28 CREATE TABLE t1(a,b); 29 INSERT INTO t1 VALUES(1,2); 30 INSERT INTO t1 VALUES(3,4); 31 INSERT INTO t1 VALUES(5,6); 32 INSERT INTO t1 VALUES(7,8); 33 CREATE TABLE t2(c,d); 34 INSERT INTO t2 VALUES(1,1); 35 INSERT INTO t2 VALUES(3,9); 36 INSERT INTO t2 VALUES(5,25); 37 INSERT INTO t2 VALUES(7,49); 38 CREATE TABLE t3(e,f); 39 INSERT INTO t3 VALUES(1,1); 40 INSERT INTO t3 VALUES(3,27); 41 INSERT INTO t3 VALUES(5,125); 42 INSERT INTO t3 VALUES(7,343); 43 COMMIT; 44 } 45 execsql { 46 SELECT a FROM t1 47 WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); 48 } 49 } {1 3 5 7} 50 do_test subquery2-1.2 { 51 execsql { 52 CREATE INDEX t1b ON t1(b); 53 SELECT a FROM t1 54 WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); 55 } 56 } {1 3 5 7} 57 58 do_test subquery2-1.11 { 59 execsql { 60 SELECT a FROM t1 61 WHERE +b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); 62 } 63 } {1} 64 do_test subquery2-1.12 { 65 execsql { 66 SELECT a FROM t1 67 WHERE b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3)); 68 } 69 } {1} 70 71 do_test subquery2-1.21 { 72 execsql { 73 SELECT a FROM t1 74 WHERE +b=(SELECT x+1 FROM 75 (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f)) 76 } 77 } {1 3 5 7} 78 do_test subquery2-1.22 { 79 execsql { 80 SELECT a FROM t1 81 WHERE b=(SELECT x+1 FROM 82 (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f)) 83 } 84 } {1 3 5 7} 85 86 #------------------------------------------------------------------------- 87 # Test that ticket d6b36be38a has been fixed. 88 do_execsql_test 2.1 { 89 CREATE TABLE t4(a, b); 90 CREATE TABLE t5(a, b); 91 INSERT INTO t5 VALUES(3, 5); 92 93 INSERT INTO t4 VALUES(1, 1); 94 INSERT INTO t4 VALUES(2, 3); 95 INSERT INTO t4 VALUES(3, 6); 96 INSERT INTO t4 VALUES(4, 10); 97 INSERT INTO t4 VALUES(5, 15); 98 } 99 100 do_execsql_test 2.2 { 101 SELECT * 102 FROM (SELECT * FROM t4 ORDER BY a LIMIT -1 OFFSET 1) 103 LIMIT (SELECT a FROM t5) 104 } {2 3 3 6 4 10} 105 106 ############################################################################ 107 # Ticket http://www.sqlite.org/src/info/d11a6e908f (2014-09-20) 108 # Query planner fault on three-way nested join with compound inner SELECT 109 # 110 do_execsql_test 3.0 { 111 DROP TABLE IF EXISTS t1; 112 DROP TABLE IF EXISTS t2; 113 CREATE TABLE t1 (id INTEGER PRIMARY KEY, data TEXT); 114 INSERT INTO t1(id,data) VALUES(9,'nine-a'); 115 INSERT INTO t1(id,data) VALUES(10,'ten-a'); 116 INSERT INTO t1(id,data) VALUES(11,'eleven-a'); 117 CREATE TABLE t2 (id INTEGER PRIMARY KEY, data TEXT); 118 INSERT INTO t2(id,data) VALUES(9,'nine-b'); 119 INSERT INTO t2(id,data) VALUES(10,'ten-b'); 120 INSERT INTO t2(id,data) VALUES(11,'eleven-b'); 121 122 SELECT id FROM ( 123 SELECT id,data FROM ( 124 SELECT * FROM t1 UNION ALL SELECT * FROM t2 125 ) 126 WHERE id=10 ORDER BY data 127 ); 128 } {10 10} 129 do_execsql_test 3.1 { 130 SELECT data FROM ( 131 SELECT 'dummy', data FROM ( 132 SELECT data FROM t1 UNION ALL SELECT data FROM t1 133 ) ORDER BY data 134 ); 135 } {eleven-a eleven-a nine-a nine-a ten-a ten-a} 136 do_execsql_test 3.2 { 137 DROP TABLE IF EXISTS t3; 138 DROP TABLE IF EXISTS t4; 139 CREATE TABLE t3(id INTEGER, data TEXT); 140 CREATE TABLE t4(id INTEGER, data TEXT); 141 INSERT INTO t3 VALUES(4, 'a'),(2,'c'); 142 INSERT INTO t4 VALUES(3, 'b'),(1,'d'); 143 144 SELECT data, id FROM ( 145 SELECT id, data FROM ( 146 SELECT * FROM t3 UNION ALL SELECT * FROM t4 147 ) ORDER BY data 148 ); 149 } {a 4 b 3 c 2 d 1} 150 151 #------------------------------------------------------------------------- 152 153 do_execsql_test 4.0 { 154 CREATE TABLE t6(x); 155 } 156 157 foreach {tn sql} { 158 1 { 159 SELECT 'abc' FROM ( 160 SELECT x FROM t6 ORDER BY 1 161 UNION ALL 162 SELECT x FROM t6 163 ) 164 } 165 2 { 166 SELECT 'abc' FROM ( 167 SELECT x FROM t6 168 UNION ALL 169 SELECT x FROM t6 ORDER BY 1 170 UNION ALL 171 SELECT x FROM t6 172 ) 173 } 174 3 { 175 SELECT 'abc' FROM ( 176 SELECT x FROM t6 ORDER BY 1 177 UNION ALL 178 SELECT x FROM t6 ORDER BY 1 179 UNION ALL 180 SELECT x FROM t6 181 ) 182 } 183 4 { 184 SELECT 'abc' FROM ( 185 SELECT x FROM t6 186 UNION ALL 187 SELECT x FROM t6 ORDER BY 1 188 UNION ALL 189 SELECT x FROM t6 ORDER BY 1 190 UNION ALL 191 SELECT x FROM t6 192 ) 193 } 194 } { 195 do_catchsql_test 4.$tn $sql [list {*}{ 196 1 {ORDER BY clause should come after UNION ALL not before} 197 }] 198 } 199 200 #------------------------------------------------------------------------- 201 # Test that ticket [9cdc5c46] is fixed. 202 # 203 reset_db 204 do_execsql_test 5.0 { 205 CREATE TABLE t1(x); 206 INSERT INTO t1 VALUES('ALFKI'); 207 INSERT INTO t1 VALUES('ANATR'); 208 209 CREATE TABLE t2(y, z); 210 CREATE INDEX t2y ON t2 (y); 211 INSERT INTO t2 VALUES('ANATR', '1997-08-08 00:00:00'); 212 INSERT INTO t2 VALUES('ALFKI', '1997-08-25 00:00:00'); 213 } 214 do_execsql_test 5.1 { 215 SELECT ( SELECT y FROM t2 WHERE x = y ORDER BY y, z) FROM t1; 216 } {ALFKI ANATR} 217 218 finish_test