1# 2007 November 29 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 tests the optimisations made in November 2007 of expressions 12# of the following form: 13# 14# <value> IN (SELECT <column> FROM <table>) 15# 16# $Id: in3.test,v 1.5 2008/08/04 03:51:24 danielk1977 Exp $ 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21ifcapable !subquery { 22 finish_test 23 return 24} 25 26# Return the number of OpenEphemeral instructions used in the 27# implementation of the sql statement passed as a an argument. 28# 29proc nEphemeral {sql} { 30 set nEph 0 31 foreach op [execsql "EXPLAIN $sql"] { 32 if {$op eq "OpenEphemeral"} {incr nEph} 33 } 34 set nEph 35} 36 37# This proc works the same way as execsql, except that the number 38# of OpenEphemeral instructions used in the implementation of the 39# statement is inserted into the start of the returned list. 40# 41proc exec_neph {sql} { 42 return [concat [nEphemeral $sql] [execsql $sql]] 43} 44 45do_test in3-1.1 { 46 execsql { 47 CREATE TABLE t1(a PRIMARY KEY, b); 48 INSERT INTO t1 VALUES(1, 2); 49 INSERT INTO t1 VALUES(3, 4); 50 INSERT INTO t1 VALUES(5, 6); 51 } 52} {} 53 54# All of these queries should avoid using a temp-table: 55# 56do_test in3-1.2 { 57 exec_neph { SELECT rowid FROM t1 WHERE rowid IN (SELECT rowid FROM t1); } 58} {0 1 2 3} 59do_test in3-1.3 { 60 exec_neph { SELECT a FROM t1 WHERE a IN (SELECT a FROM t1); } 61} {0 1 3 5} 62do_test in3-1.4 { 63 exec_neph { SELECT rowid FROM t1 WHERE rowid+0 IN (SELECT rowid FROM t1); } 64} {0 1 2 3} 65do_test in3-1.5 { 66 exec_neph { SELECT a FROM t1 WHERE a+0 IN (SELECT a FROM t1); } 67} {0 1 3 5} 68 69# Because none of the sub-select queries in the following statements 70# match the pattern ("SELECT <column> FROM <table>"), the following do 71# require a temp table. 72# 73do_test in3-1.6 { 74 exec_neph { SELECT rowid FROM t1 WHERE rowid IN (SELECT rowid+0 FROM t1); } 75} {1 1 2 3} 76do_test in3-1.7 { 77 exec_neph { SELECT a FROM t1 WHERE a IN (SELECT a+0 FROM t1); } 78} {1 1 3 5} 79do_test in3-1.8 { 80 exec_neph { SELECT a FROM t1 WHERE a IN (SELECT a FROM t1 WHERE 1); } 81} {1 1 3 5} 82do_test in3-1.9 { 83 exec_neph { SELECT a FROM t1 WHERE a IN (SELECT a FROM t1 GROUP BY a); } 84} {1 1 3 5} 85 86# This should not use a temp-table. Even though the sub-select does 87# not exactly match the pattern "SELECT <column> FROM <table>", in 88# this case the ORDER BY is a no-op and can be ignored. 89do_test in3-1.10 { 90 exec_neph { SELECT a FROM t1 WHERE a IN (SELECT a FROM t1 ORDER BY a); } 91} {0 1 3 5} 92 93# These do use the temp-table. Adding the LIMIT clause means the 94# ORDER BY cannot be ignored. 95do_test in3-1.11 { 96 exec_neph {SELECT a FROM t1 WHERE a IN (SELECT a FROM t1 ORDER BY a LIMIT 1)} 97} {1 1} 98do_test in3-1.12 { 99 exec_neph { 100 SELECT a FROM t1 WHERE a IN (SELECT a FROM t1 ORDER BY a LIMIT 1 OFFSET 1) 101 } 102} {1 3} 103 104# Has to use a temp-table because of the compound sub-select. 105# 106ifcapable compound { 107 do_test in3-1.13 { 108 exec_neph { 109 SELECT a FROM t1 WHERE a IN ( 110 SELECT a FROM t1 UNION ALL SELECT a FROM t1 111 ) 112 } 113 } {1 1 3 5} 114} 115 116# The first of these queries has to use the temp-table, because the 117# collation sequence used for the index on "t1.a" does not match the 118# collation sequence used by the "IN" comparison. The second does not 119# require a temp-table, because the collation sequences match. 120# 121do_test in3-1.14 { 122 exec_neph { SELECT a FROM t1 WHERE a COLLATE nocase IN (SELECT a FROM t1) } 123} {1 1 3 5} 124do_test in3-1.15 { 125 exec_neph { SELECT a FROM t1 WHERE a COLLATE binary IN (SELECT a FROM t1) } 126} {0 1 3 5} 127 128# Neither of these queries require a temp-table. The collation sequence 129# makes no difference when using a rowid. 130# 131do_test in3-1.16 { 132 exec_neph {SELECT a FROM t1 WHERE a COLLATE nocase IN (SELECT rowid FROM t1)} 133} {0 1 3} 134do_test in3-1.17 { 135 exec_neph {SELECT a FROM t1 WHERE a COLLATE binary IN (SELECT rowid FROM t1)} 136} {0 1 3} 137 138# The following tests - in3.2.* - test a bug that was difficult to track 139# down during development. They are not particularly well focused. 140# 141do_test in3-2.1 { 142 execsql { 143 DROP TABLE IF EXISTS t1; 144 CREATE TABLE t1(w int, x int, y int); 145 CREATE TABLE t2(p int, q int, r int, s int); 146 } 147 for {set i 1} {$i<=100} {incr i} { 148 set w $i 149 set x [expr {int(log($i)/log(2))}] 150 set y [expr {$i*$i + 2*$i + 1}] 151 execsql "INSERT INTO t1 VALUES($w,$x,$y)" 152 } 153 set maxy [execsql {select max(y) from t1}] 154 db eval { INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1 } 155} {} 156do_test in3-2.2 { 157 execsql { 158 SELECT rowid 159 FROM t1 160 WHERE rowid IN (SELECT rowid FROM t1 WHERE rowid IN (1, 2)); 161 } 162} {1 2} 163do_test in3-2.3 { 164 execsql { 165 select rowid from t1 where rowid IN (-1,2,4) 166 } 167} {2 4} 168do_test in3-2.4 { 169 execsql { 170 SELECT rowid FROM t1 WHERE rowid IN 171 (select rowid from t1 where rowid IN (-1,2,4)) 172 } 173} {2 4} 174 175#------------------------------------------------------------------------- 176# This next block of tests - in3-3.* - verify that column affinity is 177# correctly handled in cases where an index might be used to optimise 178# an IN (SELECT) expression. 179# 180do_test in3-3.1 { 181 catch {execsql { 182 DROP TABLE t1; 183 DROP TABLE t2; 184 }} 185 186 execsql { 187 188 CREATE TABLE t1(a BLOB, b NUMBER ,c TEXT); 189 CREATE UNIQUE INDEX t1_i1 ON t1(a); /* no affinity */ 190 CREATE UNIQUE INDEX t1_i2 ON t1(b); /* numeric affinity */ 191 CREATE UNIQUE INDEX t1_i3 ON t1(c); /* text affinity */ 192 193 CREATE TABLE t2(x BLOB, y NUMBER, z TEXT); 194 CREATE UNIQUE INDEX t2_i1 ON t2(x); /* no affinity */ 195 CREATE UNIQUE INDEX t2_i2 ON t2(y); /* numeric affinity */ 196 CREATE UNIQUE INDEX t2_i3 ON t2(z); /* text affinity */ 197 198 INSERT INTO t1 VALUES(1, 1, 1); 199 INSERT INTO t2 VALUES('1', '1', '1'); 200 } 201} {} 202 203do_test in3-3.2 { 204 # No affinity is applied before comparing "x" and "a". Therefore 205 # the index can be used (the comparison is false, text!=number). 206 exec_neph { SELECT x IN (SELECT a FROM t1) FROM t2 } 207} {0 0} 208do_test in3-3.3 { 209 # Logically, numeric affinity is applied to both sides before 210 # the comparison. Therefore it is possible to use index t1_i2. 211 exec_neph { SELECT x IN (SELECT b FROM t1) FROM t2 } 212} {0 1} 213do_test in3-3.4 { 214 # No affinity is applied before the comparison takes place. Making 215 # it possible to use index t1_i3. 216 exec_neph { SELECT x IN (SELECT c FROM t1) FROM t2 } 217} {0 1} 218 219do_test in3-3.5 { 220 # Numeric affinity should be applied to each side before the comparison 221 # takes place. Therefore we cannot use index t1_i1, which has no affinity. 222 exec_neph { SELECT y IN (SELECT a FROM t1) FROM t2 } 223} {1 1} 224do_test in3-3.6 { 225 # Numeric affinity is applied to both sides before 226 # the comparison. Therefore it is possible to use index t1_i2. 227 exec_neph { SELECT y IN (SELECT b FROM t1) FROM t2 } 228} {0 1} 229do_test in3-3.7 { 230 # Numeric affinity is applied before the comparison takes place. 231 # Making it impossible to use index t1_i3. 232 exec_neph { SELECT y IN (SELECT c FROM t1) FROM t2 } 233} {1 1} 234 235#--------------------------------------------------------------------- 236# 237# Test using a multi-column index. 238# 239do_test in3-4.1 { 240 execsql { 241 CREATE TABLE t3(a, b, c); 242 CREATE UNIQUE INDEX t3_i ON t3(b, a); 243 } 244 245 execsql { 246 INSERT INTO t3 VALUES(1, 'numeric', 2); 247 INSERT INTO t3 VALUES(2, 'text', 2); 248 INSERT INTO t3 VALUES(3, 'real', 2); 249 INSERT INTO t3 VALUES(4, 'none', 2); 250 } 251} {} 252do_test in3-4.2 { 253 exec_neph { SELECT 'text' IN (SELECT b FROM t3) } 254} {0 1} 255do_test in3-4.3 { 256 exec_neph { SELECT 'TEXT' COLLATE nocase IN (SELECT b FROM t3) } 257} {1 1} 258do_test in3-4.4 { 259 # A temp table must be used because t3_i.b is not guaranteed to be unique. 260 exec_neph { SELECT b FROM t3 WHERE b IN (SELECT b FROM t3) } 261} {1 none numeric real text} 262do_test in3-4.5 { 263 execsql { CREATE UNIQUE INDEX t3_i2 ON t3(b) } 264 exec_neph { SELECT b FROM t3 WHERE b IN (SELECT b FROM t3) } 265} {0 none numeric real text} 266do_test in3-4.6 { 267 execsql { DROP INDEX t3_i2 } 268} {} 269 270# The following two test cases verify that ticket #2991 has been fixed. 271# 272do_test in3-5.1 { 273 execsql { 274 CREATE TABLE Folders( 275 folderid INTEGER PRIMARY KEY, 276 parentid INTEGER, 277 rootid INTEGER, 278 path VARCHAR(255) 279 ); 280 } 281} {} 282do_test in3-5.2 { 283 catchsql { 284 DELETE FROM Folders WHERE folderid IN 285 (SELECT folderid FROM Folder WHERE path LIKE 'C:\MP3\Albums\' || '%'); 286 } 287} {1 {no such table: Folder}} 288 289finish_test 290