gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/whereG.test (about) 1 # 2013-09-05 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 # 12 # Test cases for query planning decisions and the likely(), unlikely(), and 13 # likelihood() functions. 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix whereG 18 19 do_execsql_test whereG-1.0 { 20 CREATE TABLE composer( 21 cid INTEGER PRIMARY KEY, 22 cname TEXT 23 ); 24 CREATE TABLE album( 25 aid INTEGER PRIMARY KEY, 26 aname TEXT 27 ); 28 CREATE TABLE track( 29 tid INTEGER PRIMARY KEY, 30 cid INTEGER REFERENCES composer, 31 aid INTEGER REFERENCES album, 32 title TEXT 33 ); 34 CREATE INDEX track_i1 ON track(cid); 35 CREATE INDEX track_i2 ON track(aid); 36 INSERT INTO composer VALUES(1, 'W. A. Mozart'); 37 INSERT INTO composer VALUES(2, 'Beethoven'); 38 INSERT INTO composer VALUES(3, 'Thomas Tallis'); 39 INSERT INTO composer VALUES(4, 'Joseph Hayden'); 40 INSERT INTO composer VALUES(5, 'Thomas Weelkes'); 41 INSERT INTO composer VALUES(6, 'J. S. Bach'); 42 INSERT INTO composer VALUES(7, 'Orlando Gibbons'); 43 INSERT INTO composer VALUES(8, 'Josquin des Prés'); 44 INSERT INTO composer VALUES(9, 'Byrd'); 45 INSERT INTO composer VALUES(10, 'Francis Poulenc'); 46 INSERT INTO composer VALUES(11, 'Mendelsshon'); 47 INSERT INTO composer VALUES(12, 'Zoltán Kodály'); 48 INSERT INTO composer VALUES(13, 'Handel'); 49 INSERT INTO album VALUES(100, 'Kodály: Missa Brevis'); 50 INSERT INTO album VALUES(101, 'Messiah'); 51 INSERT INTO album VALUES(102, 'Missa Brevis in D-, K.65'); 52 INSERT INTO album VALUES(103, 'The complete English anthems'); 53 INSERT INTO album VALUES(104, 'Mass in B Minor, BWV 232'); 54 INSERT INTO track VALUES(10005, 12, 100, 'Sanctus'); 55 INSERT INTO track VALUES(10007, 12, 100, 'Agnus Dei'); 56 INSERT INTO track VALUES(10115, 13, 101, 'Surely He Hath Borne Our Griefs'); 57 INSERT INTO track VALUES(10129, 13, 101, 'Since By Man Came Death'); 58 INSERT INTO track VALUES(10206, 1, 102, 'Agnus Dei'); 59 INSERT INTO track VALUES(10301, 3, 103, 'If Ye Love Me'); 60 INSERT INTO track VALUES(10402, 6, 104, 'Domine Deus'); 61 INSERT INTO track VALUES(10403, 6, 104, 'Qui tollis'); 62 } {} 63 do_eqp_test whereG-1.1 { 64 SELECT DISTINCT aname 65 FROM album, composer, track 66 WHERE unlikely(cname LIKE '%bach%') 67 AND composer.cid=track.cid 68 AND album.aid=track.aid; 69 } {composer*track*album} 70 do_execsql_test whereG-1.2 { 71 SELECT DISTINCT aname 72 FROM album, composer, track 73 WHERE unlikely(cname LIKE '%bach%') 74 AND composer.cid=track.cid 75 AND album.aid=track.aid; 76 } {{Mass in B Minor, BWV 232}} 77 78 do_eqp_test whereG-1.3 { 79 SELECT DISTINCT aname 80 FROM album, composer, track 81 WHERE likelihood(cname LIKE '%bach%', 0.5) 82 AND composer.cid=track.cid 83 AND album.aid=track.aid; 84 } {/.*track.*composer.*album.*/} 85 do_execsql_test whereG-1.4 { 86 SELECT DISTINCT aname 87 FROM album, composer, track 88 WHERE likelihood(cname LIKE '%bach%', 0.5) 89 AND composer.cid=track.cid 90 AND album.aid=track.aid; 91 } {{Mass in B Minor, BWV 232}} 92 93 do_eqp_test whereG-1.5 { 94 SELECT DISTINCT aname 95 FROM album, composer, track 96 WHERE cname LIKE '%bach%' 97 AND composer.cid=track.cid 98 AND album.aid=track.aid; 99 } {/.*track.*(composer.*album|album.*composer).*/} 100 do_execsql_test whereG-1.6 { 101 SELECT DISTINCT aname 102 FROM album, composer, track 103 WHERE cname LIKE '%bach%' 104 AND composer.cid=track.cid 105 AND album.aid=track.aid; 106 } {{Mass in B Minor, BWV 232}} 107 108 do_eqp_test whereG-1.7 { 109 SELECT DISTINCT aname 110 FROM album, composer, track 111 WHERE cname LIKE '%bach%' 112 AND unlikely(composer.cid=track.cid) 113 AND unlikely(album.aid=track.aid); 114 } {/.*track.*(composer.*album|album.*composer).*/} 115 do_execsql_test whereG-1.8 { 116 SELECT DISTINCT aname 117 FROM album, composer, track 118 WHERE cname LIKE '%bach%' 119 AND unlikely(composer.cid=track.cid) 120 AND unlikely(album.aid=track.aid); 121 } {{Mass in B Minor, BWV 232}} 122 123 do_test whereG-2.1 { 124 catchsql { 125 SELECT DISTINCT aname 126 FROM album, composer, track 127 WHERE likelihood(cname LIKE '%bach%', -0.01) 128 AND composer.cid=track.cid 129 AND album.aid=track.aid; 130 } 131 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} 132 do_test whereG-2.2 { 133 catchsql { 134 SELECT DISTINCT aname 135 FROM album, composer, track 136 WHERE likelihood(cname LIKE '%bach%', 1.01) 137 AND composer.cid=track.cid 138 AND album.aid=track.aid; 139 } 140 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} 141 do_test whereG-2.3 { 142 catchsql { 143 SELECT DISTINCT aname 144 FROM album, composer, track 145 WHERE likelihood(cname LIKE '%bach%', track.cid) 146 AND composer.cid=track.cid 147 AND album.aid=track.aid; 148 } 149 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} 150 151 # Commuting a term of the WHERE clause should not change the query plan 152 # 153 do_execsql_test whereG-3.0 { 154 CREATE TABLE a(a1 PRIMARY KEY, a2); 155 CREATE TABLE b(b1 PRIMARY KEY, b2); 156 } {} 157 do_eqp_test whereG-3.1 { 158 SELECT * FROM a, b WHERE b1=a1 AND a2=5; 159 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/} 160 do_eqp_test whereG-3.2 { 161 SELECT * FROM a, b WHERE a1=b1 AND a2=5; 162 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/} 163 do_eqp_test whereG-3.3 { 164 SELECT * FROM a, b WHERE a2=5 AND b1=a1; 165 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/} 166 do_eqp_test whereG-3.4 { 167 SELECT * FROM a, b WHERE a2=5 AND a1=b1; 168 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/} 169 170 # Ticket [1e64dd782a126f48d78c43a664844a41d0e6334e]: 171 # Incorrect result in a nested GROUP BY/DISTINCT due to the use of an OP_SCopy 172 # where an OP_Copy was needed. 173 # 174 do_execsql_test whereG-4.0 { 175 CREATE TABLE t4(x); 176 INSERT INTO t4 VALUES('right'),('wrong'); 177 SELECT DISTINCT x 178 FROM (SELECT x FROM t4 GROUP BY x) 179 WHERE x='right' 180 ORDER BY x; 181 } {right} 182 183 #------------------------------------------------------------------------- 184 # Test that likelihood() specifications on indexed terms are taken into 185 # account by various forms of loops. 186 # 187 # 5.1.*: open ended range scans 188 # 5.2.*: skip-scans 189 # 190 reset_db 191 192 do_execsql_test 5.1 { 193 CREATE TABLE t1(a, b, c); 194 CREATE INDEX i1 ON t1(a, b); 195 } 196 do_eqp_test 5.1.2 { 197 SELECT * FROM t1 WHERE a>? 198 } {SEARCH t1 USING INDEX i1 (a>?)} 199 do_eqp_test 5.1.3 { 200 SELECT * FROM t1 WHERE likelihood(a>?, 0.9) 201 } {SCAN t1} 202 do_eqp_test 5.1.4 { 203 SELECT * FROM t1 WHERE likely(a>?) 204 } {SCAN t1} 205 206 do_test 5.2 { 207 for {set i 0} {$i < 100} {incr i} { 208 execsql { INSERT INTO t1 VALUES('abc', $i, $i); } 209 } 210 execsql { INSERT INTO t1 SELECT 'def', b, c FROM t1; } 211 execsql { ANALYZE } 212 } {} 213 do_eqp_test 5.2.2 { 214 SELECT * FROM t1 WHERE likelihood(b>?, 0.01) 215 } {SEARCH t1 USING INDEX i1 (ANY(a) AND b>?)} 216 do_eqp_test 5.2.3 { 217 SELECT * FROM t1 WHERE likelihood(b>?, 0.9) 218 } {SCAN t1} 219 do_eqp_test 5.2.4 { 220 SELECT * FROM t1 WHERE likely(b>?) 221 } {SCAN t1} 222 223 ifcapable stat4 { 224 do_eqp_test 5.3.1.stat4 { 225 SELECT * FROM t1 WHERE a=? 226 } {SCAN t1} 227 } else { 228 do_eqp_test 5.3.1 { 229 SELECT * FROM t1 WHERE a=? 230 } {SEARCH t1 USING INDEX i1} 231 } 232 do_eqp_test 5.3.2 { 233 SELECT * FROM t1 WHERE likelihood(a=?, 0.9) 234 } {SCAN t1} 235 do_eqp_test 5.3.3 { 236 SELECT * FROM t1 WHERE likely(a=?) 237 } {SCAN t1} 238 239 # 2015-06-18 240 # Ticket [https://www.sqlite.org/see/tktview/472f0742a1868fb58862bc588ed70] 241 # 242 do_execsql_test 6.0 { 243 DROP TABLE IF EXISTS t1; 244 CREATE TABLE t1(i int, x, y, z); 245 INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4); 246 DROP TABLE IF EXISTS t2; 247 CREATE TABLE t2(i int, bool char); 248 INSERT INTO t2 VALUES(1,'T'), (2,'F'); 249 SELECT count(*) FROM t1 LEFT JOIN t2 ON t1.i=t2.i AND bool='T'; 250 SELECT count(*) FROM t1 LEFT JOIN t2 ON likely(t1.i=t2.i) AND bool='T'; 251 } {4 4} 252 253 # 2015-06-20 254 # Crash discovered by AFL 255 # 256 do_execsql_test 7.0 { 257 DROP TABLE IF EXISTS t1; 258 CREATE TABLE t1(a, b, PRIMARY KEY(a,b)); 259 INSERT INTO t1 VALUES(9,1),(1,2); 260 DROP TABLE IF EXISTS t2; 261 CREATE TABLE t2(x, y, PRIMARY KEY(x,y)); 262 INSERT INTO t2 VALUES(3,3),(4,4); 263 SELECT likely(a), x FROM t1, t2 ORDER BY 1, 2; 264 } {1 3 1 4 9 3 9 4} 265 do_execsql_test 7.1 { 266 SELECT unlikely(a), x FROM t1, t2 ORDER BY 1, 2; 267 } {1 3 1 4 9 3 9 4} 268 do_execsql_test 7.2 { 269 SELECT likelihood(a,0.5), x FROM t1, t2 ORDER BY 1, 2; 270 } {1 3 1 4 9 3 9 4} 271 do_execsql_test 7.3 { 272 SELECT coalesce(a,a), x FROM t1, t2 ORDER BY 1, 2; 273 } {1 3 1 4 9 3 9 4} 274 275 # 2019-08-22 276 # Ticket https://www.sqlite.org/src/info/7e07a3dbf5a8cd26 277 # 278 do_execsql_test 8.1 { 279 DROP TABLE IF EXISTS t0; 280 CREATE TABLE t0 (c0); 281 INSERT INTO t0(c0) VALUES ('a'); 282 SELECT LIKELY(t0.rowid) <= '0' FROM t0; 283 } {1} 284 do_execsql_test 8.2 { 285 SELECT * FROM t0 WHERE LIKELY(t0.rowid) <= '0'; 286 } {a} 287 do_execsql_test 8.3 { 288 SELECT (t0.rowid) <= '0' FROM t0; 289 } {0} 290 do_execsql_test 8.4 { 291 SELECT * FROM t0 WHERE (t0.rowid) <= '0'; 292 } {} 293 do_execsql_test 8.5 { 294 SELECT unlikely(t0.rowid) <= '0', likelihood(t0.rowid,0.5) <= '0' FROM t0; 295 } {1 1} 296 do_execsql_test 8.6 { 297 SELECT * FROM t0 WHERE unlikely(t0.rowid) <= '0'; 298 } {a} 299 do_execsql_test 8.7 { 300 SELECT * FROM t0 WHERE likelihood(t0.rowid, 0.5) <= '0'; 301 } {a} 302 do_execsql_test 8.8 { 303 SELECT unlikely(t0.rowid <= '0'), 304 likely(t0.rowid <= '0'), 305 likelihood(t0.rowid <= '0',0.5) 306 FROM t0; 307 } {0 0 0} 308 do_execsql_test 8.9 { 309 SELECT * FROM t0 WHERE unlikely(t0.rowid <= '0'); 310 } {} 311 do_execsql_test 8.10 { 312 SELECT * FROM t0 WHERE likelihood(t0.rowid <= '0', 0.5); 313 } {} 314 315 # 2019-12-31: assertion fault discovered by Yongheng's fuzzer. 316 # Harmless memIsValid() due to the code generators failure to 317 # release the registers used by OP_ResultRow. 318 # 319 do_execsql_test 9.10 { 320 DROP TABLE IF EXISTS t1; 321 CREATE TABLE t1(a, b FLOAT); 322 INSERT INTO t1(a) VALUES(''),(NULL),('X'),(NULL); 323 SELECT coalesce(max(quote(a)),10) FROM t1 GROUP BY a; 324 } {NULL '' 'X'} 325 326 # 2020-06-14: assert() changed back into testcase() 327 # ticket 9fb26d37cefaba40 328 # 329 reset_db 330 do_execsql_test 10.1 { 331 CREATE TABLE a(b TEXT); INSERT INTO a VALUES(0),(4),(9); 332 CREATE TABLE c(d NUM); 333 CREATE VIEW f(g, h) AS SELECT b, 0 FROM a UNION SELECT d, d FROM c; 334 SELECT g = g FROM f GROUP BY h; 335 } {1} 336 337 reset_db 338 do_execsql_test 11.0 { 339 CREATE TABLE t1(x PRIMARY KEY, y); 340 INSERT INTO t1 VALUES('AAA', 'BBB'); 341 342 CREATE TABLE t2(z); 343 INSERT INTO t2 VALUES('t2'); 344 345 CREATE TABLE t3(x PRIMARY KEY, y); 346 INSERT INTO t3 VALUES('AAA', 'AAA'); 347 } 348 349 do_execsql_test 11.1.1 { 350 SELECT * FROM t1 JOIN t2 ON unlikely(x=y) AND y='AAA' 351 } 352 do_execsql_test 11.1.2 { 353 SELECT * FROM t1 JOIN t2 ON likely(x=y) AND y='AAA' 354 } 355 do_execsql_test 11.1.3 { 356 SELECT * FROM t1 JOIN t2 ON x=y AND y='AAA' 357 } 358 359 do_execsql_test 11.2.1 { 360 SELECT * FROM t3 JOIN t2 ON unlikely(x=y) AND y='AAA' 361 } {AAA AAA t2} 362 do_execsql_test 11.2.2 { 363 SELECT * FROM t3 JOIN t2 ON likely(x=y) AND y='AAA' 364 } {AAA AAA t2} 365 do_execsql_test 11.2.3 { 366 SELECT * FROM t3 JOIN t2 ON x=y AND y='AAA' 367 } {AAA AAA t2} 368 369 # 2021-06-14 forum https://sqlite.org/forum/forumpost/3b940c437a 370 # Affinity problem when a likely() function is used as a column in 371 # an index. 372 # 373 reset_db 374 do_execsql_test 12.0 { 375 CREATE TABLE t1(a REAL); 376 INSERT INTO t1(a) VALUES(123); 377 CREATE INDEX t1x1 ON t1(likely(a)); 378 SELECT typeof(likely(a)) FROM t1 NOT INDEXED; 379 SELECT typeof(likely(a)) FROM t1 INDEXED BY t1x1; 380 } {real real} 381 do_execsql_test 12.1 { 382 CREATE INDEX t1x2 ON t1(abs(a)); 383 SELECT typeof(abs(a)) FROM t1 NOT INDEXED; 384 SELECT typeof(abs(a)) FROM t1 INDEXED BY t1x2; 385 } {real real} 386 387 388 finish_test