Debian Codegraph @

test/rowid.test

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