Annotation of embedaddon/sqlite3/test/rowid.test, revision 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