Debian Codegraph @

test/misc1.test

raw
1 # 2001 September 15.
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 # This file implements regression tests for SQLite library.
12 #
13 # This file implements tests for miscellanous features that were
14 # left out of other test files.
15 #
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19
20 # Mimic the SQLite 2 collation type NUMERIC.
21 db collate numeric numeric_collate
22 proc numeric_collate {lhs rhs} {
23 if {$lhs == $rhs} {return 0}
24 return [expr ($lhs>$rhs)?1:-1]
25 }
26
27 # Mimic the SQLite 2 collation type TEXT.
28 db collate text text_collate
29 proc numeric_collate {lhs rhs} {
30 return [string compare $lhs $rhs]
31 }
32
33 # Test the creation and use of tables that have a large number
34 # of columns.
35 #
36 do_test misc1-1.1 {
37 set cmd "CREATE TABLE manycol(x0 text"
38 for {set i 1} {$i<=99} {incr i} {
39 append cmd ",x$i text"
40 }
41 append cmd ")";
42 execsql $cmd
43 set cmd "INSERT INTO manycol VALUES(0"
44 for {set i 1} {$i<=99} {incr i} {
45 append cmd ",$i"
46 }
47 append cmd ")";
48 execsql $cmd
49 execsql "SELECT x99 FROM manycol"
50 } 99
51 do_test misc1-1.2 {
52 execsql {SELECT x0, x10, x25, x50, x75 FROM manycol}
53 } {0 10 25 50 75}
54 do_test misc1-1.3.1 {
55 for {set j 100} {$j<=1000} {incr j 100} {
56 set cmd "INSERT INTO manycol VALUES($j"
57 for {set i 1} {$i<=99} {incr i} {
58 append cmd ",[expr {$i+$j}]"
59 }
60 append cmd ")"
61 execsql $cmd
62 }
63 execsql {SELECT x50 FROM manycol ORDER BY x80+0}
64 } {50 150 250 350 450 550 650 750 850 950 1050}
65 do_test misc1-1.3.2 {
66 execsql {SELECT x50 FROM manycol ORDER BY x80}
67 } {1050 150 250 350 450 550 650 750 50 850 950}
68 do_test misc1-1.4 {
69 execsql {SELECT x75 FROM manycol WHERE x50=350}
70 } 375
71 do_test misc1-1.5 {
72 execsql {SELECT x50 FROM manycol WHERE x99=599}
73 } 550
74 do_test misc1-1.6 {
75 execsql {CREATE INDEX manycol_idx1 ON manycol(x99)}
76 execsql {SELECT x50 FROM manycol WHERE x99=899}
77 } 850
78 do_test misc1-1.7 {
79 execsql {SELECT count(*) FROM manycol}
80 } 11
81 do_test misc1-1.8 {
82 execsql {DELETE FROM manycol WHERE x98=1234}
83 execsql {SELECT count(*) FROM manycol}
84 } 11
85 do_test misc1-1.9 {
86 execsql {DELETE FROM manycol WHERE x98=998}
87 execsql {SELECT count(*) FROM manycol}
88 } 10
89 do_test misc1-1.10 {
90 execsql {DELETE FROM manycol WHERE x99=500}
91 execsql {SELECT count(*) FROM manycol}
92 } 10
93 do_test misc1-1.11 {
94 execsql {DELETE FROM manycol WHERE x99=599}
95 execsql {SELECT count(*) FROM manycol}
96 } 9
97
98 # Check GROUP BY expressions that name two or more columns.
99 #
100 do_test misc1-2.1 {
101 execsql {
102 BEGIN TRANSACTION;
103 CREATE TABLE agger(one text, two text, three text, four text);
104 INSERT INTO agger VALUES(1, 'one', 'hello', 'yes');
105 INSERT INTO agger VALUES(2, 'two', 'howdy', 'no');
106 INSERT INTO agger VALUES(3, 'thr', 'howareya', 'yes');
107 INSERT INTO agger VALUES(4, 'two', 'lothere', 'yes');
108 INSERT INTO agger VALUES(5, 'one', 'atcha', 'yes');
109 INSERT INTO agger VALUES(6, 'two', 'hello', 'no');
110 COMMIT
111 }
112 execsql {SELECT count(*) FROM agger}
113 } 6
114 do_test misc1-2.2 {
115 execsql {SELECT sum(one), two, four FROM agger
116 GROUP BY two, four ORDER BY sum(one) desc}
117 } {8 two no 6 one yes 4 two yes 3 thr yes}
118 do_test misc1-2.3 {
119 execsql {SELECT sum((one)), (two), (four) FROM agger
120 GROUP BY (two), (four) ORDER BY sum(one) desc}
121 } {8 two no 6 one yes 4 two yes 3 thr yes}
122
123 # Here's a test for a bug found by Joel Lucsy. The code below
124 # was causing an assertion failure.
125 #
126 do_test misc1-3.1 {
127 set r [execsql {
128 CREATE TABLE t1(a);
129 INSERT INTO t1 VALUES('hi');
130 PRAGMA full_column_names=on;
131 SELECT rowid, * FROM t1;
132 }]
133 lindex $r 1
134 } {hi}
135
136 # Here's a test for yet another bug found by Joel Lucsy. The code
137 # below was causing an assertion failure.
138 #
139 do_test misc1-4.1 {
140 execsql {
141 BEGIN;
142 CREATE TABLE t2(a);
143 INSERT INTO t2 VALUES('This is a long string to use up a lot of disk -');
144 UPDATE t2 SET a=a||a||a||a;
145 INSERT INTO t2 SELECT '1 - ' || a FROM t2;
146 INSERT INTO t2 SELECT '2 - ' || a FROM t2;
147 INSERT INTO t2 SELECT '3 - ' || a FROM t2;
148 INSERT INTO t2 SELECT '4 - ' || a FROM t2;
149 INSERT INTO t2 SELECT '5 - ' || a FROM t2;
150 INSERT INTO t2 SELECT '6 - ' || a FROM t2;
151 COMMIT;
152 SELECT count(*) FROM t2;
153 }
154 } {64}
155
156 # Make sure we actually see a semicolon or end-of-file in the SQL input
157 # before executing a command. Thus if "WHERE" is misspelled on an UPDATE,
158 # the user won't accidently update every record.
159 #
160 do_test misc1-5.1 {
161 catchsql {
162 CREATE TABLE t3(a,b);
163 INSERT INTO t3 VALUES(1,2);
164 INSERT INTO t3 VALUES(3,4);
165 UPDATE t3 SET a=0 WHEREwww b=2;
166 }
167 } {1 {near "WHEREwww": syntax error}}
168 do_test misc1-5.2 {
169 execsql {
170 SELECT * FROM t3 ORDER BY a;
171 }
172 } {1 2 3 4}
173
174 # Certain keywords (especially non-standard keywords like "REPLACE") can
175 # also be used as identifiers. The way this works in the parser is that
176 # the parser first detects a syntax error, the error handling routine
177 # sees that the special keyword caused the error, then replaces the keyword
178 # with "ID" and tries again.
179 #
180 # Check the operation of this logic.
181 #
182 do_test misc1-6.1 {
183 catchsql {
184 CREATE TABLE t4(
185 abort, asc, begin, cluster, conflict, copy, delimiters, desc, end,
186 explain, fail, ignore, key, offset, pragma, replace, temp,
187 vacuum, view
188 );
189 }
190 } {0 {}}
191 do_test misc1-6.2 {
192 catchsql {
193 INSERT INTO t4
194 VALUES(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);
195 }
196 } {0 {}}
197 do_test misc1-6.3 {
198 execsql {
199 SELECT * FROM t4
200 }
201 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19}
202 do_test misc1-6.4 {
203 execsql {
204 SELECT abort+asc,max(key,pragma,temp) FROM t4
205 }
206 } {3 17}
207
208 # Test for multi-column primary keys, and for multiple primary keys.
209 #
210 do_test misc1-7.1 {
211 catchsql {
212 CREATE TABLE error1(
213 a TYPE PRIMARY KEY,
214 b TYPE PRIMARY KEY
215 );
216 }
217 } {1 {table "error1" has more than one primary key}}
218 do_test misc1-7.2 {
219 catchsql {
220 CREATE TABLE error1(
221 a INTEGER PRIMARY KEY,
222 b TYPE PRIMARY KEY
223 );
224 }
225 } {1 {table "error1" has more than one primary key}}
226 do_test misc1-7.3 {
227 execsql {
228 CREATE TABLE t5(a,b,c,PRIMARY KEY(a,b));
229 INSERT INTO t5 VALUES(1,2,3);
230 SELECT * FROM t5 ORDER BY a;
231 }
232 } {1 2 3}
233 do_test misc1-7.4 {
234 catchsql {
235 INSERT INTO t5 VALUES(1,2,4);
236 }
237 } {1 {UNIQUE constraint failed: t5.a, t5.b}}
238 do_test misc1-7.5 {
239 catchsql {
240 INSERT INTO t5 VALUES(0,2,4);
241 }
242 } {0 {}}
243 do_test misc1-7.6 {
244 execsql {
245 SELECT * FROM t5 ORDER BY a;
246 }
247 } {0 2 4 1 2 3}
248
249 do_test misc1-8.1 {
250 catchsql {
251 SELECT *;
252 }
253 } {1 {no tables specified}}
254 do_test misc1-8.2 {
255 catchsql {
256 SELECT t1.*;
257 }
258 } {1 {no such table: t1}}
259
260 execsql {
261 DROP TABLE t1;
262 DROP TABLE t2;
263 DROP TABLE t3;
264 DROP TABLE t4;
265 }
266
267 # 64-bit integers are represented exactly.
268 #
269 do_test misc1-9.1 {
270 catchsql {
271 CREATE TABLE t1(a unique not null, b unique not null);
272 INSERT INTO t1 VALUES('a',1234567890123456789);
273 INSERT INTO t1 VALUES('b',1234567891123456789);
274 INSERT INTO t1 VALUES('c',1234567892123456789);
275 SELECT * FROM t1;
276 }
277 } {0 {a 1234567890123456789 b 1234567891123456789 c 1234567892123456789}}
278
279 # A WHERE clause is not allowed to contain more than 99 terms. Check to
280 # make sure this limit is enforced.
281 #
282 # 2005-07-16: There is no longer a limit on the number of terms in a
283 # WHERE clause. But keep these tests just so that we have some tests
284 # that use a large number of terms in the WHERE clause.
285 #
286 do_test misc1-10.0 {
287 execsql {SELECT count(*) FROM manycol}
288 } {9}
289 do_test misc1-10.1 {
290 set ::where {WHERE x0>=0}
291 for {set i 1} {$i<=99} {incr i} {
292 append ::where " AND x$i<>0"
293 }
294 catchsql "SELECT count(*) FROM manycol $::where"
295 } {0 9}
296 do_test misc1-10.2 {
297 catchsql "SELECT count(*) FROM manycol $::where AND rowid>0"
298 } {0 9}
299 do_test misc1-10.3 {
300 regsub "x0>=0" $::where "x0=0" ::where
301 catchsql "DELETE FROM manycol $::where"
302 } {0 {}}
303 do_test misc1-10.4 {
304 execsql {SELECT count(*) FROM manycol}
305 } {8}
306 do_test misc1-10.5 {
307 catchsql "DELETE FROM manycol $::where AND rowid>0"
308 } {0 {}}
309 do_test misc1-10.6 {
310 execsql {SELECT x1 FROM manycol WHERE x0=100}
311 } {101}
312 do_test misc1-10.7 {
313 regsub "x0=0" $::where "x0=100" ::where
314 catchsql "UPDATE manycol SET x1=x1+1 $::where"
315 } {0 {}}
316 do_test misc1-10.8 {
317 execsql {SELECT x1 FROM manycol WHERE x0=100}
318 } {102}
319 do_test misc1-10.9 {
320 catchsql "UPDATE manycol SET x1=x1+1 $::where AND rowid>0"
321 } {0 {}}
322 do_test misc1-10.10 {
323 execsql {SELECT x1 FROM manycol WHERE x0=100}
324 } {103}
325
326 # Make sure the initialization works even if a database is opened while
327 # another process has the database locked.
328 #
329 # Update for v3: The BEGIN doesn't lock the database so the schema is read
330 # and the SELECT returns successfully.
331 do_test misc1-11.1 {
332 execsql {BEGIN}
333 execsql {UPDATE t1 SET a=0 WHERE 0}
334 sqlite3 db2 test.db
335 set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
336 lappend rc $msg
337 # v2 result: {1 {database is locked}}
338 } {0 3}
339 do_test misc1-11.2 {
340 execsql {COMMIT}
341 set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
342 db2 close
343 lappend rc $msg
344 } {0 3}
345
346 # Make sure string comparisons really do compare strings in format4+.
347 # Similar tests in the format3.test file show that for format3 and earlier
348 # all comparisions where numeric if either operand looked like a number.
349 #
350 do_test misc1-12.1 {
351 execsql {SELECT '0'=='0.0'}
352 } {0}
353 do_test misc1-12.2 {
354 execsql {SELECT '0'==0.0}
355 } {0}
356 do_test misc1-12.3 {
357 execsql {SELECT '12345678901234567890'=='12345678901234567891'}
358 } {0}
359 do_test misc1-12.4 {
360 execsql {
361 CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
362 INSERT INTO t6 VALUES('0','0.0');
363 SELECT * FROM t6;
364 }
365 } {0 0.0}
366 ifcapable conflict {
367 do_test misc1-12.5 {
368 execsql {
369 INSERT OR IGNORE INTO t6 VALUES(0.0,'x');
370 SELECT * FROM t6;
371 }
372 } {0 0.0}
373 do_test misc1-12.6 {
374 execsql {
375 INSERT OR IGNORE INTO t6 VALUES('y',0);
376 SELECT * FROM t6;
377 }
378 } {0 0.0 y 0}
379 }
380 do_test misc1-12.7 {
381 execsql {
382 CREATE TABLE t7(x INTEGER, y TEXT, z);
383 INSERT INTO t7 VALUES(0,0,1);
384 INSERT INTO t7 VALUES(0.0,0,2);
385 INSERT INTO t7 VALUES(0,0.0,3);
386 INSERT INTO t7 VALUES(0.0,0.0,4);
387 SELECT DISTINCT x, y FROM t7 ORDER BY z;
388 }
389 } {0 0 0 0.0}
390 do_test misc1-12.8 {
391 execsql {
392 SELECT min(z), max(z), count(z) FROM t7 GROUP BY x ORDER BY 1;
393 }
394 } {1 4 4}
395 do_test misc1-12.9 {
396 execsql {
397 SELECT min(z), max(z), count(z) FROM t7 GROUP BY y ORDER BY 1;
398 }
399 } {1 2 2 3 4 2}
400
401 # This used to be an error. But we changed the code so that arbitrary
402 # identifiers can be used as a collating sequence. Collation is by text
403 # if the identifier contains "text", "blob", or "clob" and is numeric
404 # otherwise.
405 #
406 # Update: In v3, it is an error again.
407 #
408 #do_test misc1-12.10 {
409 # catchsql {
410 # SELECT * FROM t6 ORDER BY a COLLATE unknown;
411 # }
412 #} {0 {0 0 y 0}}
413 do_test misc1-12.11 {
414 execsql {
415 CREATE TABLE t8(x TEXT COLLATE numeric, y INTEGER COLLATE text, z);
416 INSERT INTO t8 VALUES(0,0,1);
417 INSERT INTO t8 VALUES(0.0,0,2);
418 INSERT INTO t8 VALUES(0,0.0,3);
419 INSERT INTO t8 VALUES(0.0,0.0,4);
420 SELECT DISTINCT x, y FROM t8 ORDER BY z;
421 }
422 } {0 0 0.0 0}
423 do_test misc1-12.12 {
424 execsql {
425 SELECT min(z), max(z), count(z) FROM t8 GROUP BY x ORDER BY 1;
426 }
427 } {1 3 2 2 4 2}
428 do_test misc1-12.13 {
429 execsql {
430 SELECT min(z), max(z), count(z) FROM t8 GROUP BY y ORDER BY 1;
431 }
432 } {1 4 4}
433
434 # There was a problem with realloc() in the OP_MemStore operation of
435 # the VDBE. A buffer was being reallocated but some pointers into
436 # the old copy of the buffer were not being moved over to the new copy.
437 # The following code tests for the problem.
438 #
439 ifcapable subquery {
440 do_test misc1-13.1 {
441 execsql {
442 CREATE TABLE t9(x,y);
443 INSERT INTO t9 VALUES('one',1);
444 INSERT INTO t9 VALUES('two',2);
445 INSERT INTO t9 VALUES('three',3);
446 INSERT INTO t9 VALUES('four',4);
447 INSERT INTO t9 VALUES('five',5);
448 INSERT INTO t9 VALUES('six',6);
449 INSERT INTO t9 VALUES('seven',7);
450 INSERT INTO t9 VALUES('eight',8);
451 INSERT INTO t9 VALUES('nine',9);
452 INSERT INTO t9 VALUES('ten',10);
453 INSERT INTO t9 VALUES('eleven',11);
454 SELECT y FROM t9
455 WHERE x=(SELECT x FROM t9 WHERE y=1)
456 OR x=(SELECT x FROM t9 WHERE y=2)
457 OR x=(SELECT x FROM t9 WHERE y=3)
458 OR x=(SELECT x FROM t9 WHERE y=4)
459 OR x=(SELECT x FROM t9 WHERE y=5)
460 OR x=(SELECT x FROM t9 WHERE y=6)
461 OR x=(SELECT x FROM t9 WHERE y=7)
462 OR x=(SELECT x FROM t9 WHERE y=8)
463 OR x=(SELECT x FROM t9 WHERE y=9)
464 OR x=(SELECT x FROM t9 WHERE y=10)
465 OR x=(SELECT x FROM t9 WHERE y=11)
466 OR x=(SELECT x FROM t9 WHERE y=12)
467 OR x=(SELECT x FROM t9 WHERE y=13)
468 OR x=(SELECT x FROM t9 WHERE y=14)
469 ;
470 }
471 } {1 2 3 4 5 6 7 8 9 10 11}
472 }
473
474 #
475 # The following tests can only work if the current SQLite VFS has the concept
476 # of a current directory.
477 #
478 ifcapable curdir {
479 # Make sure a database connection still works after changing the
480 # working directory.
481 #
482 if {[atomic_batch_write test.db]==0} {
483 do_test misc1-14.1 {
484 file mkdir tempdir
485 cd tempdir
486 execsql {BEGIN}
487 file exists ./test.db-journal
488 } {0}
489 do_test misc1-14.2a {
490 execsql {UPDATE t1 SET a=a||'x' WHERE 0}
491 file exists ../test.db-journal
492 } {0}
493 do_test misc1-14.2b {
494 execsql {UPDATE t1 SET a=a||'y' WHERE 1}
495 file exists ../test.db-journal
496 } {1}
497 do_test misc1-14.3 {
498 cd ..
499 forcedelete tempdir
500 execsql {COMMIT}
501 file exists ./test.db-journal
502 } {0}
503 }
504 }
505
506 # A failed create table should not leave the table in the internal
507 # data structures. Ticket #238.
508 #
509 do_test misc1-15.1.1 {
510 catchsql {
511 CREATE TABLE t10 AS SELECT c1;
512 }
513 } {1 {no such column: c1}}
514 do_test misc1-15.1.2 {
515 catchsql {
516 CREATE TABLE t10 AS SELECT t9.c1;
517 }
518 } {1 {no such column: t9.c1}}
519 do_test misc1-15.1.3 {
520 catchsql {
521 CREATE TABLE t10 AS SELECT main.t9.c1;
522 }
523 } {1 {no such column: main.t9.c1}}
524 do_test misc1-15.2 {
525 catchsql {
526 CREATE TABLE t10 AS SELECT 1;
527 }
528 # The bug in ticket #238 causes the statement above to fail with
529 # the error "table t10 alread exists"
530 } {0 {}}
531
532 # Test for memory leaks when a CREATE TABLE containing a primary key
533 # fails. Ticket #249.
534 #
535 do_test misc1-16.1 {
536 catchsql {SELECT name FROM sqlite_master LIMIT 1}
537 catchsql {
538 CREATE TABLE test(a integer, primary key(a));
539 }
540 } {0 {}}
541 do_test misc1-16.2 {
542 catchsql {
543 CREATE TABLE test(a integer, primary key(a));
544 }
545 } {1 {table test already exists}}
546 do_test misc1-16.3 {
547 catchsql {
548 CREATE TABLE test2(a text primary key, b text, primary key(a,b));
549 }
550 } {1 {table "test2" has more than one primary key}}
551 do_test misc1-16.4 {
552 execsql {
553 INSERT INTO test VALUES(1);
554 SELECT rowid, a FROM test;
555 }
556 } {1 1}
557 do_test misc1-16.5 {
558 execsql {
559 INSERT INTO test VALUES(5);
560 SELECT rowid, a FROM test;
561 }
562 } {1 1 5 5}
563 do_test misc1-16.6 {
564 execsql {
565 INSERT INTO test VALUES(NULL);
566 SELECT rowid, a FROM test;
567 }
568 } {1 1 5 5 6 6}
569
570 ifcapable trigger&&tempdb {
571 # Ticket #333: Temp triggers that modify persistent tables.
572 #
573 do_test misc1-17.1 {
574 execsql {
575 BEGIN;
576 CREATE TABLE RealTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
577 CREATE TEMP TABLE TempTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
578 CREATE TEMP TRIGGER trigTest_1 AFTER UPDATE ON TempTable BEGIN
579 INSERT INTO RealTable(TestString)
580 SELECT new.TestString FROM TempTable LIMIT 1;
581 END;
582 INSERT INTO TempTable(TestString) VALUES ('1');
583 INSERT INTO TempTable(TestString) VALUES ('2');
584 UPDATE TempTable SET TestString = TestString + 1 WHERE TestID=1 OR TestId=2;
585 COMMIT;
586 SELECT TestString FROM RealTable ORDER BY 1;
587 }
588 } {2 3}
589 }
590
591 do_test misc1-18.1 {
592 set n [sqlite3_sleep 100]
593 expr {$n>=100}
594 } {1}
595 do_test misc1-18.2 {
596 sqlite3_sleep -100
597 } {0}
598
599 # 2014-01-10: In a CREATE TABLE AS, if one or more of the column names
600 # are an empty string, that is still OK.
601 #
602 do_execsql_test misc1-19.1 {
603 CREATE TABLE t19 AS SELECT 1, 2 AS '', 3;
604 SELECT * FROM t19;
605 } {1 2 3}
606 do_execsql_test misc1-19.2 {
607 CREATE TABLE t19b AS SELECT 4 AS '', 5 AS '', 6 AS '';
608 SELECT * FROM t19b;
609 } {4 5 6}
610
611 # 2015-05-20: CREATE TABLE AS should not store INT value in a TEXT
612 # column.
613 #
614 # 2022-12-14: Change: The column is not TEXT if the AS SELECT is
615 # a compound with different types on each arm.
616 #
617 do_execsql_test misc1-19.3 {
618 CREATE TABLE t19c(x TEXT);
619 CREATE TABLE t19d AS SELECT * FROM t19c UNION ALL SELECT 1234;
620 SELECT x, typeof(x) FROM t19d;
621 } {1234 integer}
622
623 # 2014-05-16: Tests for the SQLITE_TESTCTRL_FAULT_INSTALL feature.
624 #
625 unset -nocomplain fault_callbacks
626 set fault_callbacks {}
627 proc fault_callback {n} {
628 lappend ::fault_callbacks $n
629 return 0
630 }
631 do_test misc1-19.11 {
632 sqlite3_test_control_fault_install fault_callback
633 set fault_callbacks
634 } {0}
635 do_test misc1-19.12 {
636 sqlite3_test_control_fault_install
637 set fault_callbacks
638 } {0}
639
640 # 2015-01-26: Valgrind-detected over-read.
641 # Reported on [email protected] by Michal Zalewski. Found by afl-fuzz
642 # presumably.
643 #
644 do_execsql_test misc1-20.1 {
645 CREATE TABLE t0(x INTEGER DEFAULT(0==0) NOT NULL);
646 REPLACE INTO t0(x) VALUES('');
647 SELECT rowid, quote(x) FROM t0;
648 } {1 ''}
649
650 # 2015-03-22: NULL pointer dereference after a syntax error
651 #
652 do_catchsql_test misc1-21.1 {
653 select''like''like''like#0;
654 } {1 {near "#0": syntax error}}
655 do_catchsql_test misc1-21.2 {
656 VALUES(0,0x0MATCH#0;
657 } {1 {unrecognized token: "0x0MATCH"}}
658
659 # 2015-04-15
660 do_execsql_test misc1-22.1 {
661 SELECT ''+3 FROM (SELECT ''+5);
662 } {3}
663
664 # 2015-04-19: NULL pointer dereference on a corrupt schema
665 #
666 db close
667 sqlite3 db :memory:
668 sqlite3_db_config db DEFENSIVE 0
669 do_execsql_test misc1-23.1 {
670 CREATE TABLE t1(x);
671 PRAGMA writable_schema=ON;
672 UPDATE sqlite_master SET sql='CREATE table t(d CHECK(T(#0)';
673 BEGIN;
674 CREATE TABLE t2(y);
675 ROLLBACK;
676 DROP TABLE IF EXISTS t3;
677 } {}
678
679 # 2015-04-19: Faulty assert() statement
680 #
681 db close
682 database_may_be_corrupt
683 sqlite3 db :memory:
684 sqlite3_db_config db DEFENSIVE 0
685 do_catchsql_test misc1-23.2 {
686 CREATE TABLE t1(x UNIQUE);
687 PRAGMA writable_schema=ON;
688 UPDATE sqlite_master SET sql='CREATE TABLE IF not EXISTS t(c)';
689 BEGIN;
690 CREATE TABLE t2(x);
691 ROLLBACK;
692 DROP TABLE F;
693 } {1 {no such table: F}}
694 db close
695 sqlite3 db :memory:
696 sqlite3_db_config db DEFENSIVE 0
697 do_catchsql_test misc1-23.3 {
698 CREATE TABLE t1(x UNIQUE);
699 PRAGMA writable_schema=ON;
700 UPDATE sqlite_master SET sql='CREATE table y(a TEXT, a TEXT)';
701 BEGIN;
702 CREATE TABLE t2(y);
703 ROLLBACK;
704 DROP TABLE IF EXISTS t;
705 } {0 {}}
706
707
708 # At one point, running this would read one byte passed the end of a
709 # buffer, upsetting valgrind.
710 #
711 do_test misc1-24.0 {
712 list [catch { sqlite3_prepare_v2 db ! -1 dummy } msg] $msg
713 } {1 {(1) unrecognized token: "!"}}
714
715 # The following query (provided by Kostya Serebryany) used to take 25
716 # minutes to prepare. This has been speeded up to about 250 milliseconds.
717 #
718 do_catchsql_test misc1-25.0 {
719 SELECT-1 UNION SELECT 5 UNION SELECT 0 UNION SElECT*from(SELECT-5) UNION SELECT*from(SELECT-0) UNION SELECT:SELECT-0 UNION SELECT-1 UNION SELECT 1 UNION SELECT 1 ORDER BY S in(WITH K AS(WITH K AS(select'CREINDERcharREADEVIRTUL5TABLECONFLICT !1 USIN'' MFtOR(b38q,eWITH K AS(selectCREATe TABLE t0(a,b,c,d,e, PRIMARY KEY(a,b,c,d,c,a,b,b,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,a,b,b,c,d,c,a,c,d,c,d,c,e,d,d,c,a,b,b,c,c,a,b,b,E,d,c,d,c,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,a,b,c,e,d,d,c,a,b,b,c,d,d,c,a,b,c,e,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,a,b,c,e,d,d,c,a,b,b,b,c,e,d,d,c,a,b,b,c,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,e,d,d,c,a,b,b,c,c,a,b,b,E,d,c,d,c,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d'CEIl,k'',ab, g, a,b,o11b, i'nEX/charREDE IVT LR!VABLt5SG',N ,N in rement,l_vacuum,M&U,'te3(''5l' a,bB,b,l*e)SELECT:SELECT, *,*,*from(( SELECT
720 $group,:conc ap0,1)fro,(select"",:PBAG,c,a,b,b,c,a,b,c,e,d,d,c,a,b,b,c,d,d,c,a,b,c,e,d,c,d,c,a,b,c,e,c,d,c,d,c,a,b,b,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,a,b,c,e,d,d,c,a,b,b,b,c,e,d,d,c,a,b,b,c,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,e,d,d,c,a,b,b,c,c,a,b,b,E,d,c,d,c,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,a,b,c,e,d,d,c,a,b,b,c,d,d,c,a,b,c,e,d,c,d,c,a,b,c,e,c,d,c,d,c,a,b,b,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,e,d,d,c,a,b,b,c,c,a,b,b,E,d,c,d,c,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,a,b,c,e,d,d,c,a,b,b,c,d,d,c,a,b,c,e,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,c,d,c,c,a,a,b,d,d,c,a,b,b,c,d,c,a,b,e,e,d,b,c,d,c,a,b,b,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d, foreign_keysc,d,c,e,d,d,c,a,b,b,c,c,a,b,b,E,d,c,d,c,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,a,b,d,d,c,a,b,b,c,d,c,a,b,e,e,d,b,c,d,c,a,b,b,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,a,b,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,e,d,d,c,a,b,b,c,c,a,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,a,b,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,e,d,d,c,a,b,b,c,c,a,b,b,E,d,c,d,c,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,a,b,c,e,d,d,c,a,b,b,c,d,d,c,a,b,c,e,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,c,d,c,a,b,d,d,c,a,a,b,d,d,c,a,b,b,c,d,c,a,b,e,e,d,b,c,d,c,a,b,b,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,e,d,d,c,a,b,b,c,c,a,b,b,E,d,c,d,c,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,a,b,d,d,c,a,b,b,c,d,c,a,b,e,e,d,b,c,d,c,a,b,b,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,c,a,b,b,c,d,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,d,c,e,d,d,c,a,b,b,c,c,a,b,b,E,d,c,d,c,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,a,bb,b,E,d,c,d,c,b,c,d,c,d,c,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,a,b,c,e,d,d,c,a,b,b,c,d,d,c,a,b,c,e,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,c,d,c,a,b,d,d,c,a,a,b,d,d,c,a,b,b,c,d,c,a,b,e,e,d,b,c,d,c,a,b,b,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,c,a,b,c,e,d,d,c,a,b,b,c,d,c,d,c,a,b,c,e,c,d,c,a,b,b,c,d,MAato_aecSELEC,+?b," "O,"i","a",""b ,5 ))KEY)SELECT*FROM((k()reaC,k,K) eA,k '' )t ,K M);
721 } {1 {'k' is not a function}}
722
723 # 2017-09-17
724 #
725 # Sometimes sqlite3ExprListAppend() can be invoked on an ExprList that
726 # was obtained from sqlite3ExprListDup().
727 #
728 do_execsql_test misc1-26.0 {
729 DROP TABLE IF EXISTS abc;
730 CREATE TABLE abc(a, b, c);
731 SELECT randomblob(min(max(coalesce(EXISTS (SELECT 1 FROM ( SELECT (SELECT 2147483647) NOT IN (SELECT 2147483649 UNION ALL SELECT DISTINCT -1) IN (SELECT 2147483649), 'fault', (SELECT ALL -1 INTERSECT SELECT 'experiments') IN (SELECT ALL 56.1 ORDER BY 'experiments' DESC) FROM (SELECT DISTINCT 2147483648, 'hardware' UNION ALL SELECT -2147483648, 'experiments' ORDER BY 2147483648 LIMIT 1 OFFSET 123456789.1234567899) GROUP BY (SELECT ALL 0 INTERSECT SELECT 'in') IN (SELECT DISTINCT 'experiments' ORDER BY zeroblob(1000) LIMIT 56.1 OFFSET -456) HAVING EXISTS (SELECT 'fault' EXCEPT SELECT DISTINCT 56.1) UNION SELECT 'The', 'The', 2147483649 UNION ALL SELECT DISTINCT 'hardware', 'first', 'experiments' ORDER BY 'hardware' LIMIT 123456789.1234567899 OFFSET -2147483647)) NOT IN (SELECT (SELECT DISTINCT (SELECT 'The') FROM abc ORDER BY EXISTS (SELECT -1 INTERSECT SELECT ALL NULL) ASC) IN (SELECT DISTINCT EXISTS (SELECT ALL 123456789.1234567899 ORDER BY 1 ASC, NULL DESC) FROM sqlite_master INTERSECT SELECT 456)), (SELECT ALL 'injection' UNION ALL SELECT ALL (SELECT DISTINCT 'first' UNION SELECT DISTINCT 'The') FROM (SELECT 456, 'in', 2147483649))),1), 500)), 'first', EXISTS (SELECT DISTINCT 456 FROM abc ORDER BY 'experiments' DESC) FROM abc;
732 } {}
733
734 # 2017-12-29
735 #
736 # The following behaviors (duplicate column names on an INSERT or UPDATE)
737 # are undocumented. <<--- Not so. There is a long-standing requirement
738 # in lang_update.in to say that when the columns to be updated appear more
739 # than once in an UPDATE statement that only the rightmost expression is used.
740 # See e_update-1.6.* for the tests. This is unfortunate, since omitting
741 # that requirement would greatly simplify the fix to the problem identified
742 # by forum post https://sqlite.org/forum/info/16ca0e9f32c38567
743 #
744 # These tests are added to ensure that historical behavior
745 # does not change accidentally.
746 #
747 # For duplication columns on an INSERT, the first value is used.
748 # For duplication columns on an UPDATE, the last value is used.
749 #
750 do_execsql_test misc1-27.0 {
751 CREATE TABLE dup1(a,b,c);
752 INSERT INTO dup1(a,b,c,a,b,c) VALUES(1,2,3,4,5,6);
753 SELECT a,b,c FROM dup1;
754 } {1 2 3}
755 do_execsql_test misc1-27.1 {
756 UPDATE dup1 SET a=7, b=8, c=9, a=10, b=11, c=12;
757 SELECT a,b,c FROM dup1;
758 } {10 11 12}
759
760 # 2018-12-20
761 #
762 # The Cursor.seekOp debugging value set incorrectly
763 # in OP_NotExists.
764 #
765 sqlite3 db :memory:
766 do_execsql_test misc1-28.0 {
767 CREATE TABLE t1(x);
768 CREATE UNIQUE INDEX t1x ON t1(x) WHERE x=1;
769 INSERT OR ABORT INTO t1 DEFAULT VALUES;
770 UPDATE OR REPLACE t1 SET x = 1;
771 PRAGMA integrity_check;
772 SELECT * FROM t1;
773 } {ok 1}
774
775 finish_test