1# 2017-10-11 2# 3set testprefix checkindex 4 5do_execsql_test 1.0 { 6 CREATE TABLE t1(a, b); 7 CREATE INDEX i1 ON t1(a); 8 INSERT INTO t1 VALUES('one', 2); 9 INSERT INTO t1 VALUES('two', 4); 10 INSERT INTO t1 VALUES('three', 6); 11 INSERT INTO t1 VALUES('four', 8); 12 INSERT INTO t1 VALUES('five', 10); 13 14 CREATE INDEX i2 ON t1(a DESC); 15} {} 16 17proc incr_index_check {idx nStep} { 18 set Q { 19 SELECT errmsg, current_key FROM incremental_index_check($idx, $after) 20 LIMIT $nStep 21 } 22 23 set res [list] 24 while {1} { 25 unset -nocomplain current_key 26 set res1 [db eval $Q] 27 if {[llength $res1]==0} break 28 set res [concat $res $res1] 29 set after [lindex $res end] 30 } 31 32 return $res 33} 34 35proc do_index_check_test {tn idx res} { 36 uplevel [list do_execsql_test $tn.1 " 37 SELECT errmsg, current_key FROM incremental_index_check('$idx'); 38 " $res] 39 40 uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]] 41 uplevel [list do_test $tn.3 "incr_index_check $idx 2" [list {*}$res]] 42 uplevel [list do_test $tn.4 "incr_index_check $idx 5" [list {*}$res]] 43} 44 45 46do_execsql_test 1.2.1 { 47 SELECT rowid, errmsg IS NULL, current_key FROM incremental_index_check('i1'); 48} { 49 1 1 'five',5 50 2 1 'four',4 51 3 1 'one',1 52 4 1 'three',3 53 5 1 'two',2 54} 55do_execsql_test 1.2.2 { 56 SELECT errmsg IS NULL, current_key, index_name, after_key, scanner_sql 57 FROM incremental_index_check('i1') LIMIT 1; 58} { 59 1 60 'five',5 61 i1 62 {} 63 {SELECT (SELECT a IS i.i0 FROM 't1' AS t WHERE "rowid" COLLATE BINARY IS i.i1), quote(i0)||','||quote(i1) FROM (SELECT (a) AS i0, ("rowid" COLLATE BINARY) AS i1 FROM 't1' INDEXED BY 'i1' ORDER BY 1,2) AS i} 64} 65 66do_index_check_test 1.3 i1 { 67 {} 'five',5 68 {} 'four',4 69 {} 'one',1 70 {} 'three',3 71 {} 'two',2 72} 73 74do_index_check_test 1.4 i2 { 75 {} 'two',2 76 {} 'three',3 77 {} 'one',1 78 {} 'four',4 79 {} 'five',5 80} 81 82do_test 1.5 { 83 set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t1' }] 84 sqlite3_imposter db main $tblroot {CREATE TABLE xt1(a,b)} 85 db eval { 86 UPDATE xt1 SET a='six' WHERE rowid=3; 87 DELETE FROM xt1 WHERE rowid = 5; 88 } 89 sqlite3_imposter db main 90} {} 91 92do_index_check_test 1.6 i1 { 93 {row missing} 'five',5 94 {} 'four',4 95 {} 'one',1 96 {row data mismatch} 'three',3 97 {} 'two',2 98} 99 100do_index_check_test 1.7 i2 { 101 {} 'two',2 102 {row data mismatch} 'three',3 103 {} 'one',1 104 {} 'four',4 105 {row missing} 'five',5 106} 107 108#-------------------------------------------------------------------------- 109do_execsql_test 2.0 { 110 111 CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c, d); 112 113 INSERT INTO t2 VALUES(1, NULL, 1, 1); 114 INSERT INTO t2 VALUES(2, 1, NULL, 1); 115 INSERT INTO t2 VALUES(3, 1, 1, NULL); 116 117 INSERT INTO t2 VALUES(4, 2, 2, 1); 118 INSERT INTO t2 VALUES(5, 2, 2, 2); 119 INSERT INTO t2 VALUES(6, 2, 2, 3); 120 121 INSERT INTO t2 VALUES(7, 2, 2, 1); 122 INSERT INTO t2 VALUES(8, 2, 2, 2); 123 INSERT INTO t2 VALUES(9, 2, 2, 3); 124 125 CREATE INDEX i3 ON t2(b, c, d); 126 CREATE INDEX i4 ON t2(b DESC, c DESC, d DESC); 127 CREATE INDEX i5 ON t2(d, c DESC, b); 128} {} 129 130do_index_check_test 2.1 i3 { 131 {} NULL,1,1,1 132 {} 1,NULL,1,2 133 {} 1,1,NULL,3 134 {} 2,2,1,4 135 {} 2,2,1,7 136 {} 2,2,2,5 137 {} 2,2,2,8 138 {} 2,2,3,6 139 {} 2,2,3,9 140} 141 142do_index_check_test 2.2 i4 { 143 {} 2,2,3,6 144 {} 2,2,3,9 145 {} 2,2,2,5 146 {} 2,2,2,8 147 {} 2,2,1,4 148 {} 2,2,1,7 149 {} 1,1,NULL,3 150 {} 1,NULL,1,2 151 {} NULL,1,1,1 152} 153 154do_index_check_test 2.3 i5 { 155 {} NULL,1,1,3 156 {} 1,2,2,4 157 {} 1,2,2,7 158 {} 1,1,NULL,1 159 {} 1,NULL,1,2 160 {} 2,2,2,5 161 {} 2,2,2,8 162 {} 3,2,2,6 163 {} 3,2,2,9 164} 165 166#-------------------------------------------------------------------------- 167do_execsql_test 3.0 { 168 169 CREATE TABLE t3(w, x, y, z PRIMARY KEY) WITHOUT ROWID; 170 CREATE INDEX t3wxy ON t3(w, x, y); 171 CREATE INDEX t3wxy2 ON t3(w DESC, x DESC, y DESC); 172 173 INSERT INTO t3 VALUES(NULL, NULL, NULL, 1); 174 INSERT INTO t3 VALUES(NULL, NULL, NULL, 2); 175 INSERT INTO t3 VALUES(NULL, NULL, NULL, 3); 176 177 INSERT INTO t3 VALUES('a', NULL, NULL, 4); 178 INSERT INTO t3 VALUES('a', NULL, NULL, 5); 179 INSERT INTO t3 VALUES('a', NULL, NULL, 6); 180 181 INSERT INTO t3 VALUES('a', 'b', NULL, 7); 182 INSERT INTO t3 VALUES('a', 'b', NULL, 8); 183 INSERT INTO t3 VALUES('a', 'b', NULL, 9); 184 185} {} 186 187do_index_check_test 3.1 t3wxy { 188 {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 189 {} 'a',NULL,NULL,4 {} 'a',NULL,NULL,5 {} 'a',NULL,NULL,6 190 {} 'a','b',NULL,7 {} 'a','b',NULL,8 {} 'a','b',NULL,9 191} 192do_index_check_test 3.2 t3wxy2 { 193 {} 'a','b',NULL,7 {} 'a','b',NULL,8 {} 'a','b',NULL,9 194 {} 'a',NULL,NULL,4 {} 'a',NULL,NULL,5 {} 'a',NULL,NULL,6 195 {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 196} 197 198#-------------------------------------------------------------------------- 199# Test with an index that uses non-default collation sequences. 200# 201do_execsql_test 4.0 { 202 CREATE TABLE t4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT); 203 INSERT INTO t4 VALUES(1, 'aaa', 'bbb'); 204 INSERT INTO t4 VALUES(2, 'AAA', 'CCC'); 205 INSERT INTO t4 VALUES(3, 'aab', 'ddd'); 206 INSERT INTO t4 VALUES(4, 'AAB', 'EEE'); 207 208 CREATE INDEX t4cc ON t4(c1 COLLATE nocase, c2 COLLATE nocase); 209} 210 211do_index_check_test 4.1 t4cc { 212 {} 'aaa','bbb',1 213 {} 'AAA','CCC',2 214 {} 'aab','ddd',3 215 {} 'AAB','EEE',4 216} 217 218do_test 4.2 { 219 set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t4' }] 220 sqlite3_imposter db main $tblroot \ 221 {CREATE TABLE xt4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT)} 222 223 db eval { 224 UPDATE xt4 SET c1='hello' WHERE rowid=2; 225 DELETE FROM xt4 WHERE rowid = 3; 226 } 227 sqlite3_imposter db main 228} {} 229 230do_index_check_test 4.3 t4cc { 231 {} 'aaa','bbb',1 232 {row data mismatch} 'AAA','CCC',2 233 {row missing} 'aab','ddd',3 234 {} 'AAB','EEE',4 235} 236 237#-------------------------------------------------------------------------- 238# Test an index on an expression. 239# 240do_execsql_test 5.0 { 241 CREATE TABLE t5(x INTEGER PRIMARY KEY, y TEXT, UNIQUE(y)); 242 INSERT INTO t5 VALUES(1, '{"x":1, "y":1}'); 243 INSERT INTO t5 VALUES(2, '{"x":2, "y":2}'); 244 INSERT INTO t5 VALUES(3, '{"x":3, "y":3}'); 245 INSERT INTO t5 VALUES(4, '{"w":4, "z":4}'); 246 INSERT INTO t5 VALUES(5, '{"x":5, "y":5}'); 247 248 CREATE INDEX t5x ON t5( json_extract(y, '$.x') ); 249 CREATE INDEX t5y ON t5( json_extract(y, '$.y') DESC ); 250} 251 252do_index_check_test 5.1.1 t5x { 253 {} NULL,4 {} 1,1 {} 2,2 {} 3,3 {} 5,5 254} 255 256do_index_check_test 5.1.2 t5y { 257 {} 5,5 {} 3,3 {} 2,2 {} 1,1 {} NULL,4 258} 259 260do_index_check_test 5.1.3 sqlite_autoindex_t5_1 { 261 {} {'{"w":4, "z":4}',4} 262 {} {'{"x":1, "y":1}',1} 263 {} {'{"x":2, "y":2}',2} 264 {} {'{"x":3, "y":3}',3} 265 {} {'{"x":5, "y":5}',5} 266} 267 268do_test 5.2 { 269 set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t5' }] 270 sqlite3_imposter db main $tblroot \ 271 {CREATE TABLE xt5(a INTEGER PRIMARY KEY, c1 TEXT);} 272 db eval { 273 UPDATE xt5 SET c1='{"x":22, "y":11}' WHERE rowid=1; 274 DELETE FROM xt5 WHERE rowid = 4; 275 } 276 sqlite3_imposter db main 277} {} 278 279do_index_check_test 5.3.1 t5x { 280 {row missing} NULL,4 281 {row data mismatch} 1,1 282 {} 2,2 283 {} 3,3 284 {} 5,5 285} 286 287do_index_check_test 5.3.2 sqlite_autoindex_t5_1 { 288 {row missing} {'{"w":4, "z":4}',4} 289 {row data mismatch} {'{"x":1, "y":1}',1} 290 {} {'{"x":2, "y":2}',2} 291 {} {'{"x":3, "y":3}',3} 292 {} {'{"x":5, "y":5}',5} 293} 294 295#------------------------------------------------------------------------- 296# 297do_execsql_test 6.0 { 298 CREATE TABLE t6(x INTEGER PRIMARY KEY, y, z); 299 CREATE INDEX t6x1 ON t6(y, /* one,two,three */ z); 300 CREATE INDEX t6x2 ON t6(z, -- hello,world, 301 y); 302 303 CREATE INDEX t6x3 ON t6(z -- hello,world 304 , y); 305 306 INSERT INTO t6 VALUES(1, 2, 3); 307 INSERT INTO t6 VALUES(4, 5, 6); 308} 309 310do_index_check_test 6.1 t6x1 { 311 {} 2,3,1 312 {} 5,6,4 313} 314do_index_check_test 6.2 t6x2 { 315 {} 3,2,1 316 {} 6,5,4 317} 318do_index_check_test 6.2 t6x3 { 319 {} 3,2,1 320 {} 6,5,4 321} 322 323#------------------------------------------------------------------------- 324# 325do_execsql_test 7.0 { 326 CREATE TABLE t7(x INTEGER PRIMARY KEY, y, z); 327 INSERT INTO t7 VALUES(1, 1, 1); 328 INSERT INTO t7 VALUES(2, 2, 0); 329 INSERT INTO t7 VALUES(3, 3, 1); 330 INSERT INTO t7 VALUES(4, 4, 0); 331 332 CREATE INDEX t7i1 ON t7(y) WHERE z=1; 333 CREATE INDEX t7i2 ON t7(y) /* hello,world */ WHERE z=1; 334 CREATE INDEX t7i3 ON t7(y) WHERE -- yep 335 z=1; 336 CREATE INDEX t7i4 ON t7(y) WHERE z=1 -- yep; 337} 338do_index_check_test 7.1 t7i1 { 339 {} 1,1 {} 3,3 340} 341do_index_check_test 7.2 t7i2 { 342 {} 1,1 {} 3,3 343} 344do_index_check_test 7.3 t7i3 { 345 {} 1,1 {} 3,3 346} 347do_index_check_test 7.4 t7i4 { 348 {} 1,1 {} 3,3 349} 350