test/intpkey.test

   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