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 #***********************************************************************
12 # Verify that WITHOUT ROWID tables work correctly when the PRIMARY KEY
13 # has redundant columns.
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 do_execsql_test without_rowid6-100 {
20 CREATE TABLE t1(a,b,c,d,e, PRIMARY KEY(a,b,c,a,b,c,d,a,b,c)) WITHOUT ROWID;
21 CREATE INDEX t1a ON t1(b, b);
23 c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<1000)
24 INSERT INTO t1(a,b,c,d,e) SELECT i, i+1000, printf('x%dy',i), 0, 0 FROM c;
27 do_execsql_test without_rowid6-110 {
28 SELECT c FROM t1 WHERE a=123;
30 do_execsql_test without_rowid6-120 {
31 SELECT c FROM t1 WHERE b=1123;
33 do_execsql_test without_rowid6-130 {
34 SELECT c FROM t1 ORDER BY a DESC LIMIT 5;
35 } {x1000y x999y x998y x997y x996y}
36 do_execsql_test without_rowid6-140 {
37 SELECT c FROM t1 ORDER BY b LIMIT 5;
38 } {x1y x2y x3y x4y x5y}
40 # Column t1.b starts out as a unique index, but that index is
41 # subsequently converted into a PRIMARY KEY.
43 do_execsql_test without_rowid6-200 {
44 DROP TABLE IF EXISTS t1;
51 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
52 SELECT a FROM t1 WHERE b>3 ORDER BY b;
54 do_execsql_test without_rowid6-210 {
56 SELECT a FROM t1 WHERE b>3 ORDER BY b;
57 } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
58 do_execsql_test without_rowid6-220 {
59 PRAGMA index_list(t1);
60 } {/sqlite_autoindex_t1_2 1 pk/}
62 do_execsql_test without_rowid6-300 {
63 DROP TABLE IF EXISTS t1;
70 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
71 SELECT a FROM t1 WHERE b>3 ORDER BY b;
73 do_execsql_test without_rowid6-310 {
75 SELECT a FROM t1 WHERE b>3 ORDER BY b;
76 } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
77 do_execsql_test without_rowid6-320 {
78 PRAGMA index_list(t1);
79 } {/sqlite_autoindex_t1_2 1 pk/}
81 do_execsql_test without_rowid6-400 {
82 DROP TABLE IF EXISTS t1;
88 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
89 SELECT a FROM t1 WHERE b>3 ORDER BY b;
91 do_execsql_test without_rowid6-410 {
93 SELECT a FROM t1 WHERE b>3 ORDER BY b;
94 } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
95 do_execsql_test without_rowid6-420 {
96 PRAGMA index_list(t1);
97 } {/sqlite_autoindex_t1_2 1 pk/}
99 do_execsql_test without_rowid6-500 {
100 DROP TABLE IF EXISTS t1;
101 CREATE TABLE t1(a,b,c,
105 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
106 SELECT a FROM t1 WHERE b>3 ORDER BY b;
108 do_execsql_test without_rowid6-510 {
110 SELECT a FROM t1 WHERE b>3 ORDER BY b;
111 } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
112 do_execsql_test without_rowid6-520 {
113 PRAGMA index_list(t1);
114 } {/sqlite_autoindex_t1_1 1 pk/}
116 do_catchsql_test without_rowid6-600 {
117 CREATE TABLE t6(a,b,c,PRIMARY KEY(a,rowid,b))WITHOUT ROWID;
118 } {1 {no such column: rowid}}