Annotation of embedaddon/sqlite3/test/rowid.test, revision 1.1.1.1
1.1 misho 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. The
12: # focus of this file is testing the magic ROWID column that is
13: # found on all tables.
14: #
15: # $Id: rowid.test,v 1.21 2009/06/26 15:14:55 drh Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: # Basic ROWID functionality tests.
21: #
22: do_test rowid-1.1 {
23: execsql {
24: CREATE TABLE t1(x int, y int);
25: INSERT INTO t1 VALUES(1,2);
26: INSERT INTO t1 VALUES(3,4);
27: SELECT x FROM t1 ORDER BY y;
28: }
29: } {1 3}
30: do_test rowid-1.2 {
31: set r [execsql {SELECT rowid FROM t1 ORDER BY x}]
32: global x2rowid rowid2x
33: set x2rowid(1) [lindex $r 0]
34: set x2rowid(3) [lindex $r 1]
35: set rowid2x($x2rowid(1)) 1
36: set rowid2x($x2rowid(3)) 3
37: llength $r
38: } {2}
39: do_test rowid-1.3 {
40: global x2rowid
41: set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"
42: execsql $sql
43: } {1}
44: do_test rowid-1.4 {
45: global x2rowid
46: set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"
47: execsql $sql
48: } {3}
49: do_test rowid-1.5 {
50: global x2rowid
51: set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)"
52: execsql $sql
53: } {1}
54: do_test rowid-1.6 {
55: global x2rowid
56: set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)"
57: execsql $sql
58: } {3}
59: do_test rowid-1.7 {
60: global x2rowid
61: set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)"
62: execsql $sql
63: } {1}
64: do_test rowid-1.7.1 {
65: while 1 {
66: set norow [expr {int(rand()*1000000)}]
67: if {$norow!=$x2rowid(1) && $norow!=$x2rowid(3)} break
68: }
69: execsql "SELECT x FROM t1 WHERE rowid=$norow"
70: } {}
71: do_test rowid-1.8 {
72: global x2rowid
73: set v [execsql {SELECT x, oid FROM t1 order by x}]
74: set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
75: expr {$v==$v2}
76: } {1}
77: do_test rowid-1.9 {
78: global x2rowid
79: set v [execsql {SELECT x, RowID FROM t1 order by x}]
80: set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
81: expr {$v==$v2}
82: } {1}
83: do_test rowid-1.10 {
84: global x2rowid
85: set v [execsql {SELECT x, _rowid_ FROM t1 order by x}]
86: set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
87: expr {$v==$v2}
88: } {1}
89:
90: # We can insert or update the ROWID column.
91: #
92: do_test rowid-2.1 {
93: catchsql {
94: INSERT INTO t1(rowid,x,y) VALUES(1234,5,6);
95: SELECT rowid, * FROM t1;
96: }
97: } {0 {1 1 2 2 3 4 1234 5 6}}
98: do_test rowid-2.2 {
99: catchsql {
100: UPDATE t1 SET rowid=12345 WHERE x==1;
101: SELECT rowid, * FROM t1
102: }
103: } {0 {2 3 4 1234 5 6 12345 1 2}}
104: do_test rowid-2.3 {
105: catchsql {
106: INSERT INTO t1(y,x,oid) VALUES(8,7,1235);
107: SELECT rowid, * FROM t1 WHERE rowid>1000;
108: }
109: } {0 {1234 5 6 1235 7 8 12345 1 2}}
110: do_test rowid-2.4 {
111: catchsql {
112: UPDATE t1 SET oid=12346 WHERE x==1;
113: SELECT rowid, * FROM t1;
114: }
115: } {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}}
116: do_test rowid-2.5 {
117: catchsql {
118: INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10);
119: SELECT rowid, * FROM t1 WHERE rowid>1000;
120: }
121: } {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}}
122: do_test rowid-2.6 {
123: catchsql {
124: UPDATE t1 SET _rowid_=12347 WHERE x==1;
125: SELECT rowid, * FROM t1 WHERE rowid>1000;
126: }
127: } {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}}
128:
129: # But we can use ROWID in the WHERE clause of an UPDATE that does not
130: # change the ROWID.
131: #
132: do_test rowid-2.7 {
133: global x2rowid
134: set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)"
135: execsql $sql
136: execsql {SELECT x FROM t1 ORDER BY x}
137: } {1 2 5 7 9}
138: do_test rowid-2.8 {
139: global x2rowid
140: set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"
141: execsql $sql
142: execsql {SELECT x FROM t1 ORDER BY x}
143: } {1 3 5 7 9}
144:
145: # We cannot index by ROWID
146: #
147: do_test rowid-2.9 {
148: set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]
149: lappend v $msg
150: } {1 {table t1 has no column named rowid}}
151: do_test rowid-2.10 {
152: set v [catch {execsql {CREATE INDEX idxt1 ON t1(_rowid_)}} msg]
153: lappend v $msg
154: } {1 {table t1 has no column named _rowid_}}
155: do_test rowid-2.11 {
156: set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]
157: lappend v $msg
158: } {1 {table t1 has no column named oid}}
159: do_test rowid-2.12 {
160: set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]
161: lappend v $msg
162: } {1 {table t1 has no column named rowid}}
163:
164: # Columns defined in the CREATE statement override the buildin ROWID
165: # column names.
166: #
167: do_test rowid-3.1 {
168: execsql {
169: CREATE TABLE t2(rowid int, x int, y int);
170: INSERT INTO t2 VALUES(0,2,3);
171: INSERT INTO t2 VALUES(4,5,6);
172: INSERT INTO t2 VALUES(7,8,9);
173: SELECT * FROM t2 ORDER BY x;
174: }
175: } {0 2 3 4 5 6 7 8 9}
176: do_test rowid-3.2 {
177: execsql {SELECT * FROM t2 ORDER BY rowid}
178: } {0 2 3 4 5 6 7 8 9}
179: do_test rowid-3.3 {
180: execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
181: } {0 2 3 4 5 6 7 8 9}
182: do_test rowid-3.4 {
183: set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
184: foreach {a b c d e f} $r1 {}
185: set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
186: foreach {u v w x y z} $r2 {}
187: expr {$u==$e && $w==$c && $y==$a}
188: } {1}
189: # sqlite3 v3 - do_probtest doesn't exist anymore?
190: if 0 {
191: do_probtest rowid-3.5 {
192: set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
193: foreach {a b c d e f} $r1 {}
194: expr {$a!=$b && $c!=$d && $e!=$f}
195: } {1}
196: }
197:
198: # Let's try some more complex examples, including some joins.
199: #
200: do_test rowid-4.1 {
201: execsql {
202: DELETE FROM t1;
203: DELETE FROM t2;
204: }
205: for {set i 1} {$i<=50} {incr i} {
206: execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])"
207: }
208: execsql {INSERT INTO t2 SELECT _rowid_, x*y, y*y FROM t1}
209: execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
210: } {256}
211: do_test rowid-4.2 {
212: execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
213: } {256}
214: do_test rowid-4.2.1 {
215: execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid}
216: } {256}
217: do_test rowid-4.2.2 {
218: execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
219: } {256}
220: do_test rowid-4.2.3 {
221: execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid}
222: } {256}
223: do_test rowid-4.2.4 {
224: execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4}
225: } {256}
226: do_test rowid-4.2.5 {
227: execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
228: } {256}
229: do_test rowid-4.2.6 {
230: execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid}
231: } {256}
232: do_test rowid-4.2.7 {
233: execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4}
234: } {256}
235: do_test rowid-4.3 {
236: execsql {CREATE INDEX idxt1 ON t1(x)}
237: execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
238: } {256}
239: do_test rowid-4.3.1 {
240: execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
241: } {256}
242: do_test rowid-4.3.2 {
243: execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x}
244: } {256}
245: do_test rowid-4.4 {
246: execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
247: } {256}
248: do_test rowid-4.4.1 {
249: execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
250: } {256}
251: do_test rowid-4.4.2 {
252: execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}
253: } {256}
254: do_test rowid-4.5 {
255: execsql {CREATE INDEX idxt2 ON t2(y)}
256: set sqlite_search_count 0
257: concat [execsql {
258: SELECT t1.x FROM t2, t1
259: WHERE t2.y==256 AND t1.rowid==t2.rowid
260: }] $sqlite_search_count
261: } {4 3}
262: do_test rowid-4.5.1 {
263: set sqlite_search_count 0
264: concat [execsql {
265: SELECT t1.x FROM t2, t1
266: WHERE t1.OID==t2.rowid AND t2.y==81
267: }] $sqlite_search_count
268: } {3 3}
269: do_test rowid-4.6 {
270: execsql {
271: SELECT t1.x FROM t1, t2
272: WHERE t2.y==256 AND t1.rowid==t2.rowid
273: }
274: } {4}
275:
276: do_test rowid-5.1.1 {
277: ifcapable subquery {
278: execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
279: } else {
280: set oids [execsql {SELECT oid FROM t1 WHERE x>8}]
281: set where "_rowid_ = [join $oids { OR _rowid_ = }]"
282: execsql "DELETE FROM t1 WHERE $where"
283: }
284: } {}
285: do_test rowid-5.1.2 {
286: execsql {SELECT max(x) FROM t1}
287: } {8}
288:
289: # Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.
290: #
291: do_test rowid-6.1 {
292: execsql {
293: SELECT x FROM t1
294: }
295: } {1 2 3 4 5 6 7 8}
296: do_test rowid-6.2 {
297: for {set ::norow 1} {1} {incr ::norow} {
298: if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""} break
299: }
300: execsql [subst {
301: DELETE FROM t1 WHERE rowid=$::norow
302: }]
303: } {}
304: do_test rowid-6.3 {
305: execsql {
306: SELECT x FROM t1
307: }
308: } {1 2 3 4 5 6 7 8}
309:
310: # Beginning with version 2.3.4, SQLite computes rowids of new rows by
311: # finding the maximum current rowid and adding one. It falls back to
312: # the old random algorithm if the maximum rowid is the largest integer.
313: # The following tests are for this new behavior.
314: #
315: do_test rowid-7.0 {
316: execsql {
317: DELETE FROM t1;
318: DROP TABLE t2;
319: DROP INDEX idxt1;
320: INSERT INTO t1 VALUES(1,2);
321: SELECT rowid, * FROM t1;
322: }
323: } {1 1 2}
324: do_test rowid-7.1 {
325: execsql {
326: INSERT INTO t1 VALUES(99,100);
327: SELECT rowid,* FROM t1
328: }
329: } {1 1 2 2 99 100}
330: do_test rowid-7.2 {
331: execsql {
332: CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
333: INSERT INTO t2(b) VALUES(55);
334: SELECT * FROM t2;
335: }
336: } {1 55}
337: do_test rowid-7.3 {
338: execsql {
339: INSERT INTO t2(b) VALUES(66);
340: SELECT * FROM t2;
341: }
342: } {1 55 2 66}
343: do_test rowid-7.4 {
344: execsql {
345: INSERT INTO t2(a,b) VALUES(1000000,77);
346: INSERT INTO t2(b) VALUES(88);
347: SELECT * FROM t2;
348: }
349: } {1 55 2 66 1000000 77 1000001 88}
350: do_test rowid-7.5 {
351: execsql {
352: INSERT INTO t2(a,b) VALUES(2147483647,99);
353: INSERT INTO t2(b) VALUES(11);
354: SELECT b FROM t2 ORDER BY b;
355: }
356: } {11 55 66 77 88 99}
357: ifcapable subquery {
358: do_test rowid-7.6 {
359: execsql {
360: SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
361: }
362: } {11}
363: do_test rowid-7.7 {
364: execsql {
365: INSERT INTO t2(b) VALUES(22);
366: INSERT INTO t2(b) VALUES(33);
367: INSERT INTO t2(b) VALUES(44);
368: INSERT INTO t2(b) VALUES(55);
369: SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647)
370: ORDER BY b;
371: }
372: } {11 22 33 44 55}
373: }
374: do_test rowid-7.8 {
375: execsql {
376: DELETE FROM t2 WHERE a!=2;
377: INSERT INTO t2(b) VALUES(111);
378: SELECT * FROM t2;
379: }
380: } {2 66 3 111}
381:
382: ifcapable {trigger} {
383: # Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.
384: # Ticket #290
385: #
386: do_test rowid-8.1 {
387: execsql {
388: CREATE TABLE t3(a integer primary key);
389: CREATE TABLE t4(x);
390: INSERT INTO t4 VALUES(1);
391: CREATE TRIGGER r3 AFTER INSERT on t3 FOR EACH ROW BEGIN
392: INSERT INTO t4 VALUES(NEW.a+10);
393: END;
394: SELECT * FROM t3;
395: }
396: } {}
397: do_test rowid-8.2 {
398: execsql {
399: SELECT rowid, * FROM t4;
400: }
401: } {1 1}
402: do_test rowid-8.3 {
403: execsql {
404: INSERT INTO t3 VALUES(123);
405: SELECT last_insert_rowid();
406: }
407: } {123}
408: do_test rowid-8.4 {
409: execsql {
410: SELECT * FROM t3;
411: }
412: } {123}
413: do_test rowid-8.5 {
414: execsql {
415: SELECT rowid, * FROM t4;
416: }
417: } {1 1 2 133}
418: do_test rowid-8.6 {
419: execsql {
420: INSERT INTO t3 VALUES(NULL);
421: SELECT last_insert_rowid();
422: }
423: } {124}
424: do_test rowid-8.7 {
425: execsql {
426: SELECT * FROM t3;
427: }
428: } {123 124}
429: do_test rowid-8.8 {
430: execsql {
431: SELECT rowid, * FROM t4;
432: }
433: } {1 1 2 133 3 134}
434: } ;# endif trigger
435:
436: # If triggers are not enable, simulate their effect for the tests that
437: # follow.
438: ifcapable {!trigger} {
439: execsql {
440: CREATE TABLE t3(a integer primary key);
441: INSERT INTO t3 VALUES(123);
442: INSERT INTO t3 VALUES(124);
443: }
444: }
445:
446: # ticket #377: Comparison between integer primiary key and floating point
447: # values.
448: #
449: do_test rowid-9.1 {
450: execsql {
451: SELECT * FROM t3 WHERE a<123.5
452: }
453: } {123}
454: do_test rowid-9.2 {
455: execsql {
456: SELECT * FROM t3 WHERE a<124.5
457: }
458: } {123 124}
459: do_test rowid-9.3 {
460: execsql {
461: SELECT * FROM t3 WHERE a>123.5
462: }
463: } {124}
464: do_test rowid-9.4 {
465: execsql {
466: SELECT * FROM t3 WHERE a>122.5
467: }
468: } {123 124}
469: do_test rowid-9.5 {
470: execsql {
471: SELECT * FROM t3 WHERE a==123.5
472: }
473: } {}
474: do_test rowid-9.6 {
475: execsql {
476: SELECT * FROM t3 WHERE a==123.000
477: }
478: } {123}
479: do_test rowid-9.7 {
480: execsql {
481: SELECT * FROM t3 WHERE a>100.5 AND a<200.5
482: }
483: } {123 124}
484: do_test rowid-9.8 {
485: execsql {
486: SELECT * FROM t3 WHERE a>'xyz';
487: }
488: } {}
489: do_test rowid-9.9 {
490: execsql {
491: SELECT * FROM t3 WHERE a<'xyz';
492: }
493: } {123 124}
494: do_test rowid-9.10 {
495: execsql {
496: SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1
497: }
498: } {123}
499:
500: # Ticket #567. Comparisons of ROWID or integery primary key against
501: # floating point numbers still do not always work.
502: #
503: do_test rowid-10.1 {
504: execsql {
505: CREATE TABLE t5(a);
506: INSERT INTO t5 VALUES(1);
507: INSERT INTO t5 VALUES(2);
508: INSERT INTO t5 SELECT a+2 FROM t5;
509: INSERT INTO t5 SELECT a+4 FROM t5;
510: SELECT rowid, * FROM t5;
511: }
512: } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
513: do_test rowid-10.2 {
514: execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}
515: } {6 6 7 7 8 8}
516: do_test rowid-10.3 {
517: execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}
518: } {5 5 6 6 7 7 8 8}
519: do_test rowid-10.4 {
520: execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}
521: } {6 6 7 7 8 8}
522: do_test rowid-10.3.2 {
523: execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}
524: } {6 6 7 7 8 8}
525: do_test rowid-10.5 {
526: execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}
527: } {6 6 7 7 8 8}
528: do_test rowid-10.6 {
529: execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}
530: } {6 6 7 7 8 8}
531: do_test rowid-10.7 {
532: execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}
533: } {1 1 2 2 3 3 4 4 5 5}
534: do_test rowid-10.8 {
535: execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}
536: } {1 1 2 2 3 3 4 4 5 5}
537: do_test rowid-10.9 {
538: execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}
539: } {1 1 2 2 3 3 4 4 5 5}
540: do_test rowid-10.10 {
541: execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}
542: } {1 1 2 2 3 3 4 4 5 5}
543: do_test rowid-10.11 {
544: execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}
545: } {8 8 7 7 6 6}
546: do_test rowid-10.11.2 {
547: execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}
548: } {8 8 7 7 6 6 5 5}
549: do_test rowid-10.12 {
550: execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}
551: } {8 8 7 7 6 6}
552: do_test rowid-10.12.2 {
553: execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}
554: } {8 8 7 7 6 6}
555: do_test rowid-10.13 {
556: execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}
557: } {8 8 7 7 6 6}
558: do_test rowid-10.14 {
559: execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}
560: } {8 8 7 7 6 6}
561: do_test rowid-10.15 {
562: execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}
563: } {5 5 4 4 3 3 2 2 1 1}
564: do_test rowid-10.16 {
565: execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}
566: } {5 5 4 4 3 3 2 2 1 1}
567: do_test rowid-10.17 {
568: execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}
569: } {5 5 4 4 3 3 2 2 1 1}
570: do_test rowid-10.18 {
571: execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}
572: } {5 5 4 4 3 3 2 2 1 1}
573:
574: do_test rowid-10.30 {
575: execsql {
576: CREATE TABLE t6(a);
577: INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;
578: SELECT rowid, * FROM t6;
579: }
580: } {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1}
581: do_test rowid-10.31.1 {
582: execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}
583: } {-5 5 -4 4 -3 3 -2 2 -1 1}
584: do_test rowid-10.31.2 {
585: execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}
586: } {-5 5 -4 4 -3 3 -2 2 -1 1}
587: do_test rowid-10.32.1 {
588: execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}
589: } {-1 1 -2 2 -3 3 -4 4 -5 5}
590: do_test rowid-10.32.1 {
591: execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}
592: } {-1 1 -2 2 -3 3 -4 4 -5 5}
593: do_test rowid-10.33 {
594: execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}
595: } {-5 5 -4 4 -3 3 -2 2 -1 1}
596: do_test rowid-10.34 {
597: execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}
598: } {-1 1 -2 2 -3 3 -4 4 -5 5}
599: do_test rowid-10.35.1 {
600: execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}
601: } {-5 5 -4 4 -3 3 -2 2 -1 1}
602: do_test rowid-10.35.2 {
603: execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}
604: } {-4 4 -3 3 -2 2 -1 1}
605: do_test rowid-10.36.1 {
606: execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}
607: } {-1 1 -2 2 -3 3 -4 4 -5 5}
608: do_test rowid-10.36.2 {
609: execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}
610: } {-1 1 -2 2 -3 3 -4 4}
611: do_test rowid-10.37 {
612: execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}
613: } {-5 5 -4 4 -3 3 -2 2 -1 1}
614: do_test rowid-10.38 {
615: execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}
616: } {-1 1 -2 2 -3 3 -4 4 -5 5}
617: do_test rowid-10.39 {
618: execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}
619: } {-8 8 -7 7 -6 6}
620: do_test rowid-10.40 {
621: execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}
622: } {-6 6 -7 7 -8 8}
623: do_test rowid-10.41 {
624: execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}
625: } {-8 8 -7 7 -6 6}
626: do_test rowid-10.42 {
627: execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}
628: } {-6 6 -7 7 -8 8}
629: do_test rowid-10.43 {
630: execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}
631: } {-8 8 -7 7 -6 6}
632: do_test rowid-10.44 {
633: execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}
634: } {-6 6 -7 7 -8 8}
635: do_test rowid-10.44 {
636: execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}
637: } {-8 8 -7 7 -6 6}
638: do_test rowid-10.46 {
639: execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}
640: } {-6 6 -7 7 -8 8}
641:
642: # Comparison of rowid against string values.
643: #
644: do_test rowid-11.1 {
645: execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}
646: } {}
647: do_test rowid-11.2 {
648: execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}
649: } {}
650: do_test rowid-11.3 {
651: execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}
652: } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
653: do_test rowid-11.4 {
654: execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}
655: } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
656:
657: # Test the automatic generation of rowids when the table already contains
658: # a rowid with the maximum value.
659: #
660: # Once the the maximum rowid is taken, rowids are normally chosen at
661: # random. By by reseting the random number generator, we can cause
662: # the rowid guessing loop to collide with prior rowids, and test the
663: # loop out to its limit of 100 iterations. After 100 collisions, the
664: # rowid guesser gives up and reports SQLITE_FULL.
665: #
666: do_test rowid-12.1 {
667: execsql {
668: CREATE TABLE t7(x INTEGER PRIMARY KEY, y);
669: CREATE TABLE t7temp(a INTEGER PRIMARY KEY);
670: INSERT INTO t7 VALUES(9223372036854775807,'a');
671: SELECT y FROM t7;
672: }
673: } {a}
674: do_test rowid-12.2 {
675: db close
676: sqlite3 db test.db
677: save_prng_state
678: execsql {
679: INSERT INTO t7 VALUES(NULL,'b');
680: SELECT x, y FROM t7;
681: }
682: } {1 b 9223372036854775807 a}
683: execsql {INSERT INTO t7 VALUES(2,'y');}
684: for {set i 1} {$i<100} {incr i} {
685: do_test rowid-12.3.$i {
686: db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);}
687: restore_prng_state
688: execsql {
689: INSERT INTO t7 VALUES(NULL,'x');
690: SELECT count(*) FROM t7 WHERE y=='x';
691: }
692: } $i
693: }
694: do_test rowid-12.4 {
695: db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);}
696: restore_prng_state
697: catchsql {
698: INSERT INTO t7 VALUES(NULL,'x');
699: }
700: } {1 {database or disk is full}}
701:
702:
703: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to rowid.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