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 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix indexexpr2 17 18do_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 27do_execsql_test 1.1 { 28 SELECT 'TWOX' == (b || 'x') FROM t1 WHERE (b || 'x')>'onex' 29} {0 0} 30 31do_execsql_test 1.2 { 32 SELECT 'TWOX' == (b || 'x') COLLATE nocase FROM t1 WHERE (b || 'x')>'onex' 33} {0 1} 34 35do_execsql_test 2.0 { 36 CREATE INDEX i2 ON t1(a+1); 37} 38 39do_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 50reset_db 51do_execsql_test 3.1.0 { 52 CREATE TABLE t1(a, b); 53 CREATE INDEX i1 ON t1(a, b); 54} {} 55 56do_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 62do_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 71do_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 77do_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 84do_execsql_test 3.3.0 { 85 CREATE TABLE t3(x); 86} 87 88ifcapable 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 TABLE 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 TABLE t3 USING INDEX i3 (<expr>=?) 112 `--USE TEMP B-TREE FOR GROUP BY 113 }] 114} 115 116do_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 124do_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 132do_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 141do_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} 146do_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 150do_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} 154do_execsql_test 3.4.5eqp { 155 EXPLAIN QUERY PLAN 156 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; 157} {/SCAN TABLE t4 USING INDEX i4/} 158do_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# 165unset -nocomplain cnt 166set cnt 0 167proc refcnt {x} { 168 global cnt 169 incr cnt 170 return $x 171} 172db close 173sqlite3 db :memory: 174db function refcnt -deterministic refcnt 175do_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} 182do_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} 187do_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} 194do_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# 204ifcapable 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# 237do_execsql_test 5.0 { 238 CREATE TABLE t5(a INTEGER, b INTEGER); 239 INSERT INTO t5 VALUES(2, 4), (3, 9); 240} 241do_execsql_test 5.1 { 242 SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9; 243} {2 4 3 9} 244do_execsql_test 5.2 { 245 CREATE INDEX t5a ON t5( abs(a) ); 246 CREATE INDEX t5b ON t5( abs(b) ); 247} 248do_execsql_test 5.4 { 249 SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9; 250} {2 4 3 9} 251 252#------------------------------------------------------------------------- 253do_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 259do_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} 262do_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} 266do_eqp_test 6.1.3 { 267 SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123; 268} {SEARCH TABLE x1 USING INDEX x1i (<expr>=?)} 269 270do_execsql_test 6.2.1 { 271 SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123; 272} {1 123 2 123} 273do_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} 277do_eqp_test 6.2.3 { 278 SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123; 279} {SEARCH TABLE x1 USING INDEX x1i2 (<expr>=?)} 280 281do_execsql_test 7.0 { 282 CREATE TABLE IF NOT EXISTS t0(c0); 283 INSERT INTO t0(c0) VALUES (-9223372036854775808); 284 BEGIN; 285} 286do_catchsql_test 7.1 { 287 CREATE INDEX i0 ON t0(ABS(c0)); 288} {1 {integer overflow}} 289do_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)}} 294do_execsql_test 7.3 { 295 REINDEX; 296} {} 297 298#------------------------------------------------------------------------- 299reset_db 300do_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 306do_execsql_test 8.1.1 { 307 SELECT * FROM t0 WHERE ~('' BETWEEN t0.c0 AND TRUE); 308} {{}} 309do_execsql_test 8.1.2 { 310 SELECT ~('' BETWEEN t0.c0 AND TRUE) FROM t0; 311} {-1} 312 313foreach {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 331do_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 337foreach {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 375finish_test 376