AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# 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 associated14# with INTEGER PRIMARY KEY columns.15#16# $Id: intpkey.test,v 1.24 2007/11/29 17:43:28 danielk1977 Exp $17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21# Create a table with a primary key and a datatype other than22# integer23#24do_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 key31#32do_test intpkey-1.1 {33 execsql {34 SELECT name FROM sqlite_master35 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 that40# there is no associated index.41#42do_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_master47 WHERE type='index' AND tbl_name='t1';48 }49} {}50 51# Insert some records into the new table. Specify the primary key52# and verify that the key is used as the record number.53#54do_test intpkey-1.3 {55 execsql {56 INSERT INTO t1 VALUES(5,'hello','world');57 }58 db last_insert_rowid59} {5}60do_test intpkey-1.4 {61 execsql {62 SELECT * FROM t1;63 }64} {5 hello world}65do_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 constraint72# failure.73#74do_test intpkey-1.6 {75 set r [catch {execsql {76 INSERT INTO t1 VALUES(5,'second','entry');77 }} msg]78 lappend r $msg79} {1 {UNIQUE constraint failed: t1.a}}80do_test intpkey-1.7 {81 execsql {82 SELECT rowid, * FROM t1;83 }84} {5 5 hello world}85do_test intpkey-1.8 {86 set r [catch {execsql {87 INSERT INTO t1 VALUES(6,'second','entry');88 }} msg]89 lappend r $msg90} {0 {}}91do_test intpkey-1.8.1 {92 db last_insert_rowid93} {6}94do_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 specify101# the integer primary key.102#103do_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#112do_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 column120# as an index.121#122do_test intpkey-1.12.1 {123 execsql {124 SELECT * FROM t1 WHERE a==4;125 }126} {4 one two}127do_test intpkey-1.12.2 {128 execsql {129 EXPLAIN QUERY PLAN130 SELECT * FROM t1 WHERE a==4;131 }132} {/SEARCH t1 /}133 134# Try to insert a non-integer value into the primary key field. This135# should result in a data type mismatch.136#137do_test intpkey-1.13.1 {138 set r [catch {execsql {139 INSERT INTO t1 VALUES('x','y','z');140 }} msg]141 lappend r $msg142} {1 {datatype mismatch}}143do_test intpkey-1.13.2 {144 set r [catch {execsql {145 INSERT INTO t1 VALUES('','y','z');146 }} msg]147 lappend r $msg148} {1 {datatype mismatch}}149do_test intpkey-1.14 {150 set r [catch {execsql {151 INSERT INTO t1 VALUES(3.4,'y','z');152 }} msg]153 lappend r $msg154} {1 {datatype mismatch}}155do_test intpkey-1.15 {156 set r [catch {execsql {157 INSERT INTO t1 VALUES(-3,'y','z');158 }} msg]159 lappend r $msg160} {0 {}}161do_test intpkey-1.16 {162 execsql {SELECT * FROM t1}163} {-3 y z 4 one two 5 hello world 6 second entry}164 165#### INDICES166# Check to make sure indices work correctly with integer primary keys167#168do_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}174do_test intpkey-2.1.1 {175 execsql {176 SELECT * FROM t1 WHERE b=='y' AND rowid<0177 }178} {-3 y z}179do_test intpkey-2.1.2 {180 execsql {181 SELECT * FROM t1 WHERE b=='y' AND rowid<0 AND rowid>=-20182 }183} {-3 y z}184do_test intpkey-2.1.3 {185 execsql {186 SELECT * FROM t1 WHERE b>='y'187 }188} {-3 y z}189do_test intpkey-2.1.4 {190 execsql {191 SELECT * FROM t1 WHERE b>='y' AND rowid<10192 }193} {-3 y z}194 195do_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}201do_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}206do_test intpkey-2.4 {207 execsql {208 SELECT rowid, * FROM t1 WHERE b<'second'209 }210} {5 5 hello world 4 4 one two}211do_test intpkey-2.4.1 {212 execsql {213 SELECT rowid, * FROM t1 WHERE 'second'>b214 }215} {5 5 hello world 4 4 one two}216do_test intpkey-2.4.2 {217 execsql {218 SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b219 }220} {4 4 one two 5 5 hello world}221do_test intpkey-2.4.3 {222 execsql {223 SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid224 }225} {4 4 one two 5 5 hello world}226do_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}231do_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}237do_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}243do_test intpkey-2.7 {244 execsql {245 SELECT * FROM t1246 }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#251proc count sql {252 set ::sqlite_search_count 0253 return [concat [execsql $sql] $::sqlite_search_count]254}255 256# Create indices that include the integer primary key as one of their257# columns.258#259do_test intpkey-3.1 {260 execsql {261 CREATE INDEX i2 ON t1(a);262 }263} {}264do_test intpkey-3.2 {265 count {266 SELECT * FROM t1 WHERE a=5;267 }268} {5 hello world 0}269do_test intpkey-3.3 {270 count {271 SELECT * FROM t1 WHERE a>4 AND a<6;272 }273} {5 hello world 2}274do_test intpkey-3.4 {275 count {276 SELECT * FROM t1 WHERE b>='hello' AND b<'hello2';277 }278} {5 hello world 3}279do_test intpkey-3.5 {280 execsql {281 CREATE INDEX i3 ON t1(c,a);282 }283} {}284do_test intpkey-3.6 {285 count {286 SELECT * FROM t1 WHERE c=='world';287 }288} {5 hello world 3}289do_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}295do_test intpkey-3.8 {296 count {297 SELECT * FROM t1 WHERE c=='world' AND a>7;298 }299} {11 hello world 3}300do_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 rowids307#308do_test intpkey-4.1 {309 count {310 SELECT * FROM t1 WHERE 11=rowid311 }312} {11 hello world 0}313do_test intpkey-4.2 {314 count {315 SELECT * FROM t1 WHERE 11=rowid AND b=='hello'316 }317} {11 hello world 0}318do_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}323do_test intpkey-4.4 {324 count {325 SELECT * FROM t1 WHERE rowid==11326 }327} {11 hello world 0}328do_test intpkey-4.5 {329 count {330 SELECT * FROM t1 WHERE oid==11 AND b=='hello'331 }332} {11 hello world 0}333do_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 339do_test intpkey-4.7 {340 count {341 SELECT * FROM t1 WHERE 8<rowid;342 }343} {11 hello world 1}344do_test intpkey-4.8 {345 count {346 SELECT * FROM t1 WHERE 8<rowid AND 11>=oid;347 }348} {11 hello world 1}349do_test intpkey-4.9 {350 count {351 SELECT * FROM t1 WHERE 11<=_rowid_ AND 12>=a;352 }353} {11 hello world 1}354do_test intpkey-4.10 {355 count {356 SELECT * FROM t1 WHERE 0>=_rowid_;357 }358} {-4 y z 1}359do_test intpkey-4.11 {360 count {361 SELECT * FROM t1 WHERE a<0;362 }363} {-4 y z 1}364do_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 0371#372do_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}380do_test intpkey-5.2 {381 execsql {382 SELECT rowid, a FROM t1 ORDER BY rowid383 }384} {-4 -4 0 0 5 5 6 6 11 11}385 386# Test the ability of the COPY command to put data into a387# table that contains an integer primary key.388#389# COPY command has been removed. But we retain these tests so390# that the tables will contain the right data for tests that follow.391#392do_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}402do_test intpkey-6.2 {403 execsql {404 SELECT * FROM t1 WHERE b=='hello'405 }406} {5 hello world 11 hello world}407do_test intpkey-6.3 {408 execsql {409 DELETE FROM t1 WHERE b='b-21';410 SELECT * FROM t1 WHERE b=='b-21';411 }412} {}413do_test intpkey-6.4 {414 execsql {415 SELECT * FROM t1 WHERE a>=20416 }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#421do_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}427do_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#435do_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}442do_test intpkey-8.2 {443 execsql {444 SELECT x FROM t2;445 }446} {-4 0 5 6 11 20 22 30}447 448do_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 key457#458do_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}466do_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}472do_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 used480# to reference an integer primary key.481#482do_test intpkey-11.1 {483 execsql {484 SELECT b FROM t1 WHERE a=2.0+3.0;485 }486} {hello}487do_test intpkey-11.1 {488 execsql {489 SELECT b FROM t1 WHERE a=2.0+3.5;490 }491} {}492 493integrity_check intpkey-12.1494 495# Try to use a string that looks like a floating point number as496# an integer primary key. This should actually work when the floating497# point value can be rounded to an integer without loss of data.498#499do_test intpkey-13.1 {500 execsql {501 SELECT * FROM t1 WHERE a=1;502 }503} {}504do_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}510do_test intpkey-13.3 {511 catchsql {512 INSERT INTO t1 VALUES('1.5',3,4);513 }514} {1 {datatype mismatch}}515ifcapable {bloblit} {516 do_test intpkey-13.4 {517 catchsql {518 INSERT INTO t1 VALUES(x'123456',3,4);519 }520 } {1 {datatype mismatch}}521}522do_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 INTEGER529# affinity should be applied to the text value before the comparison530# takes place.531#532do_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} {}540do_test intpkey-14.2 {541 execsql {542 SELECT * FROM t3 WHERE a>2;543 }544} {3 3 3}545do_test intpkey-14.3 {546 execsql {547 SELECT * FROM t3 WHERE a>'2';548 }549} {3 3 3}550do_test intpkey-14.4 {551 execsql {552 SELECT * FROM t3 WHERE a<'2';553 }554} {1 1 one}555do_test intpkey-14.5 {556 execsql {557 SELECT * FROM t3 WHERE a<c;558 }559} {1 1 one}560do_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 #1188568#569do_test intpkey-15.1 {570 execsql {571 INSERT INTO t1 VALUES(2147483647, 'big-1', 123);572 SELECT * FROM t1 WHERE a>2147483648;573 }574} {}575do_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}581do_test intpkey-15.3 {582 execsql {583 SELECT b FROM t1 WHERE a>2147483648;584 }585} {}586do_test intpkey-15.4 {587 execsql {588 SELECT b FROM t1 WHERE a>=2147483647;589 }590} {big-1 big-2}591do_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}596do_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}601do_test intpkey-15.7 {602 execsql {603 SELECT b FROM t1 WHERE a>12345678901;604 }605} {}606 607# 2016-04-18 ticket https://sqlite.org/src/tktview/7d7525cb01b68712495d3a608# Be sure to escape quoted typenames.609#610do_execsql_test intpkey-16.0 {611 CREATE TABLE t16a(id "INTEGER" PRIMARY KEY AUTOINCREMENT, b [TEXT], c `INT`);612} {}613do_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://sqlite.org/src/tktview/16c9801ceba4923939085618# When the schema contains an index on the IPK and no other index619# and a WHERE clause on a delete uses an OR where both sides referencing620# the IPK, then it is possible that the OP_Delete will fail because there621# deferred seek of the OP_Seek is not resolved prior to reaching the OP_Delete.622#623do_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}630do_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}638do_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 645finish_test646