1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/rowid.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,701 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the magic ROWID column that is
1.16 +# found on all tables.
1.17 +#
1.18 +# $Id: rowid.test,v 1.20 2008/01/19 20:11:26 drh Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# Basic ROWID functionality tests.
1.24 +#
1.25 +do_test rowid-1.1 {
1.26 + execsql {
1.27 + CREATE TABLE t1(x int, y int);
1.28 + INSERT INTO t1 VALUES(1,2);
1.29 + INSERT INTO t1 VALUES(3,4);
1.30 + SELECT x FROM t1 ORDER BY y;
1.31 + }
1.32 +} {1 3}
1.33 +do_test rowid-1.2 {
1.34 + set r [execsql {SELECT rowid FROM t1 ORDER BY x}]
1.35 + global x2rowid rowid2x
1.36 + set x2rowid(1) [lindex $r 0]
1.37 + set x2rowid(3) [lindex $r 1]
1.38 + set rowid2x($x2rowid(1)) 1
1.39 + set rowid2x($x2rowid(3)) 3
1.40 + llength $r
1.41 +} {2}
1.42 +do_test rowid-1.3 {
1.43 + global x2rowid
1.44 + set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"
1.45 + execsql $sql
1.46 +} {1}
1.47 +do_test rowid-1.4 {
1.48 + global x2rowid
1.49 + set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"
1.50 + execsql $sql
1.51 +} {3}
1.52 +do_test rowid-1.5 {
1.53 + global x2rowid
1.54 + set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)"
1.55 + execsql $sql
1.56 +} {1}
1.57 +do_test rowid-1.6 {
1.58 + global x2rowid
1.59 + set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)"
1.60 + execsql $sql
1.61 +} {3}
1.62 +do_test rowid-1.7 {
1.63 + global x2rowid
1.64 + set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)"
1.65 + execsql $sql
1.66 +} {1}
1.67 +do_test rowid-1.7.1 {
1.68 + while 1 {
1.69 + set norow [expr {int(rand()*1000000)}]
1.70 + if {$norow!=$x2rowid(1) && $norow!=$x2rowid(3)} break
1.71 + }
1.72 + execsql "SELECT x FROM t1 WHERE rowid=$norow"
1.73 +} {}
1.74 +do_test rowid-1.8 {
1.75 + global x2rowid
1.76 + set v [execsql {SELECT x, oid FROM t1 order by x}]
1.77 + set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
1.78 + expr {$v==$v2}
1.79 +} {1}
1.80 +do_test rowid-1.9 {
1.81 + global x2rowid
1.82 + set v [execsql {SELECT x, RowID FROM t1 order by x}]
1.83 + set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
1.84 + expr {$v==$v2}
1.85 +} {1}
1.86 +do_test rowid-1.10 {
1.87 + global x2rowid
1.88 + set v [execsql {SELECT x, _rowid_ FROM t1 order by x}]
1.89 + set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
1.90 + expr {$v==$v2}
1.91 +} {1}
1.92 +
1.93 +# We can insert or update the ROWID column.
1.94 +#
1.95 +do_test rowid-2.1 {
1.96 + catchsql {
1.97 + INSERT INTO t1(rowid,x,y) VALUES(1234,5,6);
1.98 + SELECT rowid, * FROM t1;
1.99 + }
1.100 +} {0 {1 1 2 2 3 4 1234 5 6}}
1.101 +do_test rowid-2.2 {
1.102 + catchsql {
1.103 + UPDATE t1 SET rowid=12345 WHERE x==1;
1.104 + SELECT rowid, * FROM t1
1.105 + }
1.106 +} {0 {2 3 4 1234 5 6 12345 1 2}}
1.107 +do_test rowid-2.3 {
1.108 + catchsql {
1.109 + INSERT INTO t1(y,x,oid) VALUES(8,7,1235);
1.110 + SELECT rowid, * FROM t1 WHERE rowid>1000;
1.111 + }
1.112 +} {0 {1234 5 6 1235 7 8 12345 1 2}}
1.113 +do_test rowid-2.4 {
1.114 + catchsql {
1.115 + UPDATE t1 SET oid=12346 WHERE x==1;
1.116 + SELECT rowid, * FROM t1;
1.117 + }
1.118 +} {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}}
1.119 +do_test rowid-2.5 {
1.120 + catchsql {
1.121 + INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10);
1.122 + SELECT rowid, * FROM t1 WHERE rowid>1000;
1.123 + }
1.124 +} {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}}
1.125 +do_test rowid-2.6 {
1.126 + catchsql {
1.127 + UPDATE t1 SET _rowid_=12347 WHERE x==1;
1.128 + SELECT rowid, * FROM t1 WHERE rowid>1000;
1.129 + }
1.130 +} {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}}
1.131 +
1.132 +# But we can use ROWID in the WHERE clause of an UPDATE that does not
1.133 +# change the ROWID.
1.134 +#
1.135 +do_test rowid-2.7 {
1.136 + global x2rowid
1.137 + set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)"
1.138 + execsql $sql
1.139 + execsql {SELECT x FROM t1 ORDER BY x}
1.140 +} {1 2 5 7 9}
1.141 +do_test rowid-2.8 {
1.142 + global x2rowid
1.143 + set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"
1.144 + execsql $sql
1.145 + execsql {SELECT x FROM t1 ORDER BY x}
1.146 +} {1 3 5 7 9}
1.147 +
1.148 +# We cannot index by ROWID
1.149 +#
1.150 +do_test rowid-2.9 {
1.151 + set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]
1.152 + lappend v $msg
1.153 +} {1 {table t1 has no column named rowid}}
1.154 +do_test rowid-2.10 {
1.155 + set v [catch {execsql {CREATE INDEX idxt1 ON t1(_rowid_)}} msg]
1.156 + lappend v $msg
1.157 +} {1 {table t1 has no column named _rowid_}}
1.158 +do_test rowid-2.11 {
1.159 + set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]
1.160 + lappend v $msg
1.161 +} {1 {table t1 has no column named oid}}
1.162 +do_test rowid-2.12 {
1.163 + set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]
1.164 + lappend v $msg
1.165 +} {1 {table t1 has no column named rowid}}
1.166 +
1.167 +# Columns defined in the CREATE statement override the buildin ROWID
1.168 +# column names.
1.169 +#
1.170 +do_test rowid-3.1 {
1.171 + execsql {
1.172 + CREATE TABLE t2(rowid int, x int, y int);
1.173 + INSERT INTO t2 VALUES(0,2,3);
1.174 + INSERT INTO t2 VALUES(4,5,6);
1.175 + INSERT INTO t2 VALUES(7,8,9);
1.176 + SELECT * FROM t2 ORDER BY x;
1.177 + }
1.178 +} {0 2 3 4 5 6 7 8 9}
1.179 +do_test rowid-3.2 {
1.180 + execsql {SELECT * FROM t2 ORDER BY rowid}
1.181 +} {0 2 3 4 5 6 7 8 9}
1.182 +do_test rowid-3.3 {
1.183 + execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
1.184 +} {0 2 3 4 5 6 7 8 9}
1.185 +do_test rowid-3.4 {
1.186 + set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
1.187 + foreach {a b c d e f} $r1 {}
1.188 + set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
1.189 + foreach {u v w x y z} $r2 {}
1.190 + expr {$u==$e && $w==$c && $y==$a}
1.191 +} {1}
1.192 +# sqlite3 v3 - do_probtest doesn't exist anymore?
1.193 +if 0 {
1.194 +do_probtest rowid-3.5 {
1.195 + set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
1.196 + foreach {a b c d e f} $r1 {}
1.197 + expr {$a!=$b && $c!=$d && $e!=$f}
1.198 +} {1}
1.199 +}
1.200 +
1.201 +# Let's try some more complex examples, including some joins.
1.202 +#
1.203 +do_test rowid-4.1 {
1.204 + execsql {
1.205 + DELETE FROM t1;
1.206 + DELETE FROM t2;
1.207 + }
1.208 + for {set i 1} {$i<=50} {incr i} {
1.209 + execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])"
1.210 + }
1.211 + execsql {INSERT INTO t2 SELECT _rowid_, x*y, y*y FROM t1}
1.212 + execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
1.213 +} {256}
1.214 +do_test rowid-4.2 {
1.215 + execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
1.216 +} {256}
1.217 +do_test rowid-4.2.1 {
1.218 + execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid}
1.219 +} {256}
1.220 +do_test rowid-4.2.2 {
1.221 + execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
1.222 +} {256}
1.223 +do_test rowid-4.2.3 {
1.224 + execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid}
1.225 +} {256}
1.226 +do_test rowid-4.2.4 {
1.227 + execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4}
1.228 +} {256}
1.229 +do_test rowid-4.2.5 {
1.230 + execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
1.231 +} {256}
1.232 +do_test rowid-4.2.6 {
1.233 + execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid}
1.234 +} {256}
1.235 +do_test rowid-4.2.7 {
1.236 + execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4}
1.237 +} {256}
1.238 +do_test rowid-4.3 {
1.239 + execsql {CREATE INDEX idxt1 ON t1(x)}
1.240 + execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
1.241 +} {256}
1.242 +do_test rowid-4.3.1 {
1.243 + execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
1.244 +} {256}
1.245 +do_test rowid-4.3.2 {
1.246 + execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x}
1.247 +} {256}
1.248 +do_test rowid-4.4 {
1.249 + execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
1.250 +} {256}
1.251 +do_test rowid-4.4.1 {
1.252 + execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
1.253 +} {256}
1.254 +do_test rowid-4.4.2 {
1.255 + execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}
1.256 +} {256}
1.257 +do_test rowid-4.5 {
1.258 + execsql {CREATE INDEX idxt2 ON t2(y)}
1.259 + set sqlite_search_count 0
1.260 + concat [execsql {
1.261 + SELECT t1.x FROM t2, t1
1.262 + WHERE t2.y==256 AND t1.rowid==t2.rowid
1.263 + }] $sqlite_search_count
1.264 +} {4 3}
1.265 +do_test rowid-4.5.1 {
1.266 + set sqlite_search_count 0
1.267 + concat [execsql {
1.268 + SELECT t1.x FROM t2, t1
1.269 + WHERE t1.OID==t2.rowid AND t2.y==81
1.270 + }] $sqlite_search_count
1.271 +} {3 3}
1.272 +do_test rowid-4.6 {
1.273 + execsql {
1.274 + SELECT t1.x FROM t1, t2
1.275 + WHERE t2.y==256 AND t1.rowid==t2.rowid
1.276 + }
1.277 +} {4}
1.278 +
1.279 +do_test rowid-5.1.1 {
1.280 + ifcapable subquery {
1.281 + execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
1.282 + } else {
1.283 + set oids [execsql {SELECT oid FROM t1 WHERE x>8}]
1.284 + set where "_rowid_ = [join $oids { OR _rowid_ = }]"
1.285 + execsql "DELETE FROM t1 WHERE $where"
1.286 + }
1.287 +} {}
1.288 +do_test rowid-5.1.2 {
1.289 + execsql {SELECT max(x) FROM t1}
1.290 +} {8}
1.291 +
1.292 +# Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.
1.293 +#
1.294 +do_test rowid-6.1 {
1.295 + execsql {
1.296 + SELECT x FROM t1
1.297 + }
1.298 +} {1 2 3 4 5 6 7 8}
1.299 +do_test rowid-6.2 {
1.300 + for {set ::norow 1} {1} {incr ::norow} {
1.301 + if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""} break
1.302 + }
1.303 + execsql [subst {
1.304 + DELETE FROM t1 WHERE rowid=$::norow
1.305 + }]
1.306 +} {}
1.307 +do_test rowid-6.3 {
1.308 + execsql {
1.309 + SELECT x FROM t1
1.310 + }
1.311 +} {1 2 3 4 5 6 7 8}
1.312 +
1.313 +# Beginning with version 2.3.4, SQLite computes rowids of new rows by
1.314 +# finding the maximum current rowid and adding one. It falls back to
1.315 +# the old random algorithm if the maximum rowid is the largest integer.
1.316 +# The following tests are for this new behavior.
1.317 +#
1.318 +do_test rowid-7.0 {
1.319 + execsql {
1.320 + DELETE FROM t1;
1.321 + DROP TABLE t2;
1.322 + DROP INDEX idxt1;
1.323 + INSERT INTO t1 VALUES(1,2);
1.324 + SELECT rowid, * FROM t1;
1.325 + }
1.326 +} {1 1 2}
1.327 +do_test rowid-7.1 {
1.328 + execsql {
1.329 + INSERT INTO t1 VALUES(99,100);
1.330 + SELECT rowid,* FROM t1
1.331 + }
1.332 +} {1 1 2 2 99 100}
1.333 +do_test rowid-7.2 {
1.334 + execsql {
1.335 + CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
1.336 + INSERT INTO t2(b) VALUES(55);
1.337 + SELECT * FROM t2;
1.338 + }
1.339 +} {1 55}
1.340 +do_test rowid-7.3 {
1.341 + execsql {
1.342 + INSERT INTO t2(b) VALUES(66);
1.343 + SELECT * FROM t2;
1.344 + }
1.345 +} {1 55 2 66}
1.346 +do_test rowid-7.4 {
1.347 + execsql {
1.348 + INSERT INTO t2(a,b) VALUES(1000000,77);
1.349 + INSERT INTO t2(b) VALUES(88);
1.350 + SELECT * FROM t2;
1.351 + }
1.352 +} {1 55 2 66 1000000 77 1000001 88}
1.353 +do_test rowid-7.5 {
1.354 + execsql {
1.355 + INSERT INTO t2(a,b) VALUES(2147483647,99);
1.356 + INSERT INTO t2(b) VALUES(11);
1.357 + SELECT b FROM t2 ORDER BY b;
1.358 + }
1.359 +} {11 55 66 77 88 99}
1.360 +ifcapable subquery {
1.361 + do_test rowid-7.6 {
1.362 + execsql {
1.363 + SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
1.364 + }
1.365 + } {11}
1.366 + do_test rowid-7.7 {
1.367 + execsql {
1.368 + INSERT INTO t2(b) VALUES(22);
1.369 + INSERT INTO t2(b) VALUES(33);
1.370 + INSERT INTO t2(b) VALUES(44);
1.371 + INSERT INTO t2(b) VALUES(55);
1.372 + SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647)
1.373 + ORDER BY b;
1.374 + }
1.375 + } {11 22 33 44 55}
1.376 +}
1.377 +do_test rowid-7.8 {
1.378 + execsql {
1.379 + DELETE FROM t2 WHERE a!=2;
1.380 + INSERT INTO t2(b) VALUES(111);
1.381 + SELECT * FROM t2;
1.382 + }
1.383 +} {2 66 3 111}
1.384 +
1.385 +ifcapable {trigger} {
1.386 +# Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.
1.387 +# Ticket #290
1.388 +#
1.389 +do_test rowid-8.1 {
1.390 + execsql {
1.391 + CREATE TABLE t3(a integer primary key);
1.392 + CREATE TABLE t4(x);
1.393 + INSERT INTO t4 VALUES(1);
1.394 + CREATE TRIGGER r3 AFTER INSERT on t3 FOR EACH ROW BEGIN
1.395 + INSERT INTO t4 VALUES(NEW.a+10);
1.396 + END;
1.397 + SELECT * FROM t3;
1.398 + }
1.399 +} {}
1.400 +do_test rowid-8.2 {
1.401 + execsql {
1.402 + SELECT rowid, * FROM t4;
1.403 + }
1.404 +} {1 1}
1.405 +do_test rowid-8.3 {
1.406 + execsql {
1.407 + INSERT INTO t3 VALUES(123);
1.408 + SELECT last_insert_rowid();
1.409 + }
1.410 +} {123}
1.411 +do_test rowid-8.4 {
1.412 + execsql {
1.413 + SELECT * FROM t3;
1.414 + }
1.415 +} {123}
1.416 +do_test rowid-8.5 {
1.417 + execsql {
1.418 + SELECT rowid, * FROM t4;
1.419 + }
1.420 +} {1 1 2 133}
1.421 +do_test rowid-8.6 {
1.422 + execsql {
1.423 + INSERT INTO t3 VALUES(NULL);
1.424 + SELECT last_insert_rowid();
1.425 + }
1.426 +} {124}
1.427 +do_test rowid-8.7 {
1.428 + execsql {
1.429 + SELECT * FROM t3;
1.430 + }
1.431 +} {123 124}
1.432 +do_test rowid-8.8 {
1.433 + execsql {
1.434 + SELECT rowid, * FROM t4;
1.435 + }
1.436 +} {1 1 2 133 3 134}
1.437 +} ;# endif trigger
1.438 +
1.439 +# If triggers are not enable, simulate their effect for the tests that
1.440 +# follow.
1.441 +ifcapable {!trigger} {
1.442 + execsql {
1.443 + CREATE TABLE t3(a integer primary key);
1.444 + INSERT INTO t3 VALUES(123);
1.445 + INSERT INTO t3 VALUES(124);
1.446 + }
1.447 +}
1.448 +
1.449 +# ticket #377: Comparison between integer primiary key and floating point
1.450 +# values.
1.451 +#
1.452 +do_test rowid-9.1 {
1.453 + execsql {
1.454 + SELECT * FROM t3 WHERE a<123.5
1.455 + }
1.456 +} {123}
1.457 +do_test rowid-9.2 {
1.458 + execsql {
1.459 + SELECT * FROM t3 WHERE a<124.5
1.460 + }
1.461 +} {123 124}
1.462 +do_test rowid-9.3 {
1.463 + execsql {
1.464 + SELECT * FROM t3 WHERE a>123.5
1.465 + }
1.466 +} {124}
1.467 +do_test rowid-9.4 {
1.468 + execsql {
1.469 + SELECT * FROM t3 WHERE a>122.5
1.470 + }
1.471 +} {123 124}
1.472 +do_test rowid-9.5 {
1.473 + execsql {
1.474 + SELECT * FROM t3 WHERE a==123.5
1.475 + }
1.476 +} {}
1.477 +do_test rowid-9.6 {
1.478 + execsql {
1.479 + SELECT * FROM t3 WHERE a==123.000
1.480 + }
1.481 +} {123}
1.482 +do_test rowid-9.7 {
1.483 + execsql {
1.484 + SELECT * FROM t3 WHERE a>100.5 AND a<200.5
1.485 + }
1.486 +} {123 124}
1.487 +do_test rowid-9.8 {
1.488 + execsql {
1.489 + SELECT * FROM t3 WHERE a>'xyz';
1.490 + }
1.491 +} {}
1.492 +do_test rowid-9.9 {
1.493 + execsql {
1.494 + SELECT * FROM t3 WHERE a<'xyz';
1.495 + }
1.496 +} {123 124}
1.497 +do_test rowid-9.10 {
1.498 + execsql {
1.499 + SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1
1.500 + }
1.501 +} {123}
1.502 +
1.503 +# Ticket #567. Comparisons of ROWID or integery primary key against
1.504 +# floating point numbers still do not always work.
1.505 +#
1.506 +do_test rowid-10.1 {
1.507 + execsql {
1.508 + CREATE TABLE t5(a);
1.509 + INSERT INTO t5 VALUES(1);
1.510 + INSERT INTO t5 VALUES(2);
1.511 + INSERT INTO t5 SELECT a+2 FROM t5;
1.512 + INSERT INTO t5 SELECT a+4 FROM t5;
1.513 + SELECT rowid, * FROM t5;
1.514 + }
1.515 +} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
1.516 +do_test rowid-10.2 {
1.517 + execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}
1.518 +} {6 6 7 7 8 8}
1.519 +do_test rowid-10.3 {
1.520 + execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}
1.521 +} {5 5 6 6 7 7 8 8}
1.522 +do_test rowid-10.4 {
1.523 + execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}
1.524 +} {6 6 7 7 8 8}
1.525 +do_test rowid-10.3.2 {
1.526 + execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}
1.527 +} {6 6 7 7 8 8}
1.528 +do_test rowid-10.5 {
1.529 + execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}
1.530 +} {6 6 7 7 8 8}
1.531 +do_test rowid-10.6 {
1.532 + execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}
1.533 +} {6 6 7 7 8 8}
1.534 +do_test rowid-10.7 {
1.535 + execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}
1.536 +} {1 1 2 2 3 3 4 4 5 5}
1.537 +do_test rowid-10.8 {
1.538 + execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}
1.539 +} {1 1 2 2 3 3 4 4 5 5}
1.540 +do_test rowid-10.9 {
1.541 + execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}
1.542 +} {1 1 2 2 3 3 4 4 5 5}
1.543 +do_test rowid-10.10 {
1.544 + execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}
1.545 +} {1 1 2 2 3 3 4 4 5 5}
1.546 +do_test rowid-10.11 {
1.547 + execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}
1.548 +} {8 8 7 7 6 6}
1.549 +do_test rowid-10.11.2 {
1.550 + execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}
1.551 +} {8 8 7 7 6 6 5 5}
1.552 +do_test rowid-10.12 {
1.553 + execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}
1.554 +} {8 8 7 7 6 6}
1.555 +do_test rowid-10.12.2 {
1.556 + execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}
1.557 +} {8 8 7 7 6 6}
1.558 +do_test rowid-10.13 {
1.559 + execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}
1.560 +} {8 8 7 7 6 6}
1.561 +do_test rowid-10.14 {
1.562 + execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}
1.563 +} {8 8 7 7 6 6}
1.564 +do_test rowid-10.15 {
1.565 + execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}
1.566 +} {5 5 4 4 3 3 2 2 1 1}
1.567 +do_test rowid-10.16 {
1.568 + execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}
1.569 +} {5 5 4 4 3 3 2 2 1 1}
1.570 +do_test rowid-10.17 {
1.571 + execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}
1.572 +} {5 5 4 4 3 3 2 2 1 1}
1.573 +do_test rowid-10.18 {
1.574 + execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}
1.575 +} {5 5 4 4 3 3 2 2 1 1}
1.576 +
1.577 +do_test rowid-10.30 {
1.578 + execsql {
1.579 + CREATE TABLE t6(a);
1.580 + INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;
1.581 + SELECT rowid, * FROM t6;
1.582 + }
1.583 +} {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1}
1.584 +do_test rowid-10.31.1 {
1.585 + execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}
1.586 +} {-5 5 -4 4 -3 3 -2 2 -1 1}
1.587 +do_test rowid-10.31.2 {
1.588 + execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}
1.589 +} {-5 5 -4 4 -3 3 -2 2 -1 1}
1.590 +do_test rowid-10.32.1 {
1.591 + execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}
1.592 +} {-1 1 -2 2 -3 3 -4 4 -5 5}
1.593 +do_test rowid-10.32.1 {
1.594 + execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}
1.595 +} {-1 1 -2 2 -3 3 -4 4 -5 5}
1.596 +do_test rowid-10.33 {
1.597 + execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}
1.598 +} {-5 5 -4 4 -3 3 -2 2 -1 1}
1.599 +do_test rowid-10.34 {
1.600 + execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}
1.601 +} {-1 1 -2 2 -3 3 -4 4 -5 5}
1.602 +do_test rowid-10.35.1 {
1.603 + execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}
1.604 +} {-5 5 -4 4 -3 3 -2 2 -1 1}
1.605 +do_test rowid-10.35.2 {
1.606 + execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}
1.607 +} {-4 4 -3 3 -2 2 -1 1}
1.608 +do_test rowid-10.36.1 {
1.609 + execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}
1.610 +} {-1 1 -2 2 -3 3 -4 4 -5 5}
1.611 +do_test rowid-10.36.2 {
1.612 + execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}
1.613 +} {-1 1 -2 2 -3 3 -4 4}
1.614 +do_test rowid-10.37 {
1.615 + execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}
1.616 +} {-5 5 -4 4 -3 3 -2 2 -1 1}
1.617 +do_test rowid-10.38 {
1.618 + execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}
1.619 +} {-1 1 -2 2 -3 3 -4 4 -5 5}
1.620 +do_test rowid-10.39 {
1.621 + execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}
1.622 +} {-8 8 -7 7 -6 6}
1.623 +do_test rowid-10.40 {
1.624 + execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}
1.625 +} {-6 6 -7 7 -8 8}
1.626 +do_test rowid-10.41 {
1.627 + execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}
1.628 +} {-8 8 -7 7 -6 6}
1.629 +do_test rowid-10.42 {
1.630 + execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}
1.631 +} {-6 6 -7 7 -8 8}
1.632 +do_test rowid-10.43 {
1.633 + execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}
1.634 +} {-8 8 -7 7 -6 6}
1.635 +do_test rowid-10.44 {
1.636 + execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}
1.637 +} {-6 6 -7 7 -8 8}
1.638 +do_test rowid-10.44 {
1.639 + execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}
1.640 +} {-8 8 -7 7 -6 6}
1.641 +do_test rowid-10.46 {
1.642 + execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}
1.643 +} {-6 6 -7 7 -8 8}
1.644 +
1.645 +# Comparison of rowid against string values.
1.646 +#
1.647 +do_test rowid-11.1 {
1.648 + execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}
1.649 +} {}
1.650 +do_test rowid-11.2 {
1.651 + execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}
1.652 +} {}
1.653 +do_test rowid-11.3 {
1.654 + execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}
1.655 +} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
1.656 +do_test rowid-11.4 {
1.657 + execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}
1.658 +} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
1.659 +
1.660 +# Test the automatic generation of rowids when the table already contains
1.661 +# a rowid with the maximum value.
1.662 +#
1.663 +# Once the the maximum rowid is taken, rowids are normally chosen at
1.664 +# random. By by reseting the random number generator, we can cause
1.665 +# the rowid guessing loop to collide with prior rowids, and test the
1.666 +# loop out to its limit of 100 iterations. After 100 collisions, the
1.667 +# rowid guesser gives up and reports SQLITE_FULL.
1.668 +#
1.669 +do_test rowid-12.1 {
1.670 + execsql {
1.671 + CREATE TABLE t7(x INTEGER PRIMARY KEY, y);
1.672 + INSERT INTO t7 VALUES(9223372036854775807,'a');
1.673 + SELECT y FROM t7;
1.674 + }
1.675 +} {a}
1.676 +do_test rowid-12.2 {
1.677 + db close
1.678 + sqlite3 db test.db
1.679 + save_prng_state
1.680 + execsql {
1.681 + INSERT INTO t7 VALUES(NULL,'b');
1.682 + SELECT x, y FROM t7;
1.683 + }
1.684 +} {1 b 9223372036854775807 a}
1.685 +execsql {INSERT INTO t7 VALUES(2,'y');}
1.686 +for {set i 1} {$i<=101} {incr i} {
1.687 + do_test rowid-12.3.$i {
1.688 + restore_prng_state
1.689 + execsql {
1.690 + INSERT INTO t7 VALUES(NULL,'x');
1.691 + INSERT OR IGNORE INTO t7 VALUES(last_insert_rowid()+1,'y');
1.692 + SELECT count(*) FROM t7 WHERE y=='x';
1.693 + }
1.694 + } $i
1.695 +}
1.696 +do_test rowid-12.4 {
1.697 + restore_prng_state
1.698 + catchsql {
1.699 + INSERT INTO t7 VALUES(NULL,'x');
1.700 + }
1.701 +} {1 {database or disk is full}}
1.702 +
1.703 +
1.704 +finish_test