Lines Matching refs:rowid
16 # special column, usually called the "rowid", that uniquely identifies
21 set testprefix rowid
25 do_test rowid-1.1 {
33 do_test rowid-1.2 {
34 set r [execsql {SELECT rowid FROM t1 ORDER BY x}]
42 do_test rowid-1.3 {
44 set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"
47 do_test rowid-1.4 {
49 set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"
52 do_test rowid-1.5 {
57 do_test rowid-1.6 {
62 do_test rowid-1.7 {
67 do_test rowid-1.7.1 {
72 execsql "SELECT x FROM t1 WHERE rowid=$norow"
74 do_test rowid-1.8 {
80 do_test rowid-1.9 {
86 do_test rowid-1.10 {
95 do_test rowid-2.1 {
97 INSERT INTO t1(rowid,x,y) VALUES(1234,5,6);
98 SELECT rowid, * FROM t1;
101 do_test rowid-2.2 {
103 UPDATE t1 SET rowid=12345 WHERE x==1;
104 SELECT rowid, * FROM t1
107 do_test rowid-2.3 {
110 SELECT rowid, * FROM t1 WHERE rowid>1000;
113 do_test rowid-2.4 {
116 SELECT rowid, * FROM t1;
119 do_test rowid-2.5 {
122 SELECT rowid, * FROM t1 WHERE rowid>1000;
125 do_test rowid-2.6 {
128 SELECT rowid, * FROM t1 WHERE rowid>1000;
135 do_test rowid-2.7 {
141 do_test rowid-2.8 {
152 do_test rowid-2.9 {
153 set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]
155 } {1 {table t1 has no column named rowid}}
156 do_test rowid-2.10 {
160 do_test rowid-2.11 {
164 do_test rowid-2.12 {
165 set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]
167 } {1 {table t1 has no column named rowid}}
173 do_test rowid-3.1 {
175 CREATE TABLE t2(rowid int, x int, y int);
182 do_test rowid-3.2 {
183 execsql {SELECT * FROM t2 ORDER BY rowid}
185 do_test rowid-3.3 {
186 execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
188 do_test rowid-3.4 {
189 set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
191 set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
197 do_probtest rowid-3.5 {
198 set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
206 do_test rowid-4.1 {
215 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
217 do_test rowid-4.2 {
218 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
220 do_test rowid-4.2.1 {
221 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid}
223 do_test rowid-4.2.2 {
224 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
226 do_test rowid-4.2.3 {
227 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid}
229 do_test rowid-4.2.4 {
230 execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4}
232 do_test rowid-4.2.5 {
233 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
235 do_test rowid-4.2.6 {
236 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid}
238 do_test rowid-4.2.7 {
239 execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4}
241 do_test rowid-4.3 {
243 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
245 do_test rowid-4.3.1 {
246 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
248 do_test rowid-4.3.2 {
249 execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x}
251 do_test rowid-4.4 {
252 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
254 do_test rowid-4.4.1 {
255 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
257 do_test rowid-4.4.2 {
258 execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}
260 do_test rowid-4.5 {
265 WHERE t2.y==256 AND t1.rowid==t2.rowid
268 do_test rowid-4.5.1 {
272 WHERE t1.OID==t2.rowid AND t2.y==81
275 do_test rowid-4.6 {
278 WHERE t2.y==256 AND t1.rowid==t2.rowid
282 do_test rowid-5.1.1 {
291 do_test rowid-5.1.2 {
295 # Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.
297 do_test rowid-6.1 {
302 do_test rowid-6.2 {
304 if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""} break
307 DELETE FROM t1 WHERE rowid=$::norow
310 do_test rowid-6.3 {
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.
321 do_test rowid-7.0 {
327 SELECT rowid, * FROM t1;
330 do_test rowid-7.1 {
333 SELECT rowid,* FROM t1
336 do_test rowid-7.2 {
343 do_test rowid-7.3 {
349 do_test rowid-7.4 {
356 do_test rowid-7.5 {
364 do_test rowid-7.6 {
369 do_test rowid-7.7 {
380 do_test rowid-7.8 {
392 do_test rowid-8.1 {
403 do_test rowid-8.2 {
405 SELECT rowid, * FROM t4;
408 do_test rowid-8.3 {
414 do_test rowid-8.4 {
419 do_test rowid-8.5 {
421 SELECT rowid, * FROM t4;
424 do_test rowid-8.6 {
430 do_test rowid-8.7 {
435 do_test rowid-8.8 {
437 SELECT rowid, * FROM t4;
455 do_test rowid-9.1 {
460 do_test rowid-9.2 {
465 do_test rowid-9.3 {
470 do_test rowid-9.4 {
475 do_test rowid-9.5 {
480 do_test rowid-9.6 {
485 do_test rowid-9.7 {
490 do_test rowid-9.8 {
495 do_test rowid-9.9 {
500 do_test rowid-9.10 {
509 do_test rowid-10.1 {
516 SELECT rowid, * FROM t5;
519 do_test rowid-10.2 {
520 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}
522 do_test rowid-10.3 {
523 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}
525 do_test rowid-10.4 {
526 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}
528 do_test rowid-10.3.2 {
529 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}
531 do_test rowid-10.5 {
532 execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}
534 do_test rowid-10.6 {
535 execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}
537 do_test rowid-10.7 {
538 execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}
540 do_test rowid-10.8 {
541 execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}
543 do_test rowid-10.9 {
544 execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}
546 do_test rowid-10.10 {
547 execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}
549 do_test rowid-10.11 {
550 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}
552 do_test rowid-10.11.2 {
553 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}
555 do_test rowid-10.12 {
556 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}
558 do_test rowid-10.12.2 {
559 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}
561 do_test rowid-10.13 {
562 execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}
564 do_test rowid-10.14 {
565 execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}
567 do_test rowid-10.15 {
568 execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}
570 do_test rowid-10.16 {
571 execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}
573 do_test rowid-10.17 {
574 execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}
576 do_test rowid-10.18 {
577 execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}
580 do_test rowid-10.30 {
583 INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;
584 SELECT rowid, * FROM t6;
587 do_test rowid-10.31.1 {
588 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}
590 do_test rowid-10.31.2 {
591 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}
593 do_test rowid-10.32.1 {
594 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}
596 do_test rowid-10.32.1 {
597 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}
599 do_test rowid-10.33 {
600 execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}
602 do_test rowid-10.34 {
603 execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}
605 do_test rowid-10.35.1 {
606 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}
608 do_test rowid-10.35.2 {
609 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}
611 do_test rowid-10.36.1 {
612 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}
614 do_test rowid-10.36.2 {
615 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}
617 do_test rowid-10.37 {
618 execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}
620 do_test rowid-10.38 {
621 execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}
623 do_test rowid-10.39 {
624 execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}
626 do_test rowid-10.40 {
627 execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}
629 do_test rowid-10.41 {
630 execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}
632 do_test rowid-10.42 {
633 execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}
635 do_test rowid-10.43 {
636 execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}
638 do_test rowid-10.44 {
639 execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}
641 do_test rowid-10.44 {
642 execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}
644 do_test rowid-10.46 {
645 execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}
648 # Comparison of rowid against string values.
650 do_test rowid-11.1 {
651 execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}
653 do_test rowid-11.2 {
654 execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}
656 do_test rowid-11.3 {
657 execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}
659 do_test rowid-11.4 {
660 execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}
663 do_test rowid-11.asc.1 {
664 execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc' ORDER BY 1 ASC}
666 do_test rowid-11.asc.2 {
667 execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc' ORDER BY 1 ASC}
669 do_test rowid-11.asc.3 {
670 execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc' ORDER BY 1 ASC}
672 do_test rowid-11.asc.4 {
673 execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc' ORDER BY 1 ASC}
676 do_test rowid-11.desc.1 {
677 execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc' ORDER BY 1 DESC}
679 do_test rowid-11.desc.2 {
680 execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc' ORDER BY 1 DESC}
682 do_test rowid-11.desc.3 {
683 execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc' ORDER BY 1 DESC}
685 do_test rowid-11.desc.4 {
686 execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc' ORDER BY 1 DESC}
690 # a rowid with the maximum value.
692 # Once the maximum rowid is taken, rowids are normally chosen at
694 # the rowid guessing loop to collide with prior rowids, and test the
696 # rowid guesser gives up and reports SQLITE_FULL.
698 do_test rowid-12.1 {
706 do_test rowid-12.2 {
717 do_test rowid-12.3.$i {
726 do_test rowid-12.4 {
738 db eval {INSERT INTO t13(rowid,x) VALUES($n,$n*$n)}
742 do_execsql_test rowid-13.1 {
744 INSERT INTO t13(rowid,x) VALUES(1234,5);
745 SELECT rowid, x, addrow(rowid+1000), '|' FROM t13 LIMIT 3;
750 do_execsql_test rowid-14.0 {
754 do_execsql_test rowid-14.1 {
755 SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC;
757 do_execsql_test rowid-14.2 {
758 SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC;
761 do_execsql_test rowid-14.3 {
763 SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC;
765 do_execsql_test rowid-14.4 {
766 SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC;
770 do_execsql_test rowid-15.0 {
778 do_execsql_test rowid-15.1 {
780 WHERE (t2.rowid <= 'a') OR (t1.c0 <= t2.c0) LIMIT 100
783 do_execsql_test rowid-15.2 {
786 WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC LIMIT 100
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.
801 INSERT INTO t1(rowid, x) VALUES(1, 1);
803 INSERT INTO t3(rowid, z) VALUES(3, 3);
806 do_execsql_test 16.1 { SELECT rowid FROM t1, t2; } {1}
807 do_execsql_test 16.2 { SELECT rowid FROM t1, v1; } {1}
808 do_execsql_test 16.3 { SELECT rowid FROM t3, v1; } {3}
809 do_execsql_test 16.4 { SELECT rowid FROM t3, (SELECT 123); } {3}
811 do_execsql_test 16.5 { SELECT rowid FROM t2, t1; } {1}
812 do_execsql_test 16.6 { SELECT rowid FROM v1, t1; } {1}
813 do_execsql_test 16.7 { SELECT rowid FROM v1, t3; } {3}
814 do_execsql_test 16.8 { SELECT rowid FROM (SELECT 123), t3; } {3}
816 do_catchsql_test 16.5 { SELECT rowid FROM t1, t3; } {1 {no such column: rowid}}