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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to intpkey.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test