3
# The author disclaims copyright to this source code. In place of
4
# a legal notice, here is a blessing:
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.
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.
15
# $Id: rowid.test,v 1.20 2008/01/19 20:11:26 drh Exp $
17
set testdir [file dirname $argv0]
18
source $testdir/tester.tcl
20
# Basic ROWID functionality tests.
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;
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
41
set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"
46
set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"
51
set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)"
56
set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)"
61
set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)"
66
set norow [expr {int(rand()*1000000)}]
67
if {$norow!=$x2rowid(1) && $norow!=$x2rowid(3)} break
69
execsql "SELECT x FROM t1 WHERE rowid=$norow"
73
set v [execsql {SELECT x, oid FROM t1 order by x}]
74
set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
79
set v [execsql {SELECT x, RowID FROM t1 order by x}]
80
set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
85
set v [execsql {SELECT x, _rowid_ FROM t1 order by x}]
86
set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
90
# We can insert or update the ROWID column.
94
INSERT INTO t1(rowid,x,y) VALUES(1234,5,6);
95
SELECT rowid, * FROM t1;
97
} {0 {1 1 2 2 3 4 1234 5 6}}
100
UPDATE t1 SET rowid=12345 WHERE x==1;
101
SELECT rowid, * FROM t1
103
} {0 {2 3 4 1234 5 6 12345 1 2}}
106
INSERT INTO t1(y,x,oid) VALUES(8,7,1235);
107
SELECT rowid, * FROM t1 WHERE rowid>1000;
109
} {0 {1234 5 6 1235 7 8 12345 1 2}}
112
UPDATE t1 SET oid=12346 WHERE x==1;
113
SELECT rowid, * FROM t1;
115
} {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}}
118
INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10);
119
SELECT rowid, * FROM t1 WHERE rowid>1000;
121
} {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}}
124
UPDATE t1 SET _rowid_=12347 WHERE x==1;
125
SELECT rowid, * FROM t1 WHERE rowid>1000;
127
} {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}}
129
# But we can use ROWID in the WHERE clause of an UPDATE that does not
134
set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)"
136
execsql {SELECT x FROM t1 ORDER BY x}
140
set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"
142
execsql {SELECT x FROM t1 ORDER BY x}
145
# We cannot index by ROWID
148
set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]
150
} {1 {table t1 has no column named rowid}}
152
set v [catch {execsql {CREATE INDEX idxt1 ON t1(_rowid_)}} msg]
154
} {1 {table t1 has no column named _rowid_}}
156
set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]
158
} {1 {table t1 has no column named oid}}
160
set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]
162
} {1 {table t1 has no column named rowid}}
164
# Columns defined in the CREATE statement override the buildin ROWID
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;
175
} {0 2 3 4 5 6 7 8 9}
177
execsql {SELECT * FROM t2 ORDER BY rowid}
178
} {0 2 3 4 5 6 7 8 9}
180
execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
181
} {0 2 3 4 5 6 7 8 9}
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}
189
# sqlite3 v3 - do_probtest doesn't exist anymore?
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}
198
# Let's try some more complex examples, including some joins.
205
for {set i 1} {$i<=50} {incr i} {
206
execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])"
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}
212
execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
214
do_test rowid-4.2.1 {
215
execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid}
217
do_test rowid-4.2.2 {
218
execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
220
do_test rowid-4.2.3 {
221
execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid}
223
do_test rowid-4.2.4 {
224
execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4}
226
do_test rowid-4.2.5 {
227
execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
229
do_test rowid-4.2.6 {
230
execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid}
232
do_test rowid-4.2.7 {
233
execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4}
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}
239
do_test rowid-4.3.1 {
240
execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
242
do_test rowid-4.3.2 {
243
execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x}
246
execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
248
do_test rowid-4.4.1 {
249
execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
251
do_test rowid-4.4.2 {
252
execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}
255
execsql {CREATE INDEX idxt2 ON t2(y)}
256
set sqlite_search_count 0
258
SELECT t1.x FROM t2, t1
259
WHERE t2.y==256 AND t1.rowid==t2.rowid
260
}] $sqlite_search_count
262
do_test rowid-4.5.1 {
263
set sqlite_search_count 0
265
SELECT t1.x FROM t2, t1
266
WHERE t1.OID==t2.rowid AND t2.y==81
267
}] $sqlite_search_count
271
SELECT t1.x FROM t1, t2
272
WHERE t2.y==256 AND t1.rowid==t2.rowid
276
do_test rowid-5.1.1 {
278
execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
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"
285
do_test rowid-5.1.2 {
286
execsql {SELECT max(x) FROM t1}
289
# Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.
297
for {set ::norow 1} {1} {incr ::norow} {
298
if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""} break
301
DELETE FROM t1 WHERE rowid=$::norow
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.
320
INSERT INTO t1 VALUES(1,2);
321
SELECT rowid, * FROM t1;
326
INSERT INTO t1 VALUES(99,100);
327
SELECT rowid,* FROM t1
332
CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
333
INSERT INTO t2(b) VALUES(55);
339
INSERT INTO t2(b) VALUES(66);
345
INSERT INTO t2(a,b) VALUES(1000000,77);
346
INSERT INTO t2(b) VALUES(88);
349
} {1 55 2 66 1000000 77 1000001 88}
352
INSERT INTO t2(a,b) VALUES(2147483647,99);
353
INSERT INTO t2(b) VALUES(11);
354
SELECT b FROM t2 ORDER BY b;
356
} {11 55 66 77 88 99}
360
SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
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)
376
DELETE FROM t2 WHERE a!=2;
377
INSERT INTO t2(b) VALUES(111);
382
ifcapable {trigger} {
383
# Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.
388
CREATE TABLE t3(a integer primary key);
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);
399
SELECT rowid, * FROM t4;
404
INSERT INTO t3 VALUES(123);
405
SELECT last_insert_rowid();
415
SELECT rowid, * FROM t4;
420
INSERT INTO t3 VALUES(NULL);
421
SELECT last_insert_rowid();
431
SELECT rowid, * FROM t4;
436
# If triggers are not enable, simulate their effect for the tests that
438
ifcapable {!trigger} {
440
CREATE TABLE t3(a integer primary key);
441
INSERT INTO t3 VALUES(123);
442
INSERT INTO t3 VALUES(124);
446
# ticket #377: Comparison between integer primiary key and floating point
451
SELECT * FROM t3 WHERE a<123.5
456
SELECT * FROM t3 WHERE a<124.5
461
SELECT * FROM t3 WHERE a>123.5
466
SELECT * FROM t3 WHERE a>122.5
471
SELECT * FROM t3 WHERE a==123.5
476
SELECT * FROM t3 WHERE a==123.000
481
SELECT * FROM t3 WHERE a>100.5 AND a<200.5
486
SELECT * FROM t3 WHERE a>'xyz';
491
SELECT * FROM t3 WHERE a<'xyz';
496
SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1
500
# Ticket #567. Comparisons of ROWID or integery primary key against
501
# floating point numbers still do not always work.
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;
512
} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
514
execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}
517
execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}
520
execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}
522
do_test rowid-10.3.2 {
523
execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}
526
execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}
529
execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}
532
execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}
533
} {1 1 2 2 3 3 4 4 5 5}
535
execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}
536
} {1 1 2 2 3 3 4 4 5 5}
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}
546
do_test rowid-10.11.2 {
547
execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}
549
do_test rowid-10.12 {
550
execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}
552
do_test rowid-10.12.2 {
553
execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}
555
do_test rowid-10.13 {
556
execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}
558
do_test rowid-10.14 {
559
execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}
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}
574
do_test rowid-10.30 {
577
INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;
578
SELECT rowid, * FROM t6;
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}
620
do_test rowid-10.40 {
621
execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}
623
do_test rowid-10.41 {
624
execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}
626
do_test rowid-10.42 {
627
execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}
629
do_test rowid-10.43 {
630
execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}
632
do_test rowid-10.44 {
633
execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}
635
do_test rowid-10.44 {
636
execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}
638
do_test rowid-10.46 {
639
execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}
642
# Comparison of rowid against string values.
645
execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}
648
execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}
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}
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}
657
# Test the automatic generation of rowids when the table already contains
658
# a rowid with the maximum value.
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.
668
CREATE TABLE t7(x INTEGER PRIMARY KEY, y);
669
INSERT INTO t7 VALUES(9223372036854775807,'a');
678
INSERT INTO t7 VALUES(NULL,'b');
681
} {1 b 9223372036854775807 a}
682
execsql {INSERT INTO t7 VALUES(2,'y');}
683
for {set i 1} {$i<=101} {incr i} {
684
do_test rowid-12.3.$i {
687
INSERT INTO t7 VALUES(NULL,'x');
688
INSERT OR IGNORE INTO t7 VALUES(last_insert_rowid()+1,'y');
689
SELECT count(*) FROM t7 WHERE y=='x';
696
INSERT INTO t7 VALUES(NULL,'x');
698
} {1 {database or disk is full}}