Debian Codegraph @

test/without_rowid6.test

raw
1 # 2014-12-28
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 #
12 # Verify that WITHOUT ROWID tables work correctly when the PRIMARY KEY
13 # has redundant columns.
14 #
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18
19 proc do_execsql_test_if_vtab {tn sql {res {}}} {
20 ifcapable vtab { uplevel [list do_execsql_test $tn $sql $res] }
21 }
22
23 do_execsql_test without_rowid6-100 {
24 CREATE TABLE t1(a,b,c,d,e, PRIMARY KEY(a,b,c,a,b,c,d,a,b,c)) WITHOUT ROWID;
25 CREATE INDEX t1a ON t1(b, b);
26 WITH RECURSIVE
27 c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<1000)
28 INSERT INTO t1(a,b,c,d,e) SELECT i, i+1000, printf('x%dy',i), 0, 0 FROM c;
29 ANALYZE;
30 } {}
31 do_execsql_test_if_vtab without_rowid6-101 {
32 SELECT name, key FROM pragma_index_xinfo('t1');
33 } {a 1 b 1 c 1 d 1 e 0}
34 do_execsql_test without_rowid6-110 {
35 SELECT c FROM t1 WHERE a=123;
36 } {x123y}
37 do_execsql_test without_rowid6-120 {
38 SELECT c FROM t1 WHERE b=1123;
39 } {x123y}
40 do_execsql_test without_rowid6-130 {
41 SELECT c FROM t1 ORDER BY a DESC LIMIT 5;
42 } {x1000y x999y x998y x997y x996y}
43 do_execsql_test without_rowid6-140 {
44 SELECT c FROM t1 ORDER BY b LIMIT 5;
45 } {x1y x2y x3y x4y x5y}
46
47 # Column t1.b starts out as a unique index, but that index is
48 # subsequently converted into a PRIMARY KEY.
49 #
50 do_execsql_test without_rowid6-200 {
51 DROP TABLE IF EXISTS t1;
52 CREATE TABLE t1(
53 a UNIQUE,
54 b UNIQUE,
55 c UNIQUE,
56 PRIMARY KEY(b)
57 ) WITHOUT ROWID;
58 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
59 SELECT a FROM t1 WHERE b>3 ORDER BY b;
60 } {4 1}
61 do_execsql_test_if_vtab without_rowid6-201 {
62 SELECT name, key FROM pragma_index_xinfo('t1');
63 } {b 1 a 0 c 0}
64 do_execsql_test without_rowid6-210 {
65 EXPLAIN QUERY PLAN
66 SELECT a FROM t1 WHERE b>3 ORDER BY b;
67 } {/SEARCH t1 USING PRIMARY KEY .b>../}
68 do_execsql_test without_rowid6-220 {
69 PRAGMA index_list(t1);
70 } {/sqlite_autoindex_t1_2 1 pk/}
71
72 do_execsql_test without_rowid6-300 {
73 DROP TABLE IF EXISTS t1;
74 CREATE TABLE t1(
75 a UNIQUE,
76 b PRIMARY KEY,
77 c UNIQUE,
78 UNIQUE(b)
79 ) WITHOUT ROWID;
80 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
81 SELECT a FROM t1 WHERE b>3 ORDER BY b;
82 } {4 1}
83 do_execsql_test without_rowid6-310 {
84 EXPLAIN QUERY PLAN
85 SELECT a FROM t1 WHERE b>3 ORDER BY b;
86 } {/SEARCH t1 USING PRIMARY KEY .b>../}
87 do_execsql_test without_rowid6-320 {
88 PRAGMA index_list(t1);
89 } {/sqlite_autoindex_t1_2 1 pk/}
90
91 do_execsql_test without_rowid6-400 {
92 DROP TABLE IF EXISTS t1;
93 CREATE TABLE t1(
94 a UNIQUE,
95 b UNIQUE PRIMARY KEY,
96 c UNIQUE
97 ) WITHOUT ROWID;
98 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
99 SELECT a FROM t1 WHERE b>3 ORDER BY b;
100 } {4 1}
101 do_execsql_test without_rowid6-410 {
102 EXPLAIN QUERY PLAN
103 SELECT a FROM t1 WHERE b>3 ORDER BY b;
104 } {/SEARCH t1 USING PRIMARY KEY .b>../}
105 do_execsql_test without_rowid6-420 {
106 PRAGMA index_list(t1);
107 } {/sqlite_autoindex_t1_2 1 pk/}
108
109 do_execsql_test without_rowid6-500 {
110 DROP TABLE IF EXISTS t1;
111 CREATE TABLE t1(a,b,c,
112 UNIQUE(b,c),
113 PRIMARY KEY(b,c)
114 ) WITHOUT ROWID;
115 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
116 SELECT a FROM t1 WHERE b>3 ORDER BY b;
117 } {4 1}
118 do_execsql_test_if_vtab without_rowid6-501 {
119 SELECT name, key FROM pragma_index_xinfo('t1');
120 } {b 1 c 1 a 0}
121 do_execsql_test without_rowid6-510 {
122 EXPLAIN QUERY PLAN
123 SELECT a FROM t1 WHERE b>3 ORDER BY b;
124 } {/SEARCH t1 USING PRIMARY KEY .b>../}
125 do_execsql_test without_rowid6-520 {
126 PRAGMA index_list(t1);
127 } {/sqlite_autoindex_t1_1 1 pk/}
128
129 do_catchsql_test without_rowid6-600 {
130 CREATE TABLE t6(a,b,c,PRIMARY KEY(a,rowid,b))WITHOUT ROWID;
131 } {1 {no such column: rowid}}
132
133
134 finish_test