xref: /sqlite-3.40.0/test/without_rowid6.test (revision 8210233c) 
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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19proc do_execsql_test_if_vtab {tn sql {res {}}} {
20  ifcapable vtab { uplevel [list do_execsql_test $tn $sql $res] }
21}
22
23do_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} {}
31do_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}
34do_execsql_test without_rowid6-110 {
35  SELECT c FROM t1 WHERE a=123;
36} {x123y}
37do_execsql_test without_rowid6-120 {
38  SELECT c FROM t1 WHERE b=1123;
39} {x123y}
40do_execsql_test without_rowid6-130 {
41  SELECT c FROM t1 ORDER BY a DESC LIMIT 5;
42} {x1000y x999y x998y x997y x996y}
43do_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#
50do_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}
61do_execsql_test_if_vtab without_rowid6-201 {
62  SELECT name, key FROM pragma_index_xinfo('t1');
63} {b 1 a 0 c 0}
64do_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>../}
68do_execsql_test without_rowid6-220 {
69  PRAGMA index_list(t1);
70} {/sqlite_autoindex_t1_2 1 pk/}
71
72do_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}
83do_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>../}
87do_execsql_test without_rowid6-320 {
88  PRAGMA index_list(t1);
89} {/sqlite_autoindex_t1_2 1 pk/}
90
91do_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}
101do_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>../}
105do_execsql_test without_rowid6-420 {
106  PRAGMA index_list(t1);
107} {/sqlite_autoindex_t1_2 1 pk/}
108
109do_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}
118do_execsql_test_if_vtab without_rowid6-501 {
119  SELECT name, key FROM pragma_index_xinfo('t1');
120} {b 1 c 1 a 0}
121do_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>../}
125do_execsql_test without_rowid6-520 {
126  PRAGMA index_list(t1);
127} {/sqlite_autoindex_t1_1 1 pk/}
128
129do_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
134finish_test
135