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