Debian Codegraph @

test/without_rowid1.test

raw
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
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set testprefix without_rowid1
19
20 proc do_execsql_test_if_vtab {tn sql {res {}}} {
21 ifcapable vtab { uplevel [list do_execsql_test $tn $sql $res] }
22 }
23
24 # Create and query a WITHOUT ROWID table.
25 #
26 do_execsql_test without_rowid1-1.0 {
27 CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID;
28 CREATE INDEX t1bd ON t1(b, d);
29 INSERT INTO t1 VALUES('journal','sherman','ammonia','helena');
30 INSERT INTO t1 VALUES('dynamic','juliet','flipper','command');
31 INSERT INTO t1 VALUES('journal','sherman','gamma','patriot');
32 INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena');
33 SELECT *, '|' FROM t1 ORDER BY c, a;
34 } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
35
36 integrity_check without_rowid1-1.0ic
37
38 do_execsql_test_if_vtab without_rowid1-1.0ixi {
39 SELECT name, key FROM pragma_index_xinfo('t1');
40 } {c 1 a 1 b 0 d 0}
41 do_execsql_test_if_vtab without_rowid1-1.0tl {
42 SELECT wr FROM pragma_table_list('t1');
43 } {1}
44
45 do_execsql_test without_rowid1-1.1 {
46 SELECT *, '|' FROM t1 ORDER BY +c, a;
47 } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
48
49 do_execsql_test without_rowid1-1.2 {
50 SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC;
51 } {journal sherman gamma patriot | dynamic juliet flipper command | journal sherman ammonia helena | arctic sleep ammonia helena |}
52
53 do_execsql_test without_rowid1-1.11 {
54 SELECT *, '|' FROM t1 ORDER BY b, d;
55 } {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
56
57 do_execsql_test without_rowid1-1.12 {
58 SELECT *, '|' FROM t1 ORDER BY +b, d;
59 } {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
60
61 # Trying to insert a duplicate PRIMARY KEY fails.
62 #
63 do_test without_rowid1-1.21 {
64 catchsql {
65 INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard');
66 }
67 } {1 {UNIQUE constraint failed: t1.c, t1.a}}
68
69 # REPLACE INTO works, however.
70 #
71 do_execsql_test without_rowid1-1.22 {
72 REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard');
73 SELECT *, '|' FROM t1 ORDER BY c, a;
74 } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |}
75
76 do_execsql_test without_rowid1-1.23 {
77 SELECT *, '|' FROM t1 ORDER BY b, d;
78 } {dynamic phone flipper harvard | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
79
80 # UPDATE statements.
81 #
82 do_execsql_test without_rowid1-1.31 {
83 UPDATE t1 SET d=3.1415926 WHERE a='journal';
84 SELECT *, '|' FROM t1 ORDER BY c, a;
85 } {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | dynamic phone flipper harvard | journal sherman gamma 3.1415926 |}
86 do_execsql_test without_rowid1-1.32 {
87 SELECT *, '|' FROM t1 ORDER BY b, d;
88 } {dynamic phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
89
90 do_execsql_test without_rowid1-1.35 {
91 UPDATE t1 SET a=1250 WHERE b='phone';
92 SELECT *, '|' FROM t1 ORDER BY c, a;
93 } {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | 1250 phone flipper harvard | journal sherman gamma 3.1415926 |}
94 integrity_check without_rowid1-1.36
95
96 do_execsql_test without_rowid1-1.37 {
97 SELECT *, '|' FROM t1 ORDER BY b, d;
98 } {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
99
100 do_execsql_test without_rowid1-1.40 {
101 VACUUM;
102 SELECT *, '|' FROM t1 ORDER BY b, d;
103 } {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
104 integrity_check without_rowid1-1.41
105
106 # Verify that ANALYZE works
107 #
108 do_execsql_test without_rowid1-1.50 {
109 ANALYZE;
110 SELECT * FROM sqlite_stat1 ORDER BY idx;
111 } {t1 t1 {4 2 1} t1 t1bd {4 2 2}}
112 ifcapable stat4 {
113 do_execsql_test without_rowid1-1.52 {
114 SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx;
115 } {t1 t1 t1 t1bd}
116 }
117
118 #----------
119
120 do_execsql_test 2.1.1 {
121 CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
122 INSERT INTO t4 VALUES('abc', 'def');
123 SELECT * FROM t4;
124 } {abc def}
125 do_execsql_test 2.1.2 {
126 UPDATE t4 SET a = 'ABC';
127 SELECT * FROM t4;
128 } {ABC def}
129 do_execsql_test_if_vtab 2.1.3 {
130 SELECT name, coll, key FROM pragma_index_xinfo('t4');
131 } {a nocase 1 b BINARY 0}
132
133 do_execsql_test 2.2.1 {
134 DROP TABLE t4;
135 CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
136 INSERT INTO t4(a, b) VALUES('abc', 'def');
137 SELECT * FROM t4;
138 } {def abc}
139
140 do_execsql_test 2.2.2 {
141 UPDATE t4 SET a = 'ABC', b = 'xyz';
142 SELECT * FROM t4;
143 } {xyz ABC}
144
145 do_execsql_test_if_vtab 2.2.3 {
146 SELECT name, coll, key FROM pragma_index_xinfo('t4');
147 } {a nocase 1 b BINARY 0}
148
149
150 do_execsql_test 2.3.1 {
151 CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
152 INSERT INTO t5(a, b) VALUES('abc', 'def');
153 UPDATE t5 SET a='abc', b='def';
154 } {}
155
156 do_execsql_test_if_vtab 2.3.2 {
157 SELECT name, coll, key FROM pragma_index_xinfo('t5');
158 } {b BINARY 1 a BINARY 1}
159
160
161 do_execsql_test 2.4.1 {
162 CREATE TABLE t6 (
163 a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
164 ) WITHOUT ROWID;
165
166 INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
167 UPDATE t6 SET a='ABC', c='ghi';
168 } {}
169
170 do_execsql_test 2.4.2 {
171 SELECT * FROM t6 ORDER BY b, a;
172 SELECT * FROM t6 ORDER BY c;
173 } {ABC def ghi ABC def ghi}
174
175 do_execsql_test_if_vtab 2.4.3 {
176 SELECT name, coll, key FROM pragma_index_xinfo('t6');
177 } {b BINARY 1 a nocase 1 c BINARY 0}
178
179
180 #-------------------------------------------------------------------------
181 # Unless the destination table is completely empty, the xfer optimization
182 # is disabled for WITHOUT ROWID tables. The following tests check for
183 # some problems that might occur if this were not the case.
184 #
185 reset_db
186 do_execsql_test 3.1.1 {
187 CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
188 CREATE UNIQUE INDEX i1 ON t1(b);
189
190 CREATE TABLE t2(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
191 CREATE UNIQUE INDEX i2 ON t2(b);
192
193 INSERT INTO t1 VALUES('one', 'two');
194 INSERT INTO t2 VALUES('three', 'two');
195 }
196
197 do_execsql_test 3.1.2 {
198 INSERT OR REPLACE INTO t1 SELECT * FROM t2;
199 SELECT * FROM t1;
200 } {three two}
201
202 do_execsql_test 3.1.3 {
203 DELETE FROM t1;
204 INSERT INTO t1 SELECT * FROM t2;
205 SELECT * FROM t1;
206 } {three two}
207
208 do_catchsql_test 3.1.4 {
209 INSERT INTO t2 VALUES('four', 'four');
210 INSERT INTO t2 VALUES('six', 'two');
211 INSERT INTO t1 SELECT * FROM t2;
212 } {1 {UNIQUE constraint failed: t2.b}}
213
214 do_execsql_test 3.1.5 {
215 CREATE TABLE t3(a PRIMARY KEY);
216 CREATE TABLE t4(a PRIMARY KEY);
217
218 INSERT INTO t4 VALUES('i');
219 INSERT INTO t4 VALUES('ii');
220 INSERT INTO t4 VALUES('iii');
221
222 INSERT INTO t3 SELECT * FROM t4;
223 SELECT * FROM t3;
224 } {i ii iii}
225
226 ############################################################################
227 # Ticket [c34d0557f740c450709d6e33df72d4f3f651a3cc]
228 # Name resolution issue with WITHOUT ROWID
229 #
230 do_execsql_test 4.1 {
231 CREATE TABLE t41(a PRIMARY KEY) WITHOUT ROWID;
232 INSERT INTO t41 VALUES('abc');
233 CREATE TABLE t42(x);
234 INSERT INTO t42 VALUES('xyz');
235 SELECT t42.rowid FROM t41, t42;
236 } {1}
237 do_execsql_test 4.2 {
238 SELECT t42.rowid FROM t42, t41;
239 } {1}
240
241
242 #--------------------------------------------------------------------------
243 # The following tests verify that the trailing PK fields added to each
244 # entry in an index on a WITHOUT ROWID table are used correctly.
245 #
246 do_execsql_test 5.0 {
247 CREATE TABLE t45(a PRIMARY KEY, b, c) WITHOUT ROWID;
248 CREATE INDEX i45 ON t45(b);
249
250 INSERT INTO t45 VALUES(2, 'one', 'x');
251 INSERT INTO t45 VALUES(4, 'one', 'x');
252 INSERT INTO t45 VALUES(6, 'one', 'x');
253 INSERT INTO t45 VALUES(8, 'one', 'x');
254 INSERT INTO t45 VALUES(10, 'one', 'x');
255
256 INSERT INTO t45 VALUES(1, 'two', 'x');
257 INSERT INTO t45 VALUES(3, 'two', 'x');
258 INSERT INTO t45 VALUES(5, 'two', 'x');
259 INSERT INTO t45 VALUES(7, 'two', 'x');
260 INSERT INTO t45 VALUES(9, 'two', 'x');
261 }
262
263 do_eqp_test 5.1 {
264 SELECT * FROM t45 WHERE b=? AND a>?
265 } {USING INDEX i45 (b=? AND a>?)}
266
267 do_execsql_test 5.2 {
268 SELECT * FROM t45 WHERE b='two' AND a>4
269 } {5 two x 7 two x 9 two x}
270
271 do_execsql_test 5.3 {
272 SELECT * FROM t45 WHERE b='one' AND a<8
273 } { 2 one x 4 one x 6 one x }
274
275 do_execsql_test 5.4 {
276 CREATE TABLE t46(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID;
277 WITH r(x) AS (
278 SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<100
279 )
280 INSERT INTO t46 SELECT x / 20, x % 20, x % 10, x FROM r;
281 }
282
283 set queries {
284 1 2 "c = 5 AND a = 1" {i46 (c=? AND a=?)}
285 2 6 "c = 4 AND a < 3" {i46 (c=? AND a<?)}
286 3 4 "c = 2 AND a >= 3" {i46 (c=? AND a>?)}
287 4 1 "c = 2 AND a = 1 AND b<10" {i46 (c=? AND a=? AND b<?)}
288 5 1 "c = 0 AND a = 0 AND b>5" {i46 (c=? AND a=? AND b>?)}
289 }
290
291 foreach {tn cnt where eqp} $queries {
292 do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
293 }
294
295 do_execsql_test 5.6 {
296 CREATE INDEX i46 ON t46(c);
297 }
298
299 foreach {tn cnt where eqp} $queries {
300 do_execsql_test 5.7.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
301 do_eqp_test 5.7.$tn.2 "SELECT count(*) FROM t46 WHERE $where" $eqp
302 }
303
304 #-------------------------------------------------------------------------
305 # Check that redundant UNIQUE constraints do not cause a problem.
306 #
307 do_execsql_test 6.0 {
308 CREATE TABLE t47(a, b UNIQUE PRIMARY KEY) WITHOUT ROWID;
309 CREATE INDEX i47 ON t47(a);
310 INSERT INTO t47 VALUES(1, 2);
311 INSERT INTO t47 VALUES(2, 4);
312 INSERT INTO t47 VALUES(3, 6);
313 INSERT INTO t47 VALUES(4, 8);
314
315 VACUUM;
316 PRAGMA integrity_check;
317 SELECT name FROM sqlite_master WHERE tbl_name = 't47';
318 } {ok t47 i47}
319
320 do_execsql_test 6.1 {
321 CREATE TABLE t48(
322 a UNIQUE UNIQUE,
323 b UNIQUE,
324 PRIMARY KEY(a),
325 UNIQUE(a)
326 ) WITHOUT ROWID;
327 INSERT INTO t48 VALUES('a', 'b'), ('c', 'd'), ('e', 'f');
328 VACUUM;
329 PRAGMA integrity_check;
330 SELECT name FROM sqlite_master WHERE tbl_name = 't48';
331 } {
332 ok t48 sqlite_autoindex_t48_2
333 }
334
335 # 2015-05-28: CHECK constraints can refer to the rowid in a
336 # rowid table, but not in a WITHOUT ROWID table.
337 #
338 do_execsql_test 7.1 {
339 CREATE TABLE t70a(
340 a INT CHECK( rowid!=33 ),
341 b TEXT PRIMARY KEY
342 );
343 INSERT INTO t70a(a,b) VALUES(99,'hello');
344 } {}
345 do_catchsql_test 7.2 {
346 INSERT INTO t70a(rowid,a,b) VALUES(33,99,'xyzzy');
347 } {1 {CHECK constraint failed: rowid!=33}}
348 do_catchsql_test 7.3 {
349 CREATE TABLE t70b(
350 a INT CHECK( rowid!=33 ),
351 b TEXT PRIMARY KEY
352 ) WITHOUT ROWID;
353 } {1 {no such column: rowid}}
354
355 # 2017-07-30: OSSFuzz discovered that an extra entry was being
356 # added in the sqlite_master table for an "INTEGER PRIMARY KEY UNIQUE"
357 # WITHOUT ROWID table. Make sure this has now been fixed.
358 #
359 db close
360 sqlite3 db :memory:
361 do_execsql_test 8.1 {
362 CREATE TABLE t1(x INTEGER PRIMARY KEY UNIQUE, b) WITHOUT ROWID;
363 CREATE INDEX t1x ON t1(x);
364 INSERT INTO t1(x,b) VALUES('funny','buffalo');
365 SELECT type, name, '|' FROM sqlite_master;
366 } {table t1 | index t1x |}
367
368 # 2018-04-05: OSSFuzz found that the following was accessing an
369 # unintialized memory cell. Which was not actually causing a
370 # malfunction, but does cause an assert() to fail.
371 #
372 do_execsql_test 9.0 {
373 CREATE TABLE t2(b, c, PRIMARY KEY(b,c)) WITHOUT ROWID;
374 CREATE UNIQUE INDEX t2b ON t2(b);
375 UPDATE t2 SET b=1 WHERE b='';
376 }
377
378 do_execsql_test 10.1 {
379 DELETE FROM t2 WHERE b=1
380 }
381
382 #-------------------------------------------------------------------------
383 # UNIQUE constraint violation in an UPDATE with a multi-column PK.
384 #
385 reset_db
386 do_execsql_test 10.0 {
387 CREATE TABLE t1(a, b, c UNIQUE, PRIMARY KEY(a, b)) WITHOUT ROWID;
388 INSERT INTO t1 VALUES('a', 'a', 1);
389 INSERT INTO t1 VALUES('a', 'b', 2);
390 INSERT INTO t1 VALUES('b', 'a', 3);
391 INSERT INTO t1 VALUES('b', 'b', 4);
392 }
393
394 do_catchsql_test 10.1 {
395 UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'a');
396 } {0 {}}
397 do_catchsql_test 10.2 {
398 UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'b');
399 } {1 {UNIQUE constraint failed: t1.c}}
400 do_catchsql_test 10.3 {
401 UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'a');
402 } {1 {UNIQUE constraint failed: t1.c}}
403 do_catchsql_test 10.4 {
404 UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'b');
405 } {1 {UNIQUE constraint failed: t1.c}}
406 do_catchsql_test 10.5 {
407 UPDATE t1 SET c=1 WHERE (a, b) = ('c', 'c');
408 } {0 {}}
409
410 do_execsql_test 10.6 {
411 CREATE TRIGGER t1_tr BEFORE UPDATE ON t1 BEGIN
412 DELETE FROM t1 WHERE a = new.a;
413 END;
414 UPDATE t1 SET c = c+1 WHERE a = 'a';
415 SELECT * FROM t1;
416 } {b a 3 b b 4}
417
418 # 2019-04-29 ticket https://sqlite.org/src/info/3182d3879020ef3
419 do_execsql_test 11.1 {
420 CREATE TABLE t11(a TEXT PRIMARY KEY, b INT) WITHOUT ROWID;
421 CREATE INDEX t11a ON t11(a COLLATE NOCASE);
422 INSERT INTO t11(a,b) VALUES ('A',1),('a',2);
423 PRAGMA integrity_check;
424 SELECT a FROM t11 ORDER BY a COLLATE binary;
425 } {ok A a}
426
427 # 2019-05-13 ticket https://sqlite.org/src/info/bba7b69f9849b5b
428 do_execsql_test 12.1 {
429 DROP TABLE IF EXISTS t0;
430 CREATE TABLE t0 (c0 INTEGER PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID;
431 INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
432 REINDEX;
433 PRAGMA integrity_check;
434 } {ok}
435
436 # 2019-11-07 ticket https://sqlite.org/src/info/302027baf1374498
437 # The xferCompatibleIndex() function confuses a PRIMARY KEY index
438 # with a UNIQUE index.
439 #
440 do_execsql_test 13.10 {
441 DROP TABLE IF EXISTS t0;
442 DROP TABLE IF EXISTS t1;
443 CREATE TABLE t0(
444 c0,
445 c1 UNIQUE,
446 PRIMARY KEY(c1, c1)
447 ) WITHOUT ROWID;
448 INSERT INTO t0(c0,c1) VALUES('abc','xyz');
449 CREATE TABLE t1(
450 c0,
451 c1 UNIQUE,
452 PRIMARY KEY(c1, c1)
453 ) WITHOUT ROWID;
454 INSERT INTO t1 SELECT * FROM t0;
455 PRAGMA integrity_check;
456 SELECT * FROM t0, t1;
457 } {ok abc xyz abc xyz}
458
459 # 2021-05-13 https://sqlite.org/forum/forumpost/6c8960f545
460 reset_db
461 ifcapable altertable {
462 do_execsql_test 14.1 {
463 CREATE TABLE t1(a INT PRIMARY KEY) WITHOUT ROWID;
464 INSERT INTO t1(a) VALUES(10);
465 ALTER TABLE t1 ADD COLUMN b INT;
466 SELECT * FROM t1 WHERE a=20 OR (a=10 AND b=10);
467 } {}
468 do_execsql_test 14.2 {
469 CREATE TABLE dual AS SELECT 'X' AS dummy;
470 EXPLAIN QUERY PLAN SELECT * FROM dual, t1 WHERE a=10 AND b=10;
471 } {~/b=/}
472 }
473
474 # 2022-01-01 https://sqlite.org/forum/forumpost/b03d86f951 PoC #1
475 # Omit an assert() from 2013 that no longer serves any purpose and
476 # is no longer always true.
477 #
478 ifcapable altertable {
479 reset_db
480 do_execsql_test 15.1 {
481 PRAGMA writable_schema=ON;
482 CREATE TABLE sqlite_sequence (name PRIMARY KEY) WITHOUT ROWID;
483 PRAGMA writable_schema=OFF;
484 CREATE TABLE c1(x);
485 INSERT INTO sqlite_sequence(name) VALUES('c0'),('c1'),('c2');
486 ALTER TABLE c1 RENAME TO a;
487 SELECT name FROM sqlite_sequence ORDER BY +name;
488 } {a c0 c2}
489 }
490
491 # Ensure that the number of columns in an index on a WITHOUT ROWID
492 # table does not exceed SQLITE_LIMIT_COLUMN.
493 #
494 reset_db
495 sqlite3_limit db SQLITE_LIMIT_COLUMN 8
496 do_execsql_test 16.1 {
497 CREATE TABLE t1(
498 c1,c2,c3,c4,c5,c6,c7,c8,
499 PRIMARY KEY(c1,c2,c1 COLLATE NOCASE)
500 ) WITHOUT ROWID;
501 } {}
502 do_execsql_test 16.2 {
503 CREATE TABLE t2(
504 c1,c2,c3,c4,c5,c6,c7,c8,
505 PRIMARY KEY(c1 COLLATE nocase,c1 COLLATE rtrim,
506 c2 COLLATE nocase,c2 COLLATE rtrim,
507 c3 COLLATE nocase,c3 COLLATE rtrim,
508 c4 COLLATE nocase,c4 COLLATE rtrim)
509 ) WITHOUT ROWID;
510 } {}
511 do_execsql_test 16.3 {
512 CREATE TABLE t3(
513 c1,c2,c3,c4,c5,c6,c7,c8,
514 PRIMARY KEY(c1,c2),
515 UNIQUE(c3,c4,c5,c6,c7,c8,c3 COLLATE nocase)
516 ) WITHOUT ROWID;
517 } {}
518
519 finish_test