AryaWu/sqlite
0
1# 2001 September 15.2#3# The author disclaims copyright to this source code. In place of4# 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 were14# left out of other test files.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# Mimic the SQLite 2 collation type NUMERIC.21db collate numeric numeric_collate22proc 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.28db collate text text_collate29proc numeric_collate {lhs rhs} {30 return [string compare $lhs $rhs]31}32 33# Test the creation and use of tables that have a large number34# of columns.35#36do_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 $cmd43 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 $cmd49 execsql "SELECT x99 FROM manycol"50} 9951do_test misc1-1.2 {52 execsql {SELECT x0, x10, x25, x50, x75 FROM manycol}53} {0 10 25 50 75}54do_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 $cmd62 }63 execsql {SELECT x50 FROM manycol ORDER BY x80+0}64} {50 150 250 350 450 550 650 750 850 950 1050}65do_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}68do_test misc1-1.4 {69 execsql {SELECT x75 FROM manycol WHERE x50=350}70} 37571do_test misc1-1.5 {72 execsql {SELECT x50 FROM manycol WHERE x99=599}73} 55074do_test misc1-1.6 {75 execsql {CREATE INDEX manycol_idx1 ON manycol(x99)}76 execsql {SELECT x50 FROM manycol WHERE x99=899}77} 85078do_test misc1-1.7 {79 execsql {SELECT count(*) FROM manycol}80} 1181do_test misc1-1.8 {82 execsql {DELETE FROM manycol WHERE x98=1234}83 execsql {SELECT count(*) FROM manycol}84} 1185do_test misc1-1.9 {86 execsql {DELETE FROM manycol WHERE x98=998}87 execsql {SELECT count(*) FROM manycol}88} 1089do_test misc1-1.10 {90 execsql {DELETE FROM manycol WHERE x99=500}91 execsql {SELECT count(*) FROM manycol}92} 1093do_test misc1-1.11 {94 execsql {DELETE FROM manycol WHERE x99=599}95 execsql {SELECT count(*) FROM manycol}96} 997 98# Check GROUP BY expressions that name two or more columns.99#100do_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 COMMIT111 }112 execsql {SELECT count(*) FROM agger}113} 6114do_test misc1-2.2 {115 execsql {SELECT sum(one), two, four FROM agger116 GROUP BY two, four ORDER BY sum(one) desc}117} {8 two no 6 one yes 4 two yes 3 thr yes}118do_test misc1-2.3 {119 execsql {SELECT sum((one)), (two), (four) FROM agger120 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 below124# was causing an assertion failure.125#126do_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 1134} {hi}135 136# Here's a test for yet another bug found by Joel Lucsy. The code137# below was causing an assertion failure.138#139do_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 input157# before executing a command. Thus if "WHERE" is misspelled on an UPDATE,158# the user won't accidently update every record.159#160do_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}}168do_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") can175# also be used as identifiers. The way this works in the parser is that176# the parser first detects a syntax error, the error handling routine177# sees that the special keyword caused the error, then replaces the keyword178# with "ID" and tries again.179#180# Check the operation of this logic.181#182do_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, view188 );189 }190} {0 {}}191do_test misc1-6.2 {192 catchsql {193 INSERT INTO t4194 VALUES(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);195 }196} {0 {}}197do_test misc1-6.3 {198 execsql {199 SELECT * FROM t4200 }201} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19}202do_test misc1-6.4 {203 execsql {204 SELECT abort+asc,max(key,pragma,temp) FROM t4205 }206} {3 17}207 208# Test for multi-column primary keys, and for multiple primary keys.209#210do_test misc1-7.1 {211 catchsql {212 CREATE TABLE error1(213 a TYPE PRIMARY KEY,214 b TYPE PRIMARY KEY215 );216 }217} {1 {table "error1" has more than one primary key}}218do_test misc1-7.2 {219 catchsql {220 CREATE TABLE error1(221 a INTEGER PRIMARY KEY,222 b TYPE PRIMARY KEY223 );224 }225} {1 {table "error1" has more than one primary key}}226do_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}233do_test misc1-7.4 {234 catchsql {235 INSERT INTO t5 VALUES(1,2,4);236 }237} {1 {UNIQUE constraint failed: t5.a, t5.b}}238do_test misc1-7.5 {239 catchsql {240 INSERT INTO t5 VALUES(0,2,4);241 }242} {0 {}}243do_test misc1-7.6 {244 execsql {245 SELECT * FROM t5 ORDER BY a;246 }247} {0 2 4 1 2 3}248 249do_test misc1-8.1 {250 catchsql {251 SELECT *;252 }253} {1 {no tables specified}}254do_test misc1-8.2 {255 catchsql {256 SELECT t1.*;257 }258} {1 {no such table: t1}}259 260execsql {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#269do_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 to280# make sure this limit is enforced.281#282# 2005-07-16: There is no longer a limit on the number of terms in a283# WHERE clause. But keep these tests just so that we have some tests284# that use a large number of terms in the WHERE clause.285#286do_test misc1-10.0 {287 execsql {SELECT count(*) FROM manycol}288} {9}289do_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}296do_test misc1-10.2 {297 catchsql "SELECT count(*) FROM manycol $::where AND rowid>0"298} {0 9}299do_test misc1-10.3 {300 regsub "x0>=0" $::where "x0=0" ::where301 catchsql "DELETE FROM manycol $::where"302} {0 {}}303do_test misc1-10.4 {304 execsql {SELECT count(*) FROM manycol}305} {8}306do_test misc1-10.5 {307 catchsql "DELETE FROM manycol $::where AND rowid>0"308} {0 {}}309do_test misc1-10.6 {310 execsql {SELECT x1 FROM manycol WHERE x0=100}311} {101}312do_test misc1-10.7 {313 regsub "x0=0" $::where "x0=100" ::where314 catchsql "UPDATE manycol SET x1=x1+1 $::where"315} {0 {}}316do_test misc1-10.8 {317 execsql {SELECT x1 FROM manycol WHERE x0=100}318} {102}319do_test misc1-10.9 {320 catchsql "UPDATE manycol SET x1=x1+1 $::where AND rowid>0"321} {0 {}}322do_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 while327# another process has the database locked.328#329# Update for v3: The BEGIN doesn't lock the database so the schema is read330# and the SELECT returns successfully.331do_test misc1-11.1 {332 execsql {BEGIN}333 execsql {UPDATE t1 SET a=0 WHERE 0}334 sqlite3 db2 test.db335 set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]336 lappend rc $msg337# v2 result: {1 {database is locked}}338} {0 3}339do_test misc1-11.2 {340 execsql {COMMIT}341 set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]342 db2 close343 lappend rc $msg344} {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 earlier348# all comparisions where numeric if either operand looked like a number.349#350do_test misc1-12.1 {351 execsql {SELECT '0'=='0.0'}352} {0}353do_test misc1-12.2 {354 execsql {SELECT '0'==0.0}355} {0}356do_test misc1-12.3 {357 execsql {SELECT '12345678901234567890'=='12345678901234567891'}358} {0}359do_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}366ifcapable 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}380do_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}390do_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}395do_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 arbitrary402# identifiers can be used as a collating sequence. Collation is by text403# if the identifier contains "text", "blob", or "clob" and is numeric404# 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}}413do_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}423do_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}428do_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 of435# 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#439ifcapable 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 t9455 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 concept476# of a current directory.477#478ifcapable curdir {479# Make sure a database connection still works after changing the480# working directory.481#482if {[atomic_batch_write test.db]==0} {483 do_test misc1-14.1 {484 file mkdir tempdir485 cd tempdir486 execsql {BEGIN}487 file exists ./test.db-journal488 } {0}489 do_test misc1-14.2a {490 execsql {UPDATE t1 SET a=a||'x' WHERE 0}491 file exists ../test.db-journal492 } {0}493 do_test misc1-14.2b {494 execsql {UPDATE t1 SET a=a||'y' WHERE 1}495 file exists ../test.db-journal496 } {1}497 do_test misc1-14.3 {498 cd ..499 forcedelete tempdir500 execsql {COMMIT}501 file exists ./test.db-journal502 } {0}503}504}505 506# A failed create table should not leave the table in the internal507# data structures. Ticket #238.508#509do_test misc1-15.1.1 {510 catchsql {511 CREATE TABLE t10 AS SELECT c1;512 }513} {1 {no such column: c1}}514do_test misc1-15.1.2 {515 catchsql {516 CREATE TABLE t10 AS SELECT t9.c1;517 }518} {1 {no such column: t9.c1}}519do_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}}524do_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 with529 # the error "table t10 alread exists"530} {0 {}}531 532# Test for memory leaks when a CREATE TABLE containing a primary key533# fails. Ticket #249.534#535do_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 {}}541do_test misc1-16.2 {542 catchsql {543 CREATE TABLE test(a integer, primary key(a));544 }545} {1 {table test already exists}}546do_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}}551do_test misc1-16.4 {552 execsql {553 INSERT INTO test VALUES(1);554 SELECT rowid, a FROM test;555 }556} {1 1}557do_test misc1-16.5 {558 execsql {559 INSERT INTO test VALUES(5);560 SELECT rowid, a FROM test;561 }562} {1 1 5 5}563do_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 570ifcapable trigger&&tempdb {571# Ticket #333: Temp triggers that modify persistent tables.572#573do_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 BEGIN579 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 591do_test misc1-18.1 {592 set n [sqlite3_sleep 100]593 expr {$n>=100}594} {1}595do_test misc1-18.2 {596 sqlite3_sleep -100597} {0}598 599# 2014-01-10: In a CREATE TABLE AS, if one or more of the column names600# are an empty string, that is still OK.601#602do_execsql_test misc1-19.1 {603 CREATE TABLE t19 AS SELECT 1, 2 AS '', 3;604 SELECT * FROM t19;605} {1 2 3}606do_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 TEXT612# column.613#614# 2022-12-14: Change: The column is not TEXT if the AS SELECT is615# a compound with different types on each arm.616#617do_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#625unset -nocomplain fault_callbacks626set fault_callbacks {}627proc fault_callback {n} {628 lappend ::fault_callbacks $n629 return 0630}631do_test misc1-19.11 {632 sqlite3_test_control_fault_install fault_callback633 set fault_callbacks634} {0}635do_test misc1-19.12 {636 sqlite3_test_control_fault_install637 set fault_callbacks638} {0}639 640# 2015-01-26: Valgrind-detected over-read.641# Reported on sqlite-users@sqlite.org by Michal Zalewski. Found by afl-fuzz642# presumably.643#644do_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 error651#652do_catchsql_test misc1-21.1 {653 select''like''like''like#0;654} {1 {near "#0": syntax error}}655do_catchsql_test misc1-21.2 {656 VALUES(0,0x0MATCH#0;657} {1 {unrecognized token: "0x0MATCH"}}658 659# 2015-04-15660do_execsql_test misc1-22.1 {661 SELECT ''+3 FROM (SELECT ''+5);662} {3}663 664# 2015-04-19: NULL pointer dereference on a corrupt schema665#666db close667sqlite3 db :memory:668sqlite3_db_config db DEFENSIVE 0669do_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() statement680#681db close682database_may_be_corrupt683sqlite3 db :memory:684sqlite3_db_config db DEFENSIVE 0685do_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}}694db close695sqlite3 db :memory:696sqlite3_db_config db DEFENSIVE 0697do_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#711do_test misc1-24.0 {712 list [catch { sqlite3_prepare_v2 db ! -1 dummy } msg] $msg713} {1 {(1) unrecognized token: "!"}}714 715# The following query (provided by Kostya Serebryany) used to take 25716# minutes to prepare. This has been speeded up to about 250 milliseconds.717#718do_catchsql_test misc1-25.0 {719SELECT-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(( SELECT720$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-17724#725# Sometimes sqlite3ExprListAppend() can be invoked on an ExprList that726# was obtained from sqlite3ExprListDup().727#728do_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-29735#736# The following behaviors (duplicate column names on an INSERT or UPDATE)737# are undocumented. <<--- Not so. There is a long-standing requirement738# in lang_update.in to say that when the columns to be updated appear more739# 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 omitting741# that requirement would greatly simplify the fix to the problem identified742# by forum post https://sqlite.org/forum/info/16ca0e9f32c38567743#744# These tests are added to ensure that historical behavior745# 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#750do_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}755do_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-20761#762# The Cursor.seekOp debugging value set incorrectly763# in OP_NotExists.764#765sqlite3 db :memory:766do_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 775finish_test776