src/third_party/sqlite/src/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 {PRIMARY KEY must be unique}}
  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   set sqlite_query_plan
 129 } {t1 *}
 130
 131 # Try to insert a non-integer value into the primary key field.  This
 132 # should result in a data type mismatch.
 133 #
 134 do_test intpkey-1.13.1 {
 135   set r [catch {execsql {
 136     INSERT INTO t1 VALUES('x','y','z');
 137   }} msg]
 138   lappend r $msg
 139 } {1 {datatype mismatch}}
 140 do_test intpkey-1.13.2 {
 141   set r [catch {execsql {
 142     INSERT INTO t1 VALUES('','y','z');
 143   }} msg]
 144   lappend r $msg
 145 } {1 {datatype mismatch}}
 146 do_test intpkey-1.14 {
 147   set r [catch {execsql {
 148     INSERT INTO t1 VALUES(3.4,'y','z');
 149   }} msg]
 150   lappend r $msg
 151 } {1 {datatype mismatch}}
 152 do_test intpkey-1.15 {
 153   set r [catch {execsql {
 154     INSERT INTO t1 VALUES(-3,'y','z');
 155   }} msg]
 156   lappend r $msg
 157 } {0 {}}
 158 do_test intpkey-1.16 {
 159   execsql {SELECT * FROM t1}
 160 } {-3 y z 4 one two 5 hello world 6 second entry}
 161
 162 #### INDICES
 163 # Check to make sure indices work correctly with integer primary keys
 164 #
 165 do_test intpkey-2.1 {
 166   execsql {
 167     CREATE INDEX i1 ON t1(b);
 168     SELECT * FROM t1 WHERE b=='y'
 169   }
 170 } {-3 y z}
 171 do_test intpkey-2.1.1 {
 172   execsql {
 173     SELECT * FROM t1 WHERE b=='y' AND rowid<0
 174   }
 175 } {-3 y z}
 176 do_test intpkey-2.1.2 {
 177   execsql {
 178     SELECT * FROM t1 WHERE b=='y' AND rowid<0 AND rowid>=-20
 179   }
 180 } {-3 y z}
 181 do_test intpkey-2.1.3 {
 182   execsql {
 183     SELECT * FROM t1 WHERE b>='y'
 184   }
 185 } {-3 y z}
 186 do_test intpkey-2.1.4 {
 187   execsql {
 188     SELECT * FROM t1 WHERE b>='y' AND rowid<10
 189   }
 190 } {-3 y z}
 191
 192 do_test intpkey-2.2 {
 193   execsql {
 194     UPDATE t1 SET a=8 WHERE b=='y';
 195     SELECT * FROM t1 WHERE b=='y';
 196   }
 197 } {8 y z}
 198 do_test intpkey-2.3 {
 199   execsql {
 200     SELECT rowid, * FROM t1;
 201   }
 202 } {4 4 one two 5 5 hello world 6 6 second entry 8 8 y z}
 203 do_test intpkey-2.4 {
 204   execsql {
 205     SELECT rowid, * FROM t1 WHERE b<'second'
 206   }
 207 } {5 5 hello world 4 4 one two}
 208 do_test intpkey-2.4.1 {
 209   execsql {
 210     SELECT rowid, * FROM t1 WHERE 'second'>b
 211   }
 212 } {5 5 hello world 4 4 one two}
 213 do_test intpkey-2.4.2 {
 214   execsql {
 215     SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b
 216   }
 217 } {4 4 one two 5 5 hello world}
 218 do_test intpkey-2.4.3 {
 219   execsql {
 220     SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid
 221   }
 222 } {4 4 one two 5 5 hello world}
 223 do_test intpkey-2.5 {
 224   execsql {
 225     SELECT rowid, * FROM t1 WHERE b>'a'
 226   }
 227 } {5 5 hello world 4 4 one two 6 6 second entry 8 8 y z}
 228 do_test intpkey-2.6 {
 229   execsql {
 230     DELETE FROM t1 WHERE rowid=4;
 231     SELECT * FROM t1 WHERE b>'a';
 232   }
 233 } {5 hello world 6 second entry 8 y z}
 234 do_test intpkey-2.7 {
 235   execsql {
 236     UPDATE t1 SET a=-4 WHERE rowid=8;
 237     SELECT * FROM t1 WHERE b>'a';
 238   }
 239 } {5 hello world 6 second entry -4 y z}
 240 do_test intpkey-2.7 {
 241   execsql {
 242     SELECT * FROM t1
 243   }
 244 } {-4 y z 5 hello world 6 second entry}
 245
 246 # Do an SQL statement.  Append the search count to the end of the result.
 247 #
 248 proc count sql {
 249   set ::sqlite_search_count 0
 250   return [concat [execsql $sql] $::sqlite_search_count]
 251 }
 252
 253 # Create indices that include the integer primary key as one of their
 254 # columns.
 255 #
 256 do_test intpkey-3.1 {
 257   execsql {
 258     CREATE INDEX i2 ON t1(a);
 259   }
 260 } {}
 261 do_test intpkey-3.2 {
 262   count {
 263     SELECT * FROM t1 WHERE a=5;
 264   }
 265 } {5 hello world 0}
 266 do_test intpkey-3.3 {
 267   count {
 268     SELECT * FROM t1 WHERE a>4 AND a<6;
 269   }
 270 } {5 hello world 2}
 271 do_test intpkey-3.4 {
 272   count {
 273     SELECT * FROM t1 WHERE b>='hello' AND b<'hello2';
 274   }
 275 } {5 hello world 3}
 276 do_test intpkey-3.5 {
 277   execsql {
 278     CREATE INDEX i3 ON t1(c,a);
 279   }
 280 } {}
 281 do_test intpkey-3.6 {
 282   count {
 283     SELECT * FROM t1 WHERE c=='world';
 284   }
 285 } {5 hello world 3}
 286 do_test intpkey-3.7 {
 287   execsql {INSERT INTO t1 VALUES(11,'hello','world')}
 288   count {
 289     SELECT * FROM t1 WHERE c=='world';
 290   }
 291 } {5 hello world 11 hello world 5}
 292 do_test intpkey-3.8 {
 293   count {
 294     SELECT * FROM t1 WHERE c=='world' AND a>7;
 295   }
 296 } {11 hello world 4}
 297 do_test intpkey-3.9 {
 298   count {
 299     SELECT * FROM t1 WHERE 7<a;
 300   }
 301 } {11 hello world 1}
 302
 303 # Test inequality constraints on integer primary keys and rowids
 304 #
 305 do_test intpkey-4.1 {
 306   count {
 307     SELECT * FROM t1 WHERE 11=rowid
 308   }
 309 } {11 hello world 0}
 310 do_test intpkey-4.2 {
 311   count {
 312     SELECT * FROM t1 WHERE 11=rowid AND b=='hello'
 313   }
 314 } {11 hello world 0}
 315 do_test intpkey-4.3 {
 316   count {
 317     SELECT * FROM t1 WHERE 11=rowid AND b=='hello' AND c IS NOT NULL;
 318   }
 319 } {11 hello world 0}
 320 do_test intpkey-4.4 {
 321   count {
 322     SELECT * FROM t1 WHERE rowid==11
 323   }
 324 } {11 hello world 0}
 325 do_test intpkey-4.5 {
 326   count {
 327     SELECT * FROM t1 WHERE oid==11 AND b=='hello'
 328   }
 329 } {11 hello world 0}
 330 do_test intpkey-4.6 {
 331   count {
 332     SELECT * FROM t1 WHERE a==11 AND b=='hello' AND c IS NOT NULL;
 333   }
 334 } {11 hello world 0}
 335
 336 do_test intpkey-4.7 {
 337   count {
 338     SELECT * FROM t1 WHERE 8<rowid;
 339   }
 340 } {11 hello world 1}
 341 do_test intpkey-4.8 {
 342   count {
 343     SELECT * FROM t1 WHERE 8<rowid AND 11>=oid;
 344   }
 345 } {11 hello world 1}
 346 do_test intpkey-4.9 {
 347   count {
 348     SELECT * FROM t1 WHERE 11<=_rowid_ AND 12>=a;
 349   }
 350 } {11 hello world 1}
 351 do_test intpkey-4.10 {
 352   count {
 353     SELECT * FROM t1 WHERE 0>=_rowid_;
 354   }
 355 } {-4 y z 1}
 356 do_test intpkey-4.11 {
 357   count {
 358     SELECT * FROM t1 WHERE a<0;
 359   }
 360 } {-4 y z 1}
 361 do_test intpkey-4.12 {
 362   count {
 363     SELECT * FROM t1 WHERE a<0 AND a>10;
 364   }
 365 } {1}
 366
 367 # Make sure it is OK to insert a rowid of 0
 368 #
 369 do_test intpkey-5.1 {
 370   execsql {
 371     INSERT INTO t1 VALUES(0,'zero','entry');
 372   }
 373   count {
 374     SELECT * FROM t1 WHERE a=0;
 375   }
 376 } {0 zero entry 0}
 377 do_test intpkey-5.2 {
 378   execsql {
 379     SELECT rowid, a FROM t1
 380   }
 381 } {-4 -4 0 0 5 5 6 6 11 11}
 382
 383 # Test the ability of the COPY command to put data into a
 384 # table that contains an integer primary key.
 385 #
 386 # COPY command has been removed.  But we retain these tests so
 387 # that the tables will contain the right data for tests that follow.
 388 #
 389 do_test intpkey-6.1 {
 390   execsql {
 391     BEGIN;
 392     INSERT INTO t1 VALUES(20,'b-20','c-20');
 393     INSERT INTO t1 VALUES(21,'b-21','c-21');
 394     INSERT INTO t1 VALUES(22,'b-22','c-22');
 395     COMMIT;
 396     SELECT * FROM t1 WHERE a>=20;
 397   }
 398 } {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
 399 do_test intpkey-6.2 {
 400   execsql {
 401     SELECT * FROM t1 WHERE b=='hello'
 402   }
 403 } {5 hello world 11 hello world}
 404 do_test intpkey-6.3 {
 405   execsql {
 406     DELETE FROM t1 WHERE b='b-21';
 407     SELECT * FROM t1 WHERE b=='b-21';
 408   }
 409 } {}
 410 do_test intpkey-6.4 {
 411   execsql {
 412     SELECT * FROM t1 WHERE a>=20
 413   }
 414 } {20 b-20 c-20 22 b-22 c-22}
 415
 416 # Do an insert of values with the columns specified out of order.
 417 #
 418 do_test intpkey-7.1 {
 419   execsql {
 420     INSERT INTO t1(c,b,a) VALUES('row','new',30);
 421     SELECT * FROM t1 WHERE rowid>=30;
 422   }
 423 } {30 new row}
 424 do_test intpkey-7.2 {
 425   execsql {
 426     SELECT * FROM t1 WHERE rowid>20;
 427   }
 428 } {22 b-22 c-22 30 new row}
 429
 430 # Do an insert from a select statement.
 431 #
 432 do_test intpkey-8.1 {
 433   execsql {
 434     CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
 435     INSERT INTO t2 SELECT * FROM t1;
 436     SELECT rowid FROM t2;
 437   }
 438 } {-4 0 5 6 11 20 22 30}
 439 do_test intpkey-8.2 {
 440   execsql {
 441     SELECT x FROM t2;
 442   }
 443 } {-4 0 5 6 11 20 22 30}
 444
 445 do_test intpkey-9.1 {
 446   execsql {
 447     UPDATE t1 SET c='www' WHERE c='world';
 448     SELECT rowid, a, c FROM t1 WHERE c=='www';
 449   }
 450 } {5 5 www 11 11 www}
 451
 452
 453 # Check insert of NULL for primary key
 454 #
 455 do_test intpkey-10.1 {
 456   execsql {
 457     DROP TABLE t2;
 458     CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
 459     INSERT INTO t2 VALUES(NULL, 1, 2);
 460     SELECT * from t2;
 461   }
 462 } {1 1 2}
 463 do_test intpkey-10.2 {
 464   execsql {
 465     INSERT INTO t2 VALUES(NULL, 2, 3);
 466     SELECT * from t2 WHERE x=2;
 467   }
 468 } {2 2 3}
 469 do_test intpkey-10.3 {
 470   execsql {
 471     INSERT INTO t2 SELECT NULL, z, y FROM t2;
 472     SELECT * FROM t2;
 473   }
 474 } {1 1 2 2 2 3 3 2 1 4 3 2}
 475
 476 # This tests checks to see if a floating point number can be used
 477 # to reference an integer primary key.
 478 #
 479 do_test intpkey-11.1 {
 480   execsql {
 481     SELECT b FROM t1 WHERE a=2.0+3.0;
 482   }
 483 } {hello}
 484 do_test intpkey-11.1 {
 485   execsql {
 486     SELECT b FROM t1 WHERE a=2.0+3.5;
 487   }
 488 } {}
 489
 490 integrity_check intpkey-12.1
 491
 492 # Try to use a string that looks like a floating point number as
 493 # an integer primary key.  This should actually work when the floating
 494 # point value can be rounded to an integer without loss of data.
 495 #
 496 do_test intpkey-13.1 {
 497   execsql {
 498     SELECT * FROM t1 WHERE a=1;
 499   }
 500 } {}
 501 do_test intpkey-13.2 {
 502   execsql {
 503     INSERT INTO t1 VALUES('1.0',2,3);
 504     SELECT * FROM t1 WHERE a=1;
 505   }
 506 } {1 2 3}
 507 do_test intpkey-13.3 {
 508   catchsql {
 509     INSERT INTO t1 VALUES('1.5',3,4);
 510   }
 511 } {1 {datatype mismatch}}
 512 ifcapable {bloblit} {
 513   do_test intpkey-13.4 {
 514     catchsql {
 515       INSERT INTO t1 VALUES(x'123456',3,4);
 516     }
 517   } {1 {datatype mismatch}}
 518 }
 519 do_test intpkey-13.5 {
 520   catchsql {
 521     INSERT INTO t1 VALUES('+1234567890',3,4);
 522   }
 523 } {0 {}}
 524
 525 # Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER
 526 # affinity should be applied to the text value before the comparison
 527 # takes place.
 528 #
 529 do_test intpkey-14.1 {
 530   execsql {
 531     CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT);
 532     INSERT INTO t3 VALUES(1, 1, 'one');
 533     INSERT INTO t3 VALUES(2, 2, '2');
 534     INSERT INTO t3 VALUES(3, 3, 3);
 535   }
 536 } {}
 537 do_test intpkey-14.2 {
 538   execsql {
 539     SELECT * FROM t3 WHERE a>2;
 540   }
 541 } {3 3 3}
 542 do_test intpkey-14.3 {
 543   execsql {
 544     SELECT * FROM t3 WHERE a>'2';
 545   }
 546 } {3 3 3}
 547 do_test intpkey-14.4 {
 548   execsql {
 549     SELECT * FROM t3 WHERE a<'2';
 550   }
 551 } {1 1 one}
 552 do_test intpkey-14.5 {
 553   execsql {
 554     SELECT * FROM t3 WHERE a<c;
 555   }
 556 } {1 1 one}
 557 do_test intpkey-14.6 {
 558   execsql {
 559     SELECT * FROM t3 WHERE a=c;
 560   }
 561 } {2 2 2 3 3 3}
 562
 563 # Check for proper handling of primary keys greater than 2^31.
 564 # Ticket #1188
 565 #
 566 do_test intpkey-15.1 {
 567   execsql {
 568     INSERT INTO t1 VALUES(2147483647, 'big-1', 123);
 569     SELECT * FROM t1 WHERE a>2147483648;
 570   }
 571 } {}
 572 do_test intpkey-15.2 {
 573   execsql {
 574     INSERT INTO t1 VALUES(NULL, 'big-2', 234);
 575     SELECT b FROM t1 WHERE a>=2147483648;
 576   }
 577 } {big-2}
 578 do_test intpkey-15.3 {
 579   execsql {
 580     SELECT b FROM t1 WHERE a>2147483648;
 581   }
 582 } {}
 583 do_test intpkey-15.4 {
 584   execsql {
 585     SELECT b FROM t1 WHERE a>=2147483647;
 586   }
 587 } {big-1 big-2}
 588 do_test intpkey-15.5 {
 589   execsql {
 590     SELECT b FROM t1 WHERE a<2147483648;
 591   }
 592 } {y zero 2 hello second hello b-20 b-22 new 3 big-1}
 593 do_test intpkey-15.6 {
 594   execsql {
 595     SELECT b FROM t1 WHERE a<12345678901;
 596   }
 597 } {y zero 2 hello second hello b-20 b-22 new 3 big-1 big-2}
 598 do_test intpkey-15.7 {
 599   execsql {
 600     SELECT b FROM t1 WHERE a>12345678901;
 601   }
 602 } {}
 603
 604
 605 finish_test