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: 662sqlite3_db_config db DEFENSIVE 0 663do_execsql_test misc1-23.1 { 664 CREATE TABLE t1(x); 665 PRAGMA writable_schema=ON; 666 UPDATE sqlite_master SET sql='CREATE table t(d CHECK(T(#0)'; 667 BEGIN; 668 CREATE TABLE t2(y); 669 ROLLBACK; 670 DROP TABLE IF EXISTS t3; 671} {} 672 673# 2015-04-19: Faulty assert() statement 674# 675db close 676database_may_be_corrupt 677sqlite3 db :memory: 678sqlite3_db_config db DEFENSIVE 0 679do_catchsql_test misc1-23.2 { 680 CREATE TABLE t1(x UNIQUE); 681 PRAGMA writable_schema=ON; 682 UPDATE sqlite_master SET sql='CREATE TABLE IF not EXISTS t(c)'; 683 BEGIN; 684 CREATE TABLE t2(x); 685 ROLLBACK; 686 DROP TABLE F; 687} {1 {no such table: F}} 688db close 689sqlite3 db :memory: 690sqlite3_db_config db DEFENSIVE 0 691do_catchsql_test misc1-23.3 { 692 CREATE TABLE t1(x UNIQUE); 693 PRAGMA writable_schema=ON; 694 UPDATE sqlite_master SET sql='CREATE table y(a TEXT, a TEXT)'; 695 BEGIN; 696 CREATE TABLE t2(y); 697 ROLLBACK; 698 DROP TABLE IF EXISTS t; 699} {0 {}} 700 701 702# At one point, running this would read one byte passed the end of a 703# buffer, upsetting valgrind. 704# 705do_test misc1-24.0 { 706 list [catch { sqlite3_prepare_v2 db ! -1 dummy } msg] $msg 707} {1 {(1) unrecognized token: "!"}} 708 709# The following query (provided by Kostya Serebryany) used to take 25 710# minutes to prepare. This has been speeded up to about 250 milliseconds. 711# 712do_catchsql_test misc1-25.0 { 713SELECT-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 714$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); 715} {1 {'k' is not a function}} 716 717# 2017-09-17 718# 719# Sometimes sqlite3ExprListAppend() can be invoked on an ExprList that 720# was obtained from sqlite3ExprListDup(). 721# 722do_execsql_test misc1-26.0 { 723 DROP TABLE IF EXISTS abc; 724 CREATE TABLE abc(a, b, c); 725 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; 726} {} 727 728# 2017-12-29 729# 730# The following behaviors (duplicate column names on an INSERT or UPDATE) 731# are undocumented. <<--- Not so. There is a long-standing requirement 732# in lang_update.in to say that when the columns to be updated appear more 733# than once in an UPDATE statement that only the rightmost expression is used. 734# See e_update-1.6.* for the tests. This is unfortunate, since omitting 735# that requirement would greatly simplify the fix to the problem identified 736# by forum post https://sqlite.org/forum/info/16ca0e9f32c38567 737# 738# These tests are added to ensure that historical behavior 739# does not change accidentally. 740# 741# For duplication columns on an INSERT, the first value is used. 742# For duplication columns on an UPDATE, the last value is used. 743# 744do_execsql_test misc1-27.0 { 745 CREATE TABLE dup1(a,b,c); 746 INSERT INTO dup1(a,b,c,a,b,c) VALUES(1,2,3,4,5,6); 747 SELECT a,b,c FROM dup1; 748} {1 2 3} 749do_execsql_test misc1-27.1 { 750 UPDATE dup1 SET a=7, b=8, c=9, a=10, b=11, c=12; 751 SELECT a,b,c FROM dup1; 752} {10 11 12} 753 754# 2018-12-20 755# 756# The Cursor.seekOp debugging value set incorrectly 757# in OP_NotExists. 758# 759sqlite3 db :memory: 760do_execsql_test misc1-28.0 { 761 CREATE TABLE t1(x); 762 CREATE UNIQUE INDEX t1x ON t1(x) WHERE x=1; 763 INSERT OR ABORT INTO t1 DEFAULT VALUES; 764 UPDATE OR REPLACE t1 SET x = 1; 765 PRAGMA integrity_check; 766 SELECT * FROM t1; 767} {ok 1} 768 769finish_test 770