gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/indexexpr2.test (about) 1 # 2017 April 11 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. 12 # 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix indexexpr2 17 18 do_execsql_test 1 { 19 CREATE TABLE t1(a, b); 20 INSERT INTO t1 VALUES(1, 'one'); 21 INSERT INTO t1 VALUES(2, 'two'); 22 INSERT INTO t1 VALUES(3, 'three'); 23 24 CREATE INDEX i1 ON t1(b || 'x'); 25 } 26 27 do_execsql_test 1.1 { 28 SELECT 'TWOX' == (b || 'x') FROM t1 WHERE (b || 'x')>'onex' 29 } {0 0} 30 31 do_execsql_test 1.2 { 32 SELECT 'TWOX' == (b || 'x') COLLATE nocase FROM t1 WHERE (b || 'x')>'onex' 33 } {0 1} 34 35 do_execsql_test 2.0 { 36 CREATE INDEX i2 ON t1(a+1); 37 } 38 39 do_execsql_test 2.1 { 40 SELECT a+1, quote(a+1) FROM t1 ORDER BY 1; 41 } {2 2 3 3 4 4} 42 43 #------------------------------------------------------------------------- 44 # At one point SQLite was incorrectly using indexes on expressions to 45 # optimize ORDER BY and GROUP BY clauses even when the collation 46 # sequences of the query and index did not match (ticket [e20dd54ab0e4]). 47 # The following tests - 3.* - attempt to verify that this has been fixed. 48 # 49 50 reset_db 51 do_execsql_test 3.1.0 { 52 CREATE TABLE t1(a, b); 53 CREATE INDEX i1 ON t1(a, b); 54 } {} 55 56 do_eqp_test 3.1.1 { 57 SELECT b FROM t1 WHERE b IS NOT NULL AND a IS NULL 58 GROUP BY b COLLATE nocase 59 ORDER BY b COLLATE nocase; 60 } {/USE TEMP B-TREE FOR GROUP BY/} 61 62 do_execsql_test 3.2.0 { 63 CREATE TABLE t2(x); 64 65 INSERT INTO t2 VALUES('.ABC'); 66 INSERT INTO t2 VALUES('.abcd'); 67 INSERT INTO t2 VALUES('.defg'); 68 INSERT INTO t2 VALUES('.DEF'); 69 } {} 70 71 do_execsql_test 3.2.1 { 72 SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase; 73 } { 74 .ABC .abcd .DEF .defg 75 } 76 77 do_execsql_test 3.2.2 { 78 CREATE INDEX i2 ON t2( substr(x, 2) ); 79 SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase; 80 } { 81 .ABC .abcd .DEF .defg 82 } 83 84 do_execsql_test 3.3.0 { 85 CREATE TABLE t3(x); 86 } 87 88 ifcapable json1 { 89 do_eqp_test 3.3.1 { 90 SELECT json_extract(x, '$.b') FROM t2 91 WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL 92 GROUP BY json_extract(x, '$.b') COLLATE nocase 93 ORDER BY json_extract(x, '$.b') COLLATE nocase; 94 } [string map {"\n " \n} { 95 QUERY PLAN 96 |--SCAN t2 97 `--USE TEMP B-TREE FOR GROUP BY 98 }] 99 100 do_execsql_test 3.3.2 { 101 CREATE INDEX i3 ON t3(json_extract(x, '$.a'), json_extract(x, '$.b')); 102 } {} 103 104 do_eqp_test 3.3.3 { 105 SELECT json_extract(x, '$.b') FROM t3 106 WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL 107 GROUP BY json_extract(x, '$.b') COLLATE nocase 108 ORDER BY json_extract(x, '$.b') COLLATE nocase; 109 } [string map {"\n " \n} { 110 QUERY PLAN 111 |--SEARCH t3 USING INDEX i3 (<expr>=?) 112 `--USE TEMP B-TREE FOR GROUP BY 113 }] 114 } 115 116 do_execsql_test 3.4.0 { 117 CREATE TABLE t4(a, b); 118 INSERT INTO t4 VALUES('.ABC', 1); 119 INSERT INTO t4 VALUES('.abc', 2); 120 INSERT INTO t4 VALUES('.ABC', 3); 121 INSERT INTO t4 VALUES('.abc', 4); 122 } 123 124 do_execsql_test 3.4.1 { 125 SELECT * FROM t4 126 WHERE substr(a, 2) = 'abc' COLLATE NOCASE 127 ORDER BY substr(a, 2), b; 128 } { 129 .ABC 1 .ABC 3 .abc 2 .abc 4 130 } 131 132 do_execsql_test 3.4.2 { 133 CREATE INDEX i4 ON t4( substr(a, 2) COLLATE NOCASE, b ); 134 SELECT * FROM t4 135 WHERE substr(a, 2) = 'abc' COLLATE NOCASE 136 ORDER BY substr(a, 2), b; 137 } { 138 .ABC 1 .ABC 3 .abc 2 .abc 4 139 } 140 141 do_execsql_test 3.4.3 { 142 DROP INDEX i4; 143 UPDATE t4 SET a = printf('%s%d',a,b); 144 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; 145 } {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4} 146 do_execsql_test 3.4.4 { 147 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary; 148 } {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4} 149 150 do_execsql_test 3.4.5 { 151 CREATE INDEX i4 ON t4( Substr(a,-2) COLLATE nocase ); 152 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; 153 } {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4} 154 do_execsql_test 3.4.5eqp { 155 EXPLAIN QUERY PLAN 156 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; 157 } {/SCAN t4 USING INDEX i4/} 158 do_execsql_test 3.4.6 { 159 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary; 160 } {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4} 161 162 # 2014-09-15: Verify that UPDATEs of columns not referenced by a 163 # index on expression do not modify the index. 164 # 165 unset -nocomplain cnt 166 set cnt 0 167 proc refcnt {x} { 168 global cnt 169 incr cnt 170 return $x 171 } 172 db close 173 sqlite3 db :memory: 174 db function refcnt -deterministic refcnt 175 do_test 4.100 { 176 db eval { 177 CREATE TABLE t1(a,b,c,d,e,f); 178 CREATE INDEX t1abc ON t1(refcnt(a+b+c)); 179 } 180 set ::cnt 181 } {0} 182 do_test 4.110 { 183 db eval {INSERT INTO t1 VALUES(1,2,3,4,5,6);} 184 set ::cnt 185 # The refcnt() function is invoked once to compute the index value 186 } {1} 187 do_test 4.120 { 188 set ::cnt 0 189 db eval {UPDATE t1 SET b=b+1;} 190 set ::cnt 191 # The refcnt() function is invoked twice, once to remove the old index 192 # entry and a second time to insert the new one. 193 } {2} 194 do_test 4.130 { 195 set ::cnt 0 196 db eval {UPDATE t1 SET d=d+1;} 197 set ::cnt 198 # Refcnt() should not be invoked because that index does not change. 199 } {0} 200 201 # Additional test cases to show that UPDATE does not modify indexes that 202 # do not involve unchanged columns. 203 # 204 ifcapable vtab { 205 load_static_extension db explain 206 do_execsql_test 4.200 { 207 CREATE TABLE t2(a,b,c,d,e,f); 208 INSERT INTO t2 VALUES(2,3,4,5,6,7); 209 CREATE INDEX t2abc ON t2(a+b+c); 210 CREATE INDEX t2cd ON t2(c*d); 211 CREATE INDEX t2def ON t2(d,e+25*f); 212 SELECT sqlite_master.name 213 FROM sqlite_master, explain('UPDATE t2 SET b=b+1') 214 WHERE explain.opcode LIKE 'Open%' 215 AND sqlite_master.rootpage=explain.p2 216 ORDER BY 1; 217 } {t2 t2abc} 218 do_execsql_test 4.210 { 219 SELECT sqlite_master.name 220 FROM sqlite_master, explain('UPDATE t2 SET c=c+1') 221 WHERE explain.opcode LIKE 'Open%' 222 AND sqlite_master.rootpage=explain.p2 223 ORDER BY 1; 224 } {t2 t2abc t2cd} 225 do_execsql_test 4.220 { 226 SELECT sqlite_master.name 227 FROM sqlite_master, explain('UPDATE t2 SET c=c+1, f=NULL') 228 WHERE explain.opcode LIKE 'Open%' 229 AND sqlite_master.rootpage=explain.p2 230 ORDER BY 1; 231 } {t2 t2abc t2cd t2def} 232 } 233 234 #------------------------------------------------------------------------- 235 # Test that ticket [d96eba87] has been fixed. 236 # 237 do_execsql_test 5.0 { 238 CREATE TABLE t5(a INTEGER, b INTEGER); 239 INSERT INTO t5 VALUES(2, 4), (3, 9); 240 } 241 do_execsql_test 5.1 { 242 SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9; 243 } {2 4 3 9} 244 do_execsql_test 5.2 { 245 CREATE INDEX t5a ON t5( abs(a) ); 246 CREATE INDEX t5b ON t5( abs(b) ); 247 } 248 do_execsql_test 5.4 { 249 SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9; 250 } {2 4 3 9} 251 252 #------------------------------------------------------------------------- 253 do_execsql_test 6.0 { 254 CREATE TABLE x1(a INTEGER PRIMARY KEY, b); 255 INSERT INTO x1 VALUES 256 (1, 123), (2, '123'), (3, '123abc'), (4, 123.0), (5, 1234); 257 } 258 259 do_execsql_test 6.1.1 { 260 SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123; 261 } {1 123 2 123 3 123abc 4 123.0} 262 do_execsql_test 6.1.2 { 263 CREATE INDEX x1i ON x1( CAST(b AS INTEGER) ); 264 SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123; 265 } {1 123 2 123 3 123abc 4 123.0} 266 do_eqp_test 6.1.3 { 267 SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123; 268 } {SEARCH x1 USING INDEX x1i (<expr>=?)} 269 270 do_execsql_test 6.2.1 { 271 SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123; 272 } {1 123 2 123} 273 do_execsql_test 6.2.2 { 274 CREATE INDEX x1i2 ON x1( CAST(b AS TEXT) ); 275 SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123; 276 } {1 123 2 123} 277 do_eqp_test 6.2.3 { 278 SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123; 279 } {SEARCH x1 USING INDEX x1i2 (<expr>=?)} 280 281 do_execsql_test 7.0 { 282 CREATE TABLE IF NOT EXISTS t0(c0); 283 INSERT INTO t0(c0) VALUES (-9223372036854775808); 284 BEGIN; 285 } 286 do_catchsql_test 7.1 { 287 CREATE INDEX i0 ON t0(ABS(c0)); 288 } {1 {integer overflow}} 289 do_execsql_test 7.2 { 290 COMMIT; 291 SELECT sql FROM sqlite_master WHERE tbl_name = 't0'; 292 CREATE INDEX i0 ON t0(c0); 293 } {{CREATE TABLE t0(c0)}} 294 do_execsql_test 7.3 { 295 REINDEX; 296 } {} 297 298 #------------------------------------------------------------------------- 299 reset_db 300 do_execsql_test 8.0 { 301 CREATE TABLE t0(c0); 302 CREATE INDEX i0 ON t0(c0) WHERE c0 NOT NULL; 303 INSERT INTO t0(c0) VALUES (NULL); 304 } 305 306 do_execsql_test 8.1.1 { 307 SELECT * FROM t0 WHERE ~('' BETWEEN t0.c0 AND TRUE); 308 } {{}} 309 do_execsql_test 8.1.2 { 310 SELECT ~('' BETWEEN t0.c0 AND TRUE) FROM t0; 311 } {-1} 312 313 foreach {tn expr} { 314 1 " 0 == (34 BETWEEN c0 AND 33)" 315 2 " 1 != (34 BETWEEN c0 AND 33)" 316 3 "-1 < (34 BETWEEN c0 AND 33)" 317 4 "-1 <= (34 BETWEEN c0 AND 33)" 318 5 " 1 > (34 BETWEEN c0 AND 33)" 319 6 " 1 >= (34 BETWEEN c0 AND 33)" 320 7 " 1 - (34 BETWEEN c0 AND 33)" 321 8 "-1 + (34 BETWEEN c0 AND 33)" 322 9 " 1 | (34 BETWEEN c0 AND 33)" 323 10 " 1 << (34 BETWEEN c0 AND 33)" 324 11 " 1 >> (34 BETWEEN c0 AND 33)" 325 12 " 1 || (34 BETWEEN c0 AND 33)" 326 } { 327 do_execsql_test 8.3.$tn.1 "SELECT * FROM t0 WHERE $expr ORDER BY c0" { {} } 328 do_execsql_test 8.3.$tn.2 "SELECT ($expr) IS TRUE FROM t0" { 1 } 329 } 330 331 do_execsql_test 8.4 { 332 CREATE TABLE t1(a, b); 333 INSERT INTO t1 VALUES(1, 2), (3, 4); 334 CREATE TABLE t2(x, y); 335 } 336 337 foreach {tn expr} { 338 1 " 0 == (a=0 AND y=1)" 339 2 " 1 != (a=0 AND y=1)" 340 3 "-1 < (a=0 AND y=1)" 341 4 "-1 <= (a=0 AND y=1)" 342 5 " 1 > (a=0 AND y=1)" 343 6 " 1 >= (a=0 AND y=1)" 344 7 " 1 - (a=0 AND y=1)" 345 8 "-1 + (a=0 AND y=1)" 346 9 " 1 | (a=0 AND y=1)" 347 10 "1 << (a=0 AND y=1)" 348 11 "1 >> (a=0 AND y=1)" 349 12 "1 || (a=0 AND y=1)" 350 351 13 " 0 == (10 BETWEEN y AND b)" 352 14 " 1 != (10 BETWEEN y AND b)" 353 15 "-1 < (10 BETWEEN y AND b)" 354 16 "-1 <= (10 BETWEEN y AND b)" 355 17 " 1 > (10 BETWEEN y AND b)" 356 18 " 1 >= (10 BETWEEN y AND b)" 357 19 " 1 - (10 BETWEEN y AND b)" 358 20 "-1 + (10 BETWEEN y AND b)" 359 21 " 1 | (10 BETWEEN y AND b)" 360 22 " 1 << (10 BETWEEN y AND b)" 361 23 " 1 >> (10 BETWEEN y AND b)" 362 24 " 1 || (10 BETWEEN y AND b)" 363 364 25 " 1 || (10 BETWEEN y AND b)" 365 } { 366 do_execsql_test 8.5.$tn.1 " 367 SELECT * FROM t1 LEFT JOIN t2 WHERE $expr 368 " {1 2 {} {} 3 4 {} {}} 369 370 do_execsql_test 8.5.$tn.2 " 371 SELECT ($expr) IS TRUE FROM t1 LEFT JOIN t2 372 " {1 1} 373 } 374 375 finish_test