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 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Mimic the SQLite 2 collation type NUMERIC. 21db collate numeric numeric_collate 22proc 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_collate 29proc 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# 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 $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 51do_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 $cmd 62 } 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} 375 71do_test misc1-1.5 { 72 execsql {SELECT x50 FROM manycol WHERE x99=599} 73} 550 74do_test misc1-1.6 { 75 execsql {CREATE INDEX manycol_idx1 ON manycol(x99)} 76 execsql {SELECT x50 FROM manycol WHERE x99=899} 77} 850 78do_test misc1-1.7 { 79 execsql {SELECT count(*) FROM manycol} 80} 11 81do_test misc1-1.8 { 82 execsql {DELETE FROM manycol WHERE x98=1234} 83 execsql {SELECT count(*) FROM manycol} 84} 11 85do_test misc1-1.9 { 86 execsql {DELETE FROM manycol WHERE x98=998} 87 execsql {SELECT count(*) FROM manycol} 88} 10 89do_test misc1-1.10 { 90 execsql {DELETE FROM manycol WHERE x99=500} 91 execsql {SELECT count(*) FROM manycol} 92} 10 93do_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# 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 COMMIT 111 } 112 execsql {SELECT count(*) FROM agger} 113} 6 114do_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} 118do_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# 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 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# 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 input 157# 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") 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# 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, view 188 ); 189 } 190} {0 {}} 191do_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 {}} 197do_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} 202do_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# 210do_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}} 218do_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}} 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 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# 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" ::where 301 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" ::where 314 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 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. 331do_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} 339do_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# 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 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}} 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 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# 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 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# 478ifcapable curdir { 479# Make sure a database connection still works after changing the 480# working directory. 481# 482if {[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# 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 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# 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 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 591do_test misc1-18.1 { 592 set n [sqlite3_sleep 100] 593 expr {$n>=100} 594} {1} 595 596# 2014-01-10: In a CREATE TABLE AS, if one or more of the column names 597# are an empty string, that is still OK. 598# 599do_execsql_test misc1-19.1 { 600 CREATE TABLE t19 AS SELECT 1, 2 AS '', 3; 601 SELECT * FROM t19; 602} {1 2 3} 603do_execsql_test misc1-19.2 { 604 CREATE TABLE t19b AS SELECT 4 AS '', 5 AS '', 6 AS ''; 605 SELECT * FROM t19b; 606} {4 5 6} 607 608# 2015-05-20: CREATE TABLE AS should not store INT value is a TEXT 609# column. 610# 611do_execsql_test misc1-19.3 { 612 CREATE TABLE t19c(x TEXT); 613 CREATE TABLE t19d AS SELECT * FROM t19c UNION ALL SELECT 1234; 614 SELECT x, typeof(x) FROM t19d; 615} {1234 text} 616 617# 2014-05-16: Tests for the SQLITE_TESTCTRL_FAULT_INSTALL feature. 618# 619unset -nocomplain fault_callbacks 620set fault_callbacks {} 621proc fault_callback {n} { 622 lappend ::fault_callbacks $n 623 return 0 624} 625do_test misc1-19.1 { 626 sqlite3_test_control_fault_install fault_callback 627 set fault_callbacks 628} {0} 629do_test misc1-19.2 { 630 sqlite3_test_control_fault_install 631 set fault_callbacks 632} {0} 633 634# 2015-01-26: Valgrind-detected over-read. 635# Reported on [email protected] by Michal Zalewski. Found by afl-fuzz 636# presumably. 637# 638do_execsql_test misc1-20.1 { 639 CREATE TABLE t0(x INTEGER DEFAULT(0==0) NOT NULL); 640 REPLACE INTO t0(x) VALUES(''); 641 SELECT rowid, quote(x) FROM t0; 642} {1 ''} 643 644# 2015-03-22: NULL pointer dereference after a syntax error 645# 646do_catchsql_test misc1-21.1 { 647 select''like''like''like#0; 648} {1 {near "#0": syntax error}} 649do_catchsql_test misc1-21.2 { 650 VALUES(0,0x0MATCH#0; 651} {1 {near ";": syntax error}} 652 653# 2015-04-15 654do_execsql_test misc1-22.1 { 655 SELECT ""+3 FROM (SELECT ""+5); 656} {3} 657 658# 2015-04-19: NULL pointer dereference on a corrupt schema 659# 660db close 661sqlite3 db :memory: 662do_execsql_test misc1-23.1 { 663 CREATE TABLE t1(x); 664 PRAGMA writable_schema=ON; 665 UPDATE sqlite_master SET sql='CREATE table t(d CHECK(T(#0)'; 666 BEGIN; 667 CREATE TABLE t2(y); 668 ROLLBACK; 669 DROP TABLE IF EXISTS t3; 670} {} 671 672# 2015-04-19: Faulty assert() statement 673# 674db close 675database_may_be_corrupt 676sqlite3 db :memory: 677do_catchsql_test misc1-23.2 { 678 CREATE TABLE t1(x UNIQUE); 679 PRAGMA writable_schema=ON; 680 UPDATE sqlite_master SET sql='CREATE TABLE IF not EXISTS t(c)'; 681 BEGIN; 682 CREATE TABLE t2(x); 683 ROLLBACK; 684 DROP TABLE F; 685} {1 {no such table: F}} 686db close 687sqlite3 db :memory: 688do_catchsql_test misc1-23.3 { 689 CREATE TABLE t1(x UNIQUE); 690 PRAGMA writable_schema=ON; 691 UPDATE sqlite_master SET sql='CREATE table y(a TEXT, a TEXT)'; 692 BEGIN; 693 CREATE TABLE t2(y); 694 ROLLBACK; 695 DROP TABLE IF EXISTS t; 696} {0 {}} 697 698 699# At one point, running this would read one byte passed the end of a 700# buffer, upsetting valgrind. 701# 702do_test misc1-24.0 { 703 list [catch { sqlite3_prepare_v2 db ! -1 dummy } msg] $msg 704} {1 {(1) unrecognized token: "!"}} 705 706# The following query (provided by Kostya Serebryany) used to take 25 707# minutes to prepare. This has been speeded up to about 250 milliseconds. 708# 709do_catchsql_test misc1-25.0 { 710SELECT-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 711$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); 712} {1 {'k' is not a function}} 713 714 715finish_test 716