gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/intpkey.test (about) 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. 12 # 13 # This file implements tests for the special processing associated 14 # with INTEGER PRIMARY KEY columns. 15 # 16 # $Id: intpkey.test,v 1.24 2007/11/29 17:43:28 danielk1977 Exp $ 17 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 21 # Create a table with a primary key and a datatype other than 22 # integer 23 # 24 do_test intpkey-1.0 { 25 execsql { 26 CREATE TABLE t1(a TEXT PRIMARY KEY, b, c); 27 } 28 } {} 29 30 # There should be an index associated with the primary key 31 # 32 do_test intpkey-1.1 { 33 execsql { 34 SELECT name FROM sqlite_master 35 WHERE type='index' AND tbl_name='t1'; 36 } 37 } {sqlite_autoindex_t1_1} 38 39 # Now create a table with an integer primary key and verify that 40 # there is no associated index. 41 # 42 do_test intpkey-1.2 { 43 execsql { 44 DROP TABLE t1; 45 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 46 SELECT name FROM sqlite_master 47 WHERE type='index' AND tbl_name='t1'; 48 } 49 } {} 50 51 # Insert some records into the new table. Specify the primary key 52 # and verify that the key is used as the record number. 53 # 54 do_test intpkey-1.3 { 55 execsql { 56 INSERT INTO t1 VALUES(5,'hello','world'); 57 } 58 db last_insert_rowid 59 } {5} 60 do_test intpkey-1.4 { 61 execsql { 62 SELECT * FROM t1; 63 } 64 } {5 hello world} 65 do_test intpkey-1.5 { 66 execsql { 67 SELECT rowid, * FROM t1; 68 } 69 } {5 5 hello world} 70 71 # Attempting to insert a duplicate primary key should give a constraint 72 # failure. 73 # 74 do_test intpkey-1.6 { 75 set r [catch {execsql { 76 INSERT INTO t1 VALUES(5,'second','entry'); 77 }} msg] 78 lappend r $msg 79 } {1 {UNIQUE constraint failed: t1.a}} 80 do_test intpkey-1.7 { 81 execsql { 82 SELECT rowid, * FROM t1; 83 } 84 } {5 5 hello world} 85 do_test intpkey-1.8 { 86 set r [catch {execsql { 87 INSERT INTO t1 VALUES(6,'second','entry'); 88 }} msg] 89 lappend r $msg 90 } {0 {}} 91 do_test intpkey-1.8.1 { 92 db last_insert_rowid 93 } {6} 94 do_test intpkey-1.9 { 95 execsql { 96 SELECT rowid, * FROM t1; 97 } 98 } {5 5 hello world 6 6 second entry} 99 100 # A ROWID is automatically generated for new records that do not specify 101 # the integer primary key. 102 # 103 do_test intpkey-1.10 { 104 execsql { 105 INSERT INTO t1(b,c) VALUES('one','two'); 106 SELECT b FROM t1 ORDER BY b; 107 } 108 } {hello one second} 109 110 # Try to change the ROWID for the new entry. 111 # 112 do_test intpkey-1.11 { 113 execsql { 114 UPDATE t1 SET a=4 WHERE b='one'; 115 SELECT * FROM t1; 116 } 117 } {4 one two 5 hello world 6 second entry} 118 119 # Make sure SELECT statements are able to use the primary key column 120 # as an index. 121 # 122 do_test intpkey-1.12.1 { 123 execsql { 124 SELECT * FROM t1 WHERE a==4; 125 } 126 } {4 one two} 127 do_test intpkey-1.12.2 { 128 execsql { 129 EXPLAIN QUERY PLAN 130 SELECT * FROM t1 WHERE a==4; 131 } 132 } {/SEARCH t1 /} 133 134 # Try to insert a non-integer value into the primary key field. This 135 # should result in a data type mismatch. 136 # 137 do_test intpkey-1.13.1 { 138 set r [catch {execsql { 139 INSERT INTO t1 VALUES('x','y','z'); 140 }} msg] 141 lappend r $msg 142 } {1 {datatype mismatch}} 143 do_test intpkey-1.13.2 { 144 set r [catch {execsql { 145 INSERT INTO t1 VALUES('','y','z'); 146 }} msg] 147 lappend r $msg 148 } {1 {datatype mismatch}} 149 do_test intpkey-1.14 { 150 set r [catch {execsql { 151 INSERT INTO t1 VALUES(3.4,'y','z'); 152 }} msg] 153 lappend r $msg 154 } {1 {datatype mismatch}} 155 do_test intpkey-1.15 { 156 set r [catch {execsql { 157 INSERT INTO t1 VALUES(-3,'y','z'); 158 }} msg] 159 lappend r $msg 160 } {0 {}} 161 do_test intpkey-1.16 { 162 execsql {SELECT * FROM t1} 163 } {-3 y z 4 one two 5 hello world 6 second entry} 164 165 #### INDICES 166 # Check to make sure indices work correctly with integer primary keys 167 # 168 do_test intpkey-2.1 { 169 execsql { 170 CREATE INDEX i1 ON t1(b); 171 SELECT * FROM t1 WHERE b=='y' 172 } 173 } {-3 y z} 174 do_test intpkey-2.1.1 { 175 execsql { 176 SELECT * FROM t1 WHERE b=='y' AND rowid<0 177 } 178 } {-3 y z} 179 do_test intpkey-2.1.2 { 180 execsql { 181 SELECT * FROM t1 WHERE b=='y' AND rowid<0 AND rowid>=-20 182 } 183 } {-3 y z} 184 do_test intpkey-2.1.3 { 185 execsql { 186 SELECT * FROM t1 WHERE b>='y' 187 } 188 } {-3 y z} 189 do_test intpkey-2.1.4 { 190 execsql { 191 SELECT * FROM t1 WHERE b>='y' AND rowid<10 192 } 193 } {-3 y z} 194 195 do_test intpkey-2.2 { 196 execsql { 197 UPDATE t1 SET a=8 WHERE b=='y'; 198 SELECT * FROM t1 WHERE b=='y'; 199 } 200 } {8 y z} 201 do_test intpkey-2.3 { 202 execsql { 203 SELECT rowid, * FROM t1; 204 } 205 } {4 4 one two 5 5 hello world 6 6 second entry 8 8 y z} 206 do_test intpkey-2.4 { 207 execsql { 208 SELECT rowid, * FROM t1 WHERE b<'second' 209 } 210 } {5 5 hello world 4 4 one two} 211 do_test intpkey-2.4.1 { 212 execsql { 213 SELECT rowid, * FROM t1 WHERE 'second'>b 214 } 215 } {5 5 hello world 4 4 one two} 216 do_test intpkey-2.4.2 { 217 execsql { 218 SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b 219 } 220 } {4 4 one two 5 5 hello world} 221 do_test intpkey-2.4.3 { 222 execsql { 223 SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid 224 } 225 } {4 4 one two 5 5 hello world} 226 do_test intpkey-2.5 { 227 execsql { 228 SELECT rowid, * FROM t1 WHERE b>'a' 229 } 230 } {5 5 hello world 4 4 one two 6 6 second entry 8 8 y z} 231 do_test intpkey-2.6 { 232 execsql { 233 DELETE FROM t1 WHERE rowid=4; 234 SELECT * FROM t1 WHERE b>'a'; 235 } 236 } {5 hello world 6 second entry 8 y z} 237 do_test intpkey-2.7 { 238 execsql { 239 UPDATE t1 SET a=-4 WHERE rowid=8; 240 SELECT * FROM t1 WHERE b>'a'; 241 } 242 } {5 hello world 6 second entry -4 y z} 243 do_test intpkey-2.7 { 244 execsql { 245 SELECT * FROM t1 246 } 247 } {-4 y z 5 hello world 6 second entry} 248 249 # Do an SQL statement. Append the search count to the end of the result. 250 # 251 proc count sql { 252 set ::sqlite_search_count 0 253 return [concat [execsql $sql] $::sqlite_search_count] 254 } 255 256 # Create indices that include the integer primary key as one of their 257 # columns. 258 # 259 do_test intpkey-3.1 { 260 execsql { 261 CREATE INDEX i2 ON t1(a); 262 } 263 } {} 264 do_test intpkey-3.2 { 265 count { 266 SELECT * FROM t1 WHERE a=5; 267 } 268 } {5 hello world 0} 269 do_test intpkey-3.3 { 270 count { 271 SELECT * FROM t1 WHERE a>4 AND a<6; 272 } 273 } {5 hello world 2} 274 do_test intpkey-3.4 { 275 count { 276 SELECT * FROM t1 WHERE b>='hello' AND b<'hello2'; 277 } 278 } {5 hello world 3} 279 do_test intpkey-3.5 { 280 execsql { 281 CREATE INDEX i3 ON t1(c,a); 282 } 283 } {} 284 do_test intpkey-3.6 { 285 count { 286 SELECT * FROM t1 WHERE c=='world'; 287 } 288 } {5 hello world 3} 289 do_test intpkey-3.7 { 290 execsql {INSERT INTO t1 VALUES(11,'hello','world')} 291 count { 292 SELECT * FROM t1 WHERE c=='world'; 293 } 294 } {5 hello world 11 hello world 5} 295 do_test intpkey-3.8 { 296 count { 297 SELECT * FROM t1 WHERE c=='world' AND a>7; 298 } 299 } {11 hello world 3} 300 do_test intpkey-3.9 { 301 count { 302 SELECT * FROM t1 WHERE 7<a; 303 } 304 } {11 hello world 1} 305 306 # Test inequality constraints on integer primary keys and rowids 307 # 308 do_test intpkey-4.1 { 309 count { 310 SELECT * FROM t1 WHERE 11=rowid 311 } 312 } {11 hello world 0} 313 do_test intpkey-4.2 { 314 count { 315 SELECT * FROM t1 WHERE 11=rowid AND b=='hello' 316 } 317 } {11 hello world 0} 318 do_test intpkey-4.3 { 319 count { 320 SELECT * FROM t1 WHERE 11=rowid AND b=='hello' AND c IS NOT NULL; 321 } 322 } {11 hello world 0} 323 do_test intpkey-4.4 { 324 count { 325 SELECT * FROM t1 WHERE rowid==11 326 } 327 } {11 hello world 0} 328 do_test intpkey-4.5 { 329 count { 330 SELECT * FROM t1 WHERE oid==11 AND b=='hello' 331 } 332 } {11 hello world 0} 333 do_test intpkey-4.6 { 334 count { 335 SELECT * FROM t1 WHERE a==11 AND b=='hello' AND c IS NOT NULL; 336 } 337 } {11 hello world 0} 338 339 do_test intpkey-4.7 { 340 count { 341 SELECT * FROM t1 WHERE 8<rowid; 342 } 343 } {11 hello world 1} 344 do_test intpkey-4.8 { 345 count { 346 SELECT * FROM t1 WHERE 8<rowid AND 11>=oid; 347 } 348 } {11 hello world 1} 349 do_test intpkey-4.9 { 350 count { 351 SELECT * FROM t1 WHERE 11<=_rowid_ AND 12>=a; 352 } 353 } {11 hello world 1} 354 do_test intpkey-4.10 { 355 count { 356 SELECT * FROM t1 WHERE 0>=_rowid_; 357 } 358 } {-4 y z 1} 359 do_test intpkey-4.11 { 360 count { 361 SELECT * FROM t1 WHERE a<0; 362 } 363 } {-4 y z 1} 364 do_test intpkey-4.12 { 365 count { 366 SELECT * FROM t1 WHERE a<0 AND a>10; 367 } 368 } {1} 369 370 # Make sure it is OK to insert a rowid of 0 371 # 372 do_test intpkey-5.1 { 373 execsql { 374 INSERT INTO t1 VALUES(0,'zero','entry'); 375 } 376 count { 377 SELECT * FROM t1 WHERE a=0; 378 } 379 } {0 zero entry 0} 380 do_test intpkey-5.2 { 381 execsql { 382 SELECT rowid, a FROM t1 ORDER BY rowid 383 } 384 } {-4 -4 0 0 5 5 6 6 11 11} 385 386 # Test the ability of the COPY command to put data into a 387 # table that contains an integer primary key. 388 # 389 # COPY command has been removed. But we retain these tests so 390 # that the tables will contain the right data for tests that follow. 391 # 392 do_test intpkey-6.1 { 393 execsql { 394 BEGIN; 395 INSERT INTO t1 VALUES(20,'b-20','c-20'); 396 INSERT INTO t1 VALUES(21,'b-21','c-21'); 397 INSERT INTO t1 VALUES(22,'b-22','c-22'); 398 COMMIT; 399 SELECT * FROM t1 WHERE a>=20; 400 } 401 } {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22} 402 do_test intpkey-6.2 { 403 execsql { 404 SELECT * FROM t1 WHERE b=='hello' 405 } 406 } {5 hello world 11 hello world} 407 do_test intpkey-6.3 { 408 execsql { 409 DELETE FROM t1 WHERE b='b-21'; 410 SELECT * FROM t1 WHERE b=='b-21'; 411 } 412 } {} 413 do_test intpkey-6.4 { 414 execsql { 415 SELECT * FROM t1 WHERE a>=20 416 } 417 } {20 b-20 c-20 22 b-22 c-22} 418 419 # Do an insert of values with the columns specified out of order. 420 # 421 do_test intpkey-7.1 { 422 execsql { 423 INSERT INTO t1(c,b,a) VALUES('row','new',30); 424 SELECT * FROM t1 WHERE rowid>=30; 425 } 426 } {30 new row} 427 do_test intpkey-7.2 { 428 execsql { 429 SELECT * FROM t1 WHERE rowid>20; 430 } 431 } {22 b-22 c-22 30 new row} 432 433 # Do an insert from a select statement. 434 # 435 do_test intpkey-8.1 { 436 execsql { 437 CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z); 438 INSERT INTO t2 SELECT * FROM t1; 439 SELECT rowid FROM t2; 440 } 441 } {-4 0 5 6 11 20 22 30} 442 do_test intpkey-8.2 { 443 execsql { 444 SELECT x FROM t2; 445 } 446 } {-4 0 5 6 11 20 22 30} 447 448 do_test intpkey-9.1 { 449 execsql { 450 UPDATE t1 SET c='www' WHERE c='world'; 451 SELECT rowid, a, c FROM t1 WHERE c=='www'; 452 } 453 } {5 5 www 11 11 www} 454 455 456 # Check insert of NULL for primary key 457 # 458 do_test intpkey-10.1 { 459 execsql { 460 DROP TABLE t2; 461 CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z); 462 INSERT INTO t2 VALUES(NULL, 1, 2); 463 SELECT * from t2; 464 } 465 } {1 1 2} 466 do_test intpkey-10.2 { 467 execsql { 468 INSERT INTO t2 VALUES(NULL, 2, 3); 469 SELECT * from t2 WHERE x=2; 470 } 471 } {2 2 3} 472 do_test intpkey-10.3 { 473 execsql { 474 INSERT INTO t2 SELECT NULL, z, y FROM t2; 475 SELECT * FROM t2; 476 } 477 } {1 1 2 2 2 3 3 2 1 4 3 2} 478 479 # This tests checks to see if a floating point number can be used 480 # to reference an integer primary key. 481 # 482 do_test intpkey-11.1 { 483 execsql { 484 SELECT b FROM t1 WHERE a=2.0+3.0; 485 } 486 } {hello} 487 do_test intpkey-11.1 { 488 execsql { 489 SELECT b FROM t1 WHERE a=2.0+3.5; 490 } 491 } {} 492 493 integrity_check intpkey-12.1 494 495 # Try to use a string that looks like a floating point number as 496 # an integer primary key. This should actually work when the floating 497 # point value can be rounded to an integer without loss of data. 498 # 499 do_test intpkey-13.1 { 500 execsql { 501 SELECT * FROM t1 WHERE a=1; 502 } 503 } {} 504 do_test intpkey-13.2 { 505 execsql { 506 INSERT INTO t1 VALUES('1.0',2,3); 507 SELECT * FROM t1 WHERE a=1; 508 } 509 } {1 2 3} 510 do_test intpkey-13.3 { 511 catchsql { 512 INSERT INTO t1 VALUES('1.5',3,4); 513 } 514 } {1 {datatype mismatch}} 515 ifcapable {bloblit} { 516 do_test intpkey-13.4 { 517 catchsql { 518 INSERT INTO t1 VALUES(x'123456',3,4); 519 } 520 } {1 {datatype mismatch}} 521 } 522 do_test intpkey-13.5 { 523 catchsql { 524 INSERT INTO t1 VALUES('+1234567890',3,4); 525 } 526 } {0 {}} 527 528 # Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER 529 # affinity should be applied to the text value before the comparison 530 # takes place. 531 # 532 do_test intpkey-14.1 { 533 execsql { 534 CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT); 535 INSERT INTO t3 VALUES(1, 1, 'one'); 536 INSERT INTO t3 VALUES(2, 2, '2'); 537 INSERT INTO t3 VALUES(3, 3, 3); 538 } 539 } {} 540 do_test intpkey-14.2 { 541 execsql { 542 SELECT * FROM t3 WHERE a>2; 543 } 544 } {3 3 3} 545 do_test intpkey-14.3 { 546 execsql { 547 SELECT * FROM t3 WHERE a>'2'; 548 } 549 } {3 3 3} 550 do_test intpkey-14.4 { 551 execsql { 552 SELECT * FROM t3 WHERE a<'2'; 553 } 554 } {1 1 one} 555 do_test intpkey-14.5 { 556 execsql { 557 SELECT * FROM t3 WHERE a<c; 558 } 559 } {1 1 one} 560 do_test intpkey-14.6 { 561 execsql { 562 SELECT * FROM t3 WHERE a=c; 563 } 564 } {2 2 2 3 3 3} 565 566 # Check for proper handling of primary keys greater than 2^31. 567 # Ticket #1188 568 # 569 do_test intpkey-15.1 { 570 execsql { 571 INSERT INTO t1 VALUES(2147483647, 'big-1', 123); 572 SELECT * FROM t1 WHERE a>2147483648; 573 } 574 } {} 575 do_test intpkey-15.2 { 576 execsql { 577 INSERT INTO t1 VALUES(NULL, 'big-2', 234); 578 SELECT b FROM t1 WHERE a>=2147483648; 579 } 580 } {big-2} 581 do_test intpkey-15.3 { 582 execsql { 583 SELECT b FROM t1 WHERE a>2147483648; 584 } 585 } {} 586 do_test intpkey-15.4 { 587 execsql { 588 SELECT b FROM t1 WHERE a>=2147483647; 589 } 590 } {big-1 big-2} 591 do_test intpkey-15.5 { 592 execsql { 593 SELECT b FROM t1 WHERE a<2147483648; 594 } 595 } {y zero 2 hello second hello b-20 b-22 new 3 big-1} 596 do_test intpkey-15.6 { 597 execsql { 598 SELECT b FROM t1 WHERE a<12345678901; 599 } 600 } {y zero 2 hello second hello b-20 b-22 new 3 big-1 big-2} 601 do_test intpkey-15.7 { 602 execsql { 603 SELECT b FROM t1 WHERE a>12345678901; 604 } 605 } {} 606 607 # 2016-04-18 ticket https://www.sqlite.org/src/tktview/7d7525cb01b68712495d3a 608 # Be sure to escape quoted typenames. 609 # 610 do_execsql_test intpkey-16.0 { 611 CREATE TABLE t16a(id "INTEGER" PRIMARY KEY AUTOINCREMENT, b [TEXT], c `INT`); 612 } {} 613 do_execsql_test intpkey-16.1 { 614 PRAGMA table_info=t16a; 615 } {0 id INTEGER 0 {} 1 1 b TEXT 0 {} 0 2 c INT 0 {} 0} 616 617 # 2016-05-06 ticket https://www.sqlite.org/src/tktview/16c9801ceba4923939085 618 # When the schema contains an index on the IPK and no other index 619 # and a WHERE clause on a delete uses an OR where both sides referencing 620 # the IPK, then it is possible that the OP_Delete will fail because there 621 # deferred seek of the OP_Seek is not resolved prior to reaching the OP_Delete. 622 # 623 do_execsql_test intpkey-17.0 { 624 CREATE TABLE t17(x INTEGER PRIMARY KEY, y TEXT); 625 INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe'); 626 CREATE INDEX t17x ON t17(x); 627 DELETE FROM t17 WHERE x=99 OR x<130; 628 SELECT * FROM t17; 629 } {248 giraffe} 630 do_execsql_test intpkey-17.1 { 631 DROP INDEX t17x; 632 DELETE FROM t17; 633 INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe'); 634 CREATE UNIQUE INDEX t17x ON t17(abs(x)); 635 DELETE FROM t17 WHERE abs(x) IS NULL OR abs(x)<130; 636 SELECT * FROM t17; 637 } {248 giraffe} 638 do_execsql_test intpkey-17.2 { 639 DELETE FROM t17; 640 INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe'); 641 UPDATE t17 SET y='ostrich' WHERE abs(x)=248; 642 SELECT * FROM t17 ORDER BY +x; 643 } {123 elephant 248 ostrich} 644 645 finish_test