| 1 |
# 2001 September 15 |
| 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. The |
| 12 |
# focus of this file is testing the CREATE INDEX statement. |
| 13 |
# |
| 14 |
# $Id: index.test,v 1.43 2008/01/16 18:20:42 danielk1977 Exp $ |
| 15 |
|
| 16 |
set testdir [file dirname $argv0] |
| 17 |
source $testdir/tester.tcl |
| 18 |
|
| 19 |
# Create a basic index and verify it is added to sqlite_master |
| 20 |
# |
| 21 |
do_test index-1.1 { |
| 22 |
execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)} |
| 23 |
execsql {CREATE INDEX index1 ON test1(f1)} |
| 24 |
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
| 25 |
} {index1 test1} |
| 26 |
do_test index-1.1b { |
| 27 |
execsql {SELECT name, sql, tbl_name, type FROM sqlite_master |
| 28 |
WHERE name='index1'} |
| 29 |
} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index} |
| 30 |
do_test index-1.1c { |
| 31 |
db close |
| 32 |
sqlite3 db test.db |
| 33 |
execsql {SELECT name, sql, tbl_name, type FROM sqlite_master |
| 34 |
WHERE name='index1'} |
| 35 |
} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index} |
| 36 |
do_test index-1.1d { |
| 37 |
db close |
| 38 |
sqlite3 db test.db |
| 39 |
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
| 40 |
} {index1 test1} |
| 41 |
|
| 42 |
# Verify that the index dies with the table |
| 43 |
# |
| 44 |
do_test index-1.2 { |
| 45 |
execsql {DROP TABLE test1} |
| 46 |
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
| 47 |
} {} |
| 48 |
|
| 49 |
# Try adding an index to a table that does not exist |
| 50 |
# |
| 51 |
do_test index-2.1 { |
| 52 |
set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg] |
| 53 |
lappend v $msg |
| 54 |
} {1 {no such table: main.test1}} |
| 55 |
|
| 56 |
# Try adding an index on a column of a table where the table |
| 57 |
# exists but the column does not. |
| 58 |
# |
| 59 |
do_test index-2.1b { |
| 60 |
execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)} |
| 61 |
set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg] |
| 62 |
lappend v $msg |
| 63 |
} {1 {no such column: f4}} |
| 64 |
|
| 65 |
# Try an index with some columns that match and others that do now. |
| 66 |
# |
| 67 |
do_test index-2.2 { |
| 68 |
set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg] |
| 69 |
execsql {DROP TABLE test1} |
| 70 |
lappend v $msg |
| 71 |
} {1 {no such column: f4}} |
| 72 |
|
| 73 |
# Try creating a bunch of indices on the same table |
| 74 |
# |
| 75 |
set r {} |
| 76 |
for {set i 1} {$i<100} {incr i} { |
| 77 |
lappend r [format index%02d $i] |
| 78 |
} |
| 79 |
do_test index-3.1 { |
| 80 |
execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)} |
| 81 |
for {set i 1} {$i<100} {incr i} { |
| 82 |
set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])" |
| 83 |
execsql $sql |
| 84 |
} |
| 85 |
execsql {SELECT name FROM sqlite_master |
| 86 |
WHERE type='index' AND tbl_name='test1' |
| 87 |
ORDER BY name} |
| 88 |
} $r |
| 89 |
integrity_check index-3.2.1 |
| 90 |
ifcapable {reindex} { |
| 91 |
do_test index-3.2.2 { |
| 92 |
execsql REINDEX |
| 93 |
} {} |
| 94 |
} |
| 95 |
integrity_check index-3.2.3 |
| 96 |
|
| 97 |
|
| 98 |
# Verify that all the indices go away when we drop the table. |
| 99 |
# |
| 100 |
do_test index-3.3 { |
| 101 |
execsql {DROP TABLE test1} |
| 102 |
execsql {SELECT name FROM sqlite_master |
| 103 |
WHERE type='index' AND tbl_name='test1' |
| 104 |
ORDER BY name} |
| 105 |
} {} |
| 106 |
|
| 107 |
# Create a table and insert values into that table. Then create |
| 108 |
# an index on that table. Verify that we can select values |
| 109 |
# from the table correctly using the index. |
| 110 |
# |
| 111 |
# Note that the index names "index9" and "indext" are chosen because |
| 112 |
# they both have the same hash. |
| 113 |
# |
| 114 |
do_test index-4.1 { |
| 115 |
execsql {CREATE TABLE test1(cnt int, power int)} |
| 116 |
for {set i 1} {$i<20} {incr i} { |
| 117 |
execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])" |
| 118 |
} |
| 119 |
execsql {CREATE INDEX index9 ON test1(cnt)} |
| 120 |
execsql {CREATE INDEX indext ON test1(power)} |
| 121 |
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
| 122 |
} {index9 indext test1} |
| 123 |
do_test index-4.2 { |
| 124 |
execsql {SELECT cnt FROM test1 WHERE power=4} |
| 125 |
} {2} |
| 126 |
do_test index-4.3 { |
| 127 |
execsql {SELECT cnt FROM test1 WHERE power=1024} |
| 128 |
} {10} |
| 129 |
do_test index-4.4 { |
| 130 |
execsql {SELECT power FROM test1 WHERE cnt=6} |
| 131 |
} {64} |
| 132 |
do_test index-4.5 { |
| 133 |
execsql {DROP INDEX indext} |
| 134 |
execsql {SELECT power FROM test1 WHERE cnt=6} |
| 135 |
} {64} |
| 136 |
do_test index-4.6 { |
| 137 |
execsql {SELECT cnt FROM test1 WHERE power=1024} |
| 138 |
} {10} |
| 139 |
do_test index-4.7 { |
| 140 |
execsql {CREATE INDEX indext ON test1(cnt)} |
| 141 |
execsql {SELECT power FROM test1 WHERE cnt=6} |
| 142 |
} {64} |
| 143 |
do_test index-4.8 { |
| 144 |
execsql {SELECT cnt FROM test1 WHERE power=1024} |
| 145 |
} {10} |
| 146 |
do_test index-4.9 { |
| 147 |
execsql {DROP INDEX index9} |
| 148 |
execsql {SELECT power FROM test1 WHERE cnt=6} |
| 149 |
} {64} |
| 150 |
do_test index-4.10 { |
| 151 |
execsql {SELECT cnt FROM test1 WHERE power=1024} |
| 152 |
} {10} |
| 153 |
do_test index-4.11 { |
| 154 |
execsql {DROP INDEX indext} |
| 155 |
execsql {SELECT power FROM test1 WHERE cnt=6} |
| 156 |
} {64} |
| 157 |
do_test index-4.12 { |
| 158 |
execsql {SELECT cnt FROM test1 WHERE power=1024} |
| 159 |
} {10} |
| 160 |
do_test index-4.13 { |
| 161 |
execsql {DROP TABLE test1} |
| 162 |
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
| 163 |
} {} |
| 164 |
integrity_check index-4.14 |
| 165 |
|
| 166 |
# Do not allow indices to be added to sqlite_master |
| 167 |
# |
| 168 |
do_test index-5.1 { |
| 169 |
set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg] |
| 170 |
lappend v $msg |
| 171 |
} {1 {table sqlite_master may not be indexed}} |
| 172 |
do_test index-5.2 { |
| 173 |
execsql {SELECT name FROM sqlite_master WHERE type!='meta'} |
| 174 |
} {} |
| 175 |
|
| 176 |
# Do not allow indices with duplicate names to be added |
| 177 |
# |
| 178 |
do_test index-6.1 { |
| 179 |
execsql {CREATE TABLE test1(f1 int, f2 int)} |
| 180 |
execsql {CREATE TABLE test2(g1 real, g2 real)} |
| 181 |
execsql {CREATE INDEX index1 ON test1(f1)} |
| 182 |
set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg] |
| 183 |
lappend v $msg |
| 184 |
} {1 {index index1 already exists}} |
| 185 |
do_test index-6.1.1 { |
| 186 |
catchsql {CREATE INDEX [index1] ON test2(g1)} |
| 187 |
} {1 {index index1 already exists}} |
| 188 |
do_test index-6.1b { |
| 189 |
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
| 190 |
} {index1 test1 test2} |
| 191 |
do_test index-6.1c { |
| 192 |
catchsql {CREATE INDEX IF NOT EXISTS index1 ON test1(f1)} |
| 193 |
} {0 {}} |
| 194 |
do_test index-6.2 { |
| 195 |
set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg] |
| 196 |
lappend v $msg |
| 197 |
} {1 {there is already a table named test1}} |
| 198 |
do_test index-6.2b { |
| 199 |
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
| 200 |
} {index1 test1 test2} |
| 201 |
do_test index-6.3 { |
| 202 |
execsql {DROP TABLE test1} |
| 203 |
execsql {DROP TABLE test2} |
| 204 |
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
| 205 |
} {} |
| 206 |
do_test index-6.4 { |
| 207 |
execsql { |
| 208 |
CREATE TABLE test1(a,b); |
| 209 |
CREATE INDEX index1 ON test1(a); |
| 210 |
CREATE INDEX index2 ON test1(b); |
| 211 |
CREATE INDEX index3 ON test1(a,b); |
| 212 |
DROP TABLE test1; |
| 213 |
SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name; |
| 214 |
} |
| 215 |
} {} |
| 216 |
integrity_check index-6.5 |
| 217 |
|
| 218 |
|
| 219 |
# Create a primary key |
| 220 |
# |
| 221 |
do_test index-7.1 { |
| 222 |
execsql {CREATE TABLE test1(f1 int, f2 int primary key)} |
| 223 |
for {set i 1} {$i<20} {incr i} { |
| 224 |
execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])" |
| 225 |
} |
| 226 |
execsql {SELECT count(*) FROM test1} |
| 227 |
} {19} |
| 228 |
do_test index-7.2 { |
| 229 |
execsql {SELECT f1 FROM test1 WHERE f2=65536} |
| 230 |
} {16} |
| 231 |
do_test index-7.3 { |
| 232 |
execsql { |
| 233 |
SELECT name FROM sqlite_master |
| 234 |
WHERE type='index' AND tbl_name='test1' |
| 235 |
} |
| 236 |
} {sqlite_autoindex_test1_1} |
| 237 |
do_test index-7.4 { |
| 238 |
execsql {DROP table test1} |
| 239 |
execsql {SELECT name FROM sqlite_master WHERE type!='meta'} |
| 240 |
} {} |
| 241 |
integrity_check index-7.5 |
| 242 |
|
| 243 |
# Make sure we cannot drop a non-existant index. |
| 244 |
# |
| 245 |
do_test index-8.1 { |
| 246 |
set v [catch {execsql {DROP INDEX index1}} msg] |
| 247 |
lappend v $msg |
| 248 |
} {1 {no such index: index1}} |
| 249 |
|
| 250 |
# Make sure we don't actually create an index when the EXPLAIN keyword |
| 251 |
# is used. |
| 252 |
# |
| 253 |
do_test index-9.1 { |
| 254 |
execsql {CREATE TABLE tab1(a int)} |
| 255 |
ifcapable {explain} { |
| 256 |
execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)} |
| 257 |
} |
| 258 |
execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'} |
| 259 |
} {tab1} |
| 260 |
do_test index-9.2 { |
| 261 |
execsql {CREATE INDEX idx1 ON tab1(a)} |
| 262 |
execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name} |
| 263 |
} {idx1 tab1} |
| 264 |
integrity_check index-9.3 |
| 265 |
|
| 266 |
# Allow more than one entry with the same key. |
| 267 |
# |
| 268 |
do_test index-10.0 { |
| 269 |
execsql { |
| 270 |
CREATE TABLE t1(a int, b int); |
| 271 |
CREATE INDEX i1 ON t1(a); |
| 272 |
INSERT INTO t1 VALUES(1,2); |
| 273 |
INSERT INTO t1 VALUES(2,4); |
| 274 |
INSERT INTO t1 VALUES(3,8); |
| 275 |
INSERT INTO t1 VALUES(1,12); |
| 276 |
SELECT b FROM t1 WHERE a=1 ORDER BY b; |
| 277 |
} |
| 278 |
} {2 12} |
| 279 |
do_test index-10.1 { |
| 280 |
execsql { |
| 281 |
SELECT b FROM t1 WHERE a=2 ORDER BY b; |
| 282 |
} |
| 283 |
} {4} |
| 284 |
do_test index-10.2 { |
| 285 |
execsql { |
| 286 |
DELETE FROM t1 WHERE b=12; |
| 287 |
SELECT b FROM t1 WHERE a=1 ORDER BY b; |
| 288 |
} |
| 289 |
} {2} |
| 290 |
do_test index-10.3 { |
| 291 |
execsql { |
| 292 |
DELETE FROM t1 WHERE b=2; |
| 293 |
SELECT b FROM t1 WHERE a=1 ORDER BY b; |
| 294 |
} |
| 295 |
} {} |
| 296 |
do_test index-10.4 { |
| 297 |
execsql { |
| 298 |
DELETE FROM t1; |
| 299 |
INSERT INTO t1 VALUES (1,1); |
| 300 |
INSERT INTO t1 VALUES (1,2); |
| 301 |
INSERT INTO t1 VALUES (1,3); |
| 302 |
INSERT INTO t1 VALUES (1,4); |
| 303 |
INSERT INTO t1 VALUES (1,5); |
| 304 |
INSERT INTO t1 VALUES (1,6); |
| 305 |
INSERT INTO t1 VALUES (1,7); |
| 306 |
INSERT INTO t1 VALUES (1,8); |
| 307 |
INSERT INTO t1 VALUES (1,9); |
| 308 |
INSERT INTO t1 VALUES (2,0); |
| 309 |
SELECT b FROM t1 WHERE a=1 ORDER BY b; |
| 310 |
} |
| 311 |
} {1 2 3 4 5 6 7 8 9} |
| 312 |
do_test index-10.5 { |
| 313 |
ifcapable subquery { |
| 314 |
execsql { DELETE FROM t1 WHERE b IN (2, 4, 6, 8); } |
| 315 |
} else { |
| 316 |
execsql { DELETE FROM t1 WHERE b = 2 OR b = 4 OR b = 6 OR b = 8; } |
| 317 |
} |
| 318 |
execsql { |
| 319 |
SELECT b FROM t1 WHERE a=1 ORDER BY b; |
| 320 |
} |
| 321 |
} {1 3 5 7 9} |
| 322 |
do_test index-10.6 { |
| 323 |
execsql { |
| 324 |
DELETE FROM t1 WHERE b>2; |
| 325 |
SELECT b FROM t1 WHERE a=1 ORDER BY b; |
| 326 |
} |
| 327 |
} {1} |
| 328 |
do_test index-10.7 { |
| 329 |
execsql { |
| 330 |
DELETE FROM t1 WHERE b=1; |
| 331 |
SELECT b FROM t1 WHERE a=1 ORDER BY b; |
| 332 |
} |
| 333 |
} {} |
| 334 |
do_test index-10.8 { |
| 335 |
execsql { |
| 336 |
SELECT b FROM t1 ORDER BY b; |
| 337 |
} |
| 338 |
} {0} |
| 339 |
integrity_check index-10.9 |
| 340 |
|
| 341 |
# Automatically create an index when we specify a primary key. |
| 342 |
# |
| 343 |
do_test index-11.1 { |
| 344 |
execsql { |
| 345 |
CREATE TABLE t3( |
| 346 |
a text, |
| 347 |
b int, |
| 348 |
c float, |
| 349 |
PRIMARY KEY(b) |
| 350 |
); |
| 351 |
} |
| 352 |
for {set i 1} {$i<=50} {incr i} { |
| 353 |
execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)" |
| 354 |
} |
| 355 |
set sqlite_search_count 0 |
| 356 |
concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count |
| 357 |
} {0.1 2} |
| 358 |
integrity_check index-11.2 |
| 359 |
|
| 360 |
|
| 361 |
# Numeric strings should compare as if they were numbers. So even if the |
| 362 |
# strings are not character-by-character the same, if they represent the |
| 363 |
# same number they should compare equal to one another. Verify that this |
| 364 |
# is true in indices. |
| 365 |
# |
| 366 |
# Updated for sqlite3 v3: SQLite will now store these values as numbers |
| 367 |
# (because the affinity of column a is NUMERIC) so the quirky |
| 368 |
# representations are not retained. i.e. '+1.0' becomes '1'. |
| 369 |
do_test index-12.1 { |
| 370 |
execsql { |
| 371 |
CREATE TABLE t4(a NUM,b); |
| 372 |
INSERT INTO t4 VALUES('0.0',1); |
| 373 |
INSERT INTO t4 VALUES('0.00',2); |
| 374 |
INSERT INTO t4 VALUES('abc',3); |
| 375 |
INSERT INTO t4 VALUES('-1.0',4); |
| 376 |
INSERT INTO t4 VALUES('+1.0',5); |
| 377 |
INSERT INTO t4 VALUES('0',6); |
| 378 |
INSERT INTO t4 VALUES('00000',7); |
| 379 |
SELECT a FROM t4 ORDER BY b; |
| 380 |
} |
| 381 |
} {0 0 abc -1 1 0 0} |
| 382 |
do_test index-12.2 { |
| 383 |
execsql { |
| 384 |
SELECT a FROM t4 WHERE a==0 ORDER BY b |
| 385 |
} |
| 386 |
} {0 0 0 0} |
| 387 |
do_test index-12.3 { |
| 388 |
execsql { |
| 389 |
SELECT a FROM t4 WHERE a<0.5 ORDER BY b |
| 390 |
} |
| 391 |
} {0 0 -1 0 0} |
| 392 |
do_test index-12.4 { |
| 393 |
execsql { |
| 394 |
SELECT a FROM t4 WHERE a>-0.5 ORDER BY b |
| 395 |
} |
| 396 |
} {0 0 abc 1 0 0} |
| 397 |
do_test index-12.5 { |
| 398 |
execsql { |
| 399 |
CREATE INDEX t4i1 ON t4(a); |
| 400 |
SELECT a FROM t4 WHERE a==0 ORDER BY b |
| 401 |
} |
| 402 |
} {0 0 0 0} |
| 403 |
do_test index-12.6 { |
| 404 |
execsql { |
| 405 |
SELECT a FROM t4 WHERE a<0.5 ORDER BY b |
| 406 |
} |
| 407 |
} {0 0 -1 0 0} |
| 408 |
do_test index-12.7 { |
| 409 |
execsql { |
| 410 |
SELECT a FROM t4 WHERE a>-0.5 ORDER BY b |
| 411 |
} |
| 412 |
} {0 0 abc 1 0 0} |
| 413 |
integrity_check index-12.8 |
| 414 |
|
| 415 |
# Make sure we cannot drop an automatically created index. |
| 416 |
# |
| 417 |
do_test index-13.1 { |
| 418 |
execsql { |
| 419 |
CREATE TABLE t5( |
| 420 |
a int UNIQUE, |
| 421 |
b float PRIMARY KEY, |
| 422 |
c varchar(10), |
| 423 |
UNIQUE(a,c) |
| 424 |
); |
| 425 |
INSERT INTO t5 VALUES(1,2,3); |
| 426 |
SELECT * FROM t5; |
| 427 |
} |
| 428 |
} {1 2.0 3} |
| 429 |
do_test index-13.2 { |
| 430 |
set ::idxlist [execsql { |
| 431 |
SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='t5'; |
| 432 |
}] |
| 433 |
llength $::idxlist |
| 434 |
} {3} |
| 435 |
for {set i 0} {$i<[llength $::idxlist]} {incr i} { |
| 436 |
do_test index-13.3.$i { |
| 437 |
catchsql " |
| 438 |
DROP INDEX '[lindex $::idxlist $i]'; |
| 439 |
" |
| 440 |
} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}} |
| 441 |
} |
| 442 |
do_test index-13.4 { |
| 443 |
execsql { |
| 444 |
INSERT INTO t5 VALUES('a','b','c'); |
| 445 |
SELECT * FROM t5; |
| 446 |
} |
| 447 |
} {1 2.0 3 a b c} |
| 448 |
integrity_check index-13.5 |
| 449 |
|
| 450 |
# Check the sort order of data in an index. |
| 451 |
# |
| 452 |
do_test index-14.1 { |
| 453 |
execsql { |
| 454 |
CREATE TABLE t6(a,b,c); |
| 455 |
CREATE INDEX t6i1 ON t6(a,b); |
| 456 |
INSERT INTO t6 VALUES('','',1); |
| 457 |
INSERT INTO t6 VALUES('',NULL,2); |
| 458 |
INSERT INTO t6 VALUES(NULL,'',3); |
| 459 |
INSERT INTO t6 VALUES('abc',123,4); |
| 460 |
INSERT INTO t6 VALUES(123,'abc',5); |
| 461 |
SELECT c FROM t6 ORDER BY a,b; |
| 462 |
} |
| 463 |
} {3 5 2 1 4} |
| 464 |
do_test index-14.2 { |
| 465 |
execsql { |
| 466 |
SELECT c FROM t6 WHERE a=''; |
| 467 |
} |
| 468 |
} {2 1} |
| 469 |
do_test index-14.3 { |
| 470 |
execsql { |
| 471 |
SELECT c FROM t6 WHERE b=''; |
| 472 |
} |
| 473 |
} {1 3} |
| 474 |
do_test index-14.4 { |
| 475 |
execsql { |
| 476 |
SELECT c FROM t6 WHERE a>''; |
| 477 |
} |
| 478 |
} {4} |
| 479 |
do_test index-14.5 { |
| 480 |
execsql { |
| 481 |
SELECT c FROM t6 WHERE a>=''; |
| 482 |
} |
| 483 |
} {2 1 4} |
| 484 |
do_test index-14.6 { |
| 485 |
execsql { |
| 486 |
SELECT c FROM t6 WHERE a>123; |
| 487 |
} |
| 488 |
} {2 1 4} |
| 489 |
do_test index-14.7 { |
| 490 |
execsql { |
| 491 |
SELECT c FROM t6 WHERE a>=123; |
| 492 |
} |
| 493 |
} {5 2 1 4} |
| 494 |
do_test index-14.8 { |
| 495 |
execsql { |
| 496 |
SELECT c FROM t6 WHERE a<'abc'; |
| 497 |
} |
| 498 |
} {5 2 1} |
| 499 |
do_test index-14.9 { |
| 500 |
execsql { |
| 501 |
SELECT c FROM t6 WHERE a<='abc'; |
| 502 |
} |
| 503 |
} {5 2 1 4} |
| 504 |
do_test index-14.10 { |
| 505 |
execsql { |
| 506 |
SELECT c FROM t6 WHERE a<=''; |
| 507 |
} |
| 508 |
} {5 2 1} |
| 509 |
do_test index-14.11 { |
| 510 |
execsql { |
| 511 |
SELECT c FROM t6 WHERE a<''; |
| 512 |
} |
| 513 |
} {5} |
| 514 |
integrity_check index-14.12 |
| 515 |
|
| 516 |
do_test index-15.1 { |
| 517 |
execsql { |
| 518 |
DELETE FROM t1; |
| 519 |
SELECT * FROM t1; |
| 520 |
} |
| 521 |
} {} |
| 522 |
do_test index-15.2 { |
| 523 |
execsql { |
| 524 |
INSERT INTO t1 VALUES('1.234e5',1); |
| 525 |
INSERT INTO t1 VALUES('12.33e04',2); |
| 526 |
INSERT INTO t1 VALUES('12.35E4',3); |
| 527 |
INSERT INTO t1 VALUES('12.34e',4); |
| 528 |
INSERT INTO t1 VALUES('12.32e+4',5); |
| 529 |
INSERT INTO t1 VALUES('12.36E+04',6); |
| 530 |
INSERT INTO t1 VALUES('12.36E+',7); |
| 531 |
INSERT INTO t1 VALUES('+123.10000E+0003',8); |
| 532 |
INSERT INTO t1 VALUES('+',9); |
| 533 |
INSERT INTO t1 VALUES('+12347.E+02',10); |
| 534 |
INSERT INTO t1 VALUES('+12347E+02',11); |
| 535 |
INSERT INTO t1 VALUES('+.125E+04',12); |
| 536 |
INSERT INTO t1 VALUES('-.125E+04',13); |
| 537 |
INSERT INTO t1 VALUES('.125E+0',14); |
| 538 |
INSERT INTO t1 VALUES('.125',15); |
| 539 |
SELECT b FROM t1 ORDER BY a, b; |
| 540 |
} |
| 541 |
} {13 14 15 12 8 5 2 1 3 6 10 11 9 4 7} |
| 542 |
do_test index-15.3 { |
| 543 |
execsql { |
| 544 |
SELECT b FROM t1 WHERE typeof(a) IN ('integer','real') ORDER BY b; |
| 545 |
} |
| 546 |
} {1 2 3 5 6 8 10 11 12 13 14 15} |
| 547 |
integrity_check index-15.4 |
| 548 |
|
| 549 |
# The following tests - index-16.* - test that when a table definition |
| 550 |
# includes qualifications that specify the same constraint twice only a |
| 551 |
# single index is generated to enforce the constraint. |
| 552 |
# |
| 553 |
# For example: "CREATE TABLE abc( x PRIMARY KEY, UNIQUE(x) );" |
| 554 |
# |
| 555 |
do_test index-16.1 { |
| 556 |
execsql { |
| 557 |
CREATE TABLE t7(c UNIQUE PRIMARY KEY); |
| 558 |
SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; |
| 559 |
} |
| 560 |
} {1} |
| 561 |
do_test index-16.2 { |
| 562 |
execsql { |
| 563 |
DROP TABLE t7; |
| 564 |
CREATE TABLE t7(c UNIQUE PRIMARY KEY); |
| 565 |
SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; |
| 566 |
} |
| 567 |
} {1} |
| 568 |
do_test index-16.3 { |
| 569 |
execsql { |
| 570 |
DROP TABLE t7; |
| 571 |
CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) ); |
| 572 |
SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; |
| 573 |
} |
| 574 |
} {1} |
| 575 |
do_test index-16.4 { |
| 576 |
execsql { |
| 577 |
DROP TABLE t7; |
| 578 |
CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) ); |
| 579 |
SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; |
| 580 |
} |
| 581 |
} {1} |
| 582 |
do_test index-16.5 { |
| 583 |
execsql { |
| 584 |
DROP TABLE t7; |
| 585 |
CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) ); |
| 586 |
SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; |
| 587 |
} |
| 588 |
} {2} |
| 589 |
|
| 590 |
# Test that automatically create indices are named correctly. The current |
| 591 |
# convention is: "sqlite_autoindex_<table name>_<integer>" |
| 592 |
# |
| 593 |
# Then check that it is an error to try to drop any automtically created |
| 594 |
# indices. |
| 595 |
do_test index-17.1 { |
| 596 |
execsql { |
| 597 |
DROP TABLE t7; |
| 598 |
CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) ); |
| 599 |
SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; |
| 600 |
} |
| 601 |
} {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3} |
| 602 |
do_test index-17.2 { |
| 603 |
catchsql { |
| 604 |
DROP INDEX sqlite_autoindex_t7_1; |
| 605 |
} |
| 606 |
} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}} |
| 607 |
do_test index-17.3 { |
| 608 |
catchsql { |
| 609 |
DROP INDEX IF EXISTS sqlite_autoindex_t7_1; |
| 610 |
} |
| 611 |
} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}} |
| 612 |
do_test index-17.4 { |
| 613 |
catchsql { |
| 614 |
DROP INDEX IF EXISTS no_such_index; |
| 615 |
} |
| 616 |
} {0 {}} |
| 617 |
|
| 618 |
|
| 619 |
# The following tests ensure that it is not possible to explicitly name |
| 620 |
# a schema object with a name beginning with "sqlite_". Granted that is a |
| 621 |
# little outside the focus of this test scripts, but this has got to be |
| 622 |
# tested somewhere. |
| 623 |
do_test index-18.1 { |
| 624 |
catchsql { |
| 625 |
CREATE TABLE sqlite_t1(a, b, c); |
| 626 |
} |
| 627 |
} {1 {object name reserved for internal use: sqlite_t1}} |
| 628 |
do_test index-18.1.2 { |
| 629 |
catchsql { |
| 630 |
CREATE TABLE sqlite_t1(a, b, c); |
| 631 |
} |
| 632 |
} {1 {object name reserved for internal use: sqlite_t1}} |
| 633 |
sqlite3_db_config db DEFENSIVE 0 |
| 634 |
do_test index-18.2 { |
| 635 |
catchsql { |
| 636 |
CREATE INDEX sqlite_i1 ON t7(c); |
| 637 |
} |
| 638 |
} {1 {object name reserved for internal use: sqlite_i1}} |
| 639 |
ifcapable view { |
| 640 |
do_test index-18.3 { |
| 641 |
catchsql { |
| 642 |
CREATE VIEW sqlite_v1 AS SELECT * FROM t7; |
| 643 |
} |
| 644 |
} {1 {object name reserved for internal use: sqlite_v1}} |
| 645 |
} ;# ifcapable view |
| 646 |
ifcapable {trigger} { |
| 647 |
do_test index-18.4 { |
| 648 |
catchsql { |
| 649 |
CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END; |
| 650 |
} |
| 651 |
} {1 {object name reserved for internal use: sqlite_tr1}} |
| 652 |
} |
| 653 |
do_test index-18.5 { |
| 654 |
execsql { |
| 655 |
DROP TABLE t7; |
| 656 |
} |
| 657 |
} {} |
| 658 |
|
| 659 |
# These tests ensure that if multiple table definition constraints are |
| 660 |
# implemented by a single indice, the correct ON CONFLICT policy applies. |
| 661 |
ifcapable conflict { |
| 662 |
do_test index-19.1 { |
| 663 |
execsql { |
| 664 |
CREATE TABLE t7(a UNIQUE PRIMARY KEY); |
| 665 |
CREATE TABLE t8(a UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK); |
| 666 |
INSERT INTO t7 VALUES(1); |
| 667 |
INSERT INTO t8 VALUES(1); |
| 668 |
} |
| 669 |
} {} |
| 670 |
do_test index-19.2 { |
| 671 |
catchsql { |
| 672 |
BEGIN; |
| 673 |
INSERT INTO t7 VALUES(1); |
| 674 |
} |
| 675 |
} {1 {UNIQUE constraint failed: t7.a}} |
| 676 |
do_test index-19.3 { |
| 677 |
catchsql { |
| 678 |
BEGIN; |
| 679 |
} |
| 680 |
} {1 {cannot start a transaction within a transaction}} |
| 681 |
do_test index-19.4 { |
| 682 |
catchsql { |
| 683 |
INSERT INTO t8 VALUES(1); |
| 684 |
} |
| 685 |
} {1 {UNIQUE constraint failed: t8.a}} |
| 686 |
do_test index-19.5 { |
| 687 |
catchsql { |
| 688 |
BEGIN; |
| 689 |
COMMIT; |
| 690 |
} |
| 691 |
} {0 {}} |
| 692 |
do_test index-19.6 { |
| 693 |
catchsql { |
| 694 |
DROP TABLE t7; |
| 695 |
DROP TABLE t8; |
| 696 |
CREATE TABLE t7( |
| 697 |
a PRIMARY KEY ON CONFLICT FAIL, |
| 698 |
UNIQUE(a) ON CONFLICT IGNORE |
| 699 |
); |
| 700 |
} |
| 701 |
} {1 {conflicting ON CONFLICT clauses specified}} |
| 702 |
} ; # end of "ifcapable conflict" block |
| 703 |
|
| 704 |
ifcapable {reindex} { |
| 705 |
do_test index-19.7 { |
| 706 |
execsql REINDEX |
| 707 |
} {} |
| 708 |
} |
| 709 |
integrity_check index-19.8 |
| 710 |
|
| 711 |
# Drop index with a quoted name. Ticket #695. |
| 712 |
# |
| 713 |
do_test index-20.1 { |
| 714 |
execsql { |
| 715 |
CREATE INDEX "t6i2" ON t6(c); |
| 716 |
DROP INDEX "t6i2"; |
| 717 |
} |
| 718 |
} {} |
| 719 |
do_test index-20.2 { |
| 720 |
execsql { |
| 721 |
DROP INDEX "t6i1"; |
| 722 |
} |
| 723 |
} {} |
| 724 |
|
| 725 |
# Try to create a TEMP index on a non-TEMP table. */ |
| 726 |
# |
| 727 |
do_test index-21.1 { |
| 728 |
catchsql { |
| 729 |
CREATE INDEX temp.i21 ON t6(c); |
| 730 |
} |
| 731 |
} {1 {cannot create a TEMP index on non-TEMP table "t6"}} |
| 732 |
do_test index-21.2 { |
| 733 |
catchsql { |
| 734 |
CREATE TEMP TABLE t6(x); |
| 735 |
INSERT INTO temp.t6 values(1),(5),(9); |
| 736 |
CREATE INDEX temp.i21 ON t6(x); |
| 737 |
SELECT x FROM t6 ORDER BY x DESC; |
| 738 |
} |
| 739 |
} {0 {9 5 1}} |
| 740 |
|
| 741 |
# 2019-05-01 ticket https://sqlite.org/src/info/3be1295b264be2fa |
| 742 |
do_execsql_test index-22.0 { |
| 743 |
DROP TABLE IF EXISTS t1; |
| 744 |
CREATE TABLE t1(a, b TEXT); |
| 745 |
CREATE UNIQUE INDEX IF NOT EXISTS x1 ON t1(b==0); |
| 746 |
CREATE INDEX IF NOT EXISTS x2 ON t1(a || 0) WHERE b; |
| 747 |
INSERT INTO t1(a,b) VALUES('a',1),('a',0); |
| 748 |
SELECT a, b, '|' FROM t1; |
| 749 |
} {a 1 | a 0 |} |
| 750 |
|
| 751 |
# 2019-05-10 ticket https://sqlite.org/src/info/ae0f637bddc5290b |
| 752 |
do_execsql_test index-23.0 { |
| 753 |
DROP TABLE t1; |
| 754 |
CREATE TABLE t1(a TEXT, b REAL); |
| 755 |
CREATE UNIQUE INDEX t1x1 ON t1(a GLOB b); |
| 756 |
INSERT INTO t1(a,b) VALUES('0.0','1'),('1.0','1'); |
| 757 |
SELECT * FROM t1; |
| 758 |
REINDEX; |
| 759 |
} {0.0 1.0 1.0 1.0} |
| 760 |
do_execsql_test index-23.1 { |
| 761 |
DROP TABLE t1; |
| 762 |
CREATE TABLE t1(a REAL); |
| 763 |
CREATE UNIQUE INDEX index_0 ON t1(TYPEOF(a)); |
| 764 |
INSERT OR IGNORE INTO t1(a) VALUES (0.1),(FALSE); |
| 765 |
SELECT * FROM t1; |
| 766 |
REINDEX; |
| 767 |
} {0.1} |
| 768 |
|
| 769 |
finish_test |