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