xref: /sqlite-3.40.0/test/without_rowid1.test (revision a3fdec71) 
1# 2013-10-30
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# This file implements regression tests for SQLite library.  The
13# focus of this file is testing WITHOUT ROWID tables.
14#
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18set testprefix without_rowid1
19
20# Create and query a WITHOUT ROWID table.
21#
22do_execsql_test without_rowid1-1.0 {
23  CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID;
24  CREATE INDEX t1bd ON t1(b, d);
25  INSERT INTO t1 VALUES('journal','sherman','ammonia','helena');
26  INSERT INTO t1 VALUES('dynamic','juliet','flipper','command');
27  INSERT INTO t1 VALUES('journal','sherman','gamma','patriot');
28  INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena');
29  SELECT *, '|' FROM t1 ORDER BY c, a;
30} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
31
32integrity_check without_rowid1-1.0ic
33
34do_execsql_test without_rowid1-1.1 {
35  SELECT *, '|' FROM t1 ORDER BY +c, a;
36} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
37
38do_execsql_test without_rowid1-1.2 {
39  SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC;
40} {journal sherman gamma patriot | dynamic juliet flipper command | journal sherman ammonia helena | arctic sleep ammonia helena |}
41
42do_execsql_test without_rowid1-1.11 {
43  SELECT *, '|' FROM t1 ORDER BY b, d;
44} {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
45
46do_execsql_test without_rowid1-1.12 {
47  SELECT *, '|' FROM t1 ORDER BY +b, d;
48} {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
49
50# Trying to insert a duplicate PRIMARY KEY fails.
51#
52do_test without_rowid1-1.21 {
53  catchsql {
54    INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard');
55  }
56} {1 {UNIQUE constraint failed: t1.c, t1.a}}
57
58# REPLACE INTO works, however.
59#
60do_execsql_test without_rowid1-1.22 {
61  REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard');
62  SELECT *, '|' FROM t1 ORDER BY c, a;
63} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |}
64
65do_execsql_test without_rowid1-1.23 {
66  SELECT *, '|' FROM t1 ORDER BY b, d;
67} {dynamic phone flipper harvard | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
68
69# UPDATE statements.
70#
71do_execsql_test without_rowid1-1.31 {
72  UPDATE t1 SET d=3.1415926 WHERE a='journal';
73  SELECT *, '|' FROM t1 ORDER BY c, a;
74} {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | dynamic phone flipper harvard | journal sherman gamma 3.1415926 |}
75do_execsql_test without_rowid1-1.32 {
76  SELECT *, '|' FROM t1 ORDER BY b, d;
77} {dynamic phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
78
79do_execsql_test without_rowid1-1.35 {
80  UPDATE t1 SET a=1250 WHERE b='phone';
81  SELECT *, '|' FROM t1 ORDER BY c, a;
82} {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | 1250 phone flipper harvard | journal sherman gamma 3.1415926 |}
83integrity_check without_rowid1-1.36
84
85do_execsql_test without_rowid1-1.37 {
86  SELECT *, '|' FROM t1 ORDER BY b, d;
87} {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
88
89do_execsql_test without_rowid1-1.40 {
90  VACUUM;
91  SELECT *, '|' FROM t1 ORDER BY b, d;
92} {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
93integrity_check without_rowid1-1.41
94
95# Verify that ANALYZE works
96#
97do_execsql_test without_rowid1-1.50 {
98  ANALYZE;
99  SELECT * FROM sqlite_stat1 ORDER BY idx;
100} {t1 t1 {4 2 1} t1 t1bd {4 2 2}}
101ifcapable stat3 {
102  do_execsql_test without_rowid1-1.51 {
103    SELECT DISTINCT tbl, idx FROM sqlite_stat3 ORDER BY idx;
104  } {t1 t1 t1 t1bd}
105}
106ifcapable stat4 {
107  do_execsql_test without_rowid1-1.52 {
108    SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx;
109  } {t1 t1 t1 t1bd}
110}
111
112#----------
113
114do_execsql_test 2.1.1 {
115  CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
116  INSERT INTO t4 VALUES('abc', 'def');
117  SELECT * FROM t4;
118} {abc def}
119do_execsql_test 2.1.2 {
120  UPDATE t4 SET a = 'ABC';
121  SELECT * FROM t4;
122} {ABC def}
123
124do_execsql_test 2.2.1 {
125  DROP TABLE t4;
126  CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
127  INSERT INTO t4(a, b) VALUES('abc', 'def');
128  SELECT * FROM t4;
129} {def abc}
130
131do_execsql_test 2.2.2 {
132  UPDATE t4 SET a = 'ABC', b = 'xyz';
133  SELECT * FROM t4;
134} {xyz ABC}
135
136do_execsql_test 2.3.1 {
137  CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
138  INSERT INTO t5(a, b) VALUES('abc', 'def');
139  UPDATE t5 SET a='abc', b='def';
140} {}
141
142do_execsql_test 2.4.1 {
143  CREATE TABLE t6 (
144    a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
145  ) WITHOUT ROWID;
146
147  INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
148  UPDATE t6 SET a='ABC', c='ghi';
149} {}
150
151do_execsql_test 2.4.2 {
152  SELECT * FROM t6 ORDER BY b, a;
153  SELECT * FROM t6 ORDER BY c;
154} {ABC def ghi ABC def ghi}
155
156#-------------------------------------------------------------------------
157# Unless the destination table is completely empty, the xfer optimization
158# is disabled for WITHOUT ROWID tables. The following tests check for
159# some problems that might occur if this were not the case.
160#
161reset_db
162do_execsql_test 3.1.1 {
163  CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
164  CREATE UNIQUE INDEX i1 ON t1(b);
165
166  CREATE TABLE t2(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
167  CREATE UNIQUE INDEX i2 ON t2(b);
168
169  INSERT INTO t1 VALUES('one', 'two');
170  INSERT INTO t2 VALUES('three', 'two');
171}
172
173do_execsql_test 3.1.2 {
174  INSERT OR REPLACE INTO t1 SELECT * FROM t2;
175  SELECT * FROM t1;
176} {three two}
177
178do_execsql_test 3.1.3 {
179  DELETE FROM t1;
180  INSERT INTO t1 SELECT * FROM t2;
181  SELECT * FROM t1;
182} {three two}
183
184do_catchsql_test 3.1.4 {
185  INSERT INTO t2 VALUES('four', 'four');
186  INSERT INTO t2 VALUES('six', 'two');
187  INSERT INTO t1 SELECT * FROM t2;
188} {1 {UNIQUE constraint failed: t2.b}}
189
190do_execsql_test 3.1.5 {
191  CREATE TABLE t3(a PRIMARY KEY);
192  CREATE TABLE t4(a PRIMARY KEY);
193
194  INSERT INTO t4 VALUES('i');
195  INSERT INTO t4 VALUES('ii');
196  INSERT INTO t4 VALUES('iii');
197
198  INSERT INTO t3 SELECT * FROM t4;
199  SELECT * FROM t3;
200} {i ii iii}
201
202finish_test
203
204