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# $Id: misc1.test,v 1.27 2004/06/10 05:59:25 danielk1977 Exp $ 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# Mimic the SQLite 2 collation type NUMERIC. 22db collate numeric numeric_collate 23proc numeric_collate {lhs rhs} { 24 if {$lhs == $rhs} {return 0} 25 return [expr ($lhs>$rhs)?1:-1] 26} 27 28# Mimic the SQLite 2 collation type TEXT. 29db collate text text_collate 30proc numeric_collate {lhs rhs} { 31 return [string compare $lhs $rhs] 32} 33 34# Test the creation and use of tables that have a large number 35# of columns. 36# 37do_test misc1-1.1 { 38 set cmd "CREATE TABLE manycol(x0 text" 39 for {set i 1} {$i<=99} {incr i} { 40 append cmd ",x$i text" 41 } 42 append cmd ")"; 43 execsql $cmd 44 set cmd "INSERT INTO manycol VALUES(0" 45 for {set i 1} {$i<=99} {incr i} { 46 append cmd ",$i" 47 } 48 append cmd ")"; 49 execsql $cmd 50 execsql "SELECT x99 FROM manycol" 51} 99 52do_test misc1-1.2 { 53 execsql {SELECT x0, x10, x25, x50, x75 FROM manycol} 54} {0 10 25 50 75} 55do_test misc1-1.3.1 { 56 for {set j 100} {$j<=1000} {incr j 100} { 57 set cmd "INSERT INTO manycol VALUES($j" 58 for {set i 1} {$i<=99} {incr i} { 59 append cmd ",[expr {$i+$j}]" 60 } 61 append cmd ")" 62 execsql $cmd 63 } 64 execsql {SELECT x50 FROM manycol ORDER BY x80+0} 65} {50 150 250 350 450 550 650 750 850 950 1050} 66do_test misc1-1.3.2 { 67 execsql {SELECT x50 FROM manycol ORDER BY x80} 68} {1050 150 250 350 450 550 650 750 50 850 950} 69do_test misc1-1.4 { 70 execsql {SELECT x75 FROM manycol WHERE x50=350} 71} 375 72do_test misc1-1.5 { 73 execsql {SELECT x50 FROM manycol WHERE x99=599} 74} 550 75do_test misc1-1.6 { 76 execsql {CREATE INDEX manycol_idx1 ON manycol(x99)} 77 execsql {SELECT x50 FROM manycol WHERE x99=899} 78} 850 79do_test misc1-1.7 { 80 execsql {SELECT count(*) FROM manycol} 81} 11 82do_test misc1-1.8 { 83 execsql {DELETE FROM manycol WHERE x98=1234} 84 execsql {SELECT count(*) FROM manycol} 85} 11 86do_test misc1-1.9 { 87 execsql {DELETE FROM manycol WHERE x98=998} 88 execsql {SELECT count(*) FROM manycol} 89} 10 90do_test misc1-1.10 { 91 execsql {DELETE FROM manycol WHERE x99=500} 92 execsql {SELECT count(*) FROM manycol} 93} 10 94do_test misc1-1.11 { 95 execsql {DELETE FROM manycol WHERE x99=599} 96 execsql {SELECT count(*) FROM manycol} 97} 9 98 99# Check GROUP BY expressions that name two or more columns. 100# 101do_test misc1-2.1 { 102 execsql { 103 BEGIN TRANSACTION; 104 CREATE TABLE agger(one text, two text, three text, four text); 105 INSERT INTO agger VALUES(1, 'one', 'hello', 'yes'); 106 INSERT INTO agger VALUES(2, 'two', 'howdy', 'no'); 107 INSERT INTO agger VALUES(3, 'thr', 'howareya', 'yes'); 108 INSERT INTO agger VALUES(4, 'two', 'lothere', 'yes'); 109 INSERT INTO agger VALUES(5, 'one', 'atcha', 'yes'); 110 INSERT INTO agger VALUES(6, 'two', 'hello', 'no'); 111 COMMIT 112 } 113 execsql {SELECT count(*) FROM agger} 114} 6 115do_test misc1-2.2 { 116 execsql {SELECT sum(one), two, four FROM agger 117 GROUP BY two, four ORDER BY sum(one) desc} 118} {8 two no 6 one yes 4 two yes 3 thr yes} 119do_test misc1-2.3 { 120 execsql {SELECT sum((one)), (two), (four) FROM agger 121 GROUP BY (two), (four) ORDER BY sum(one) desc} 122} {8 two no 6 one yes 4 two yes 3 thr yes} 123 124# Here's a test for a bug found by Joel Lucsy. The code below 125# was causing an assertion failure. 126# 127do_test misc1-3.1 { 128 set r [execsql { 129 CREATE TABLE t1(a); 130 INSERT INTO t1 VALUES('hi'); 131 PRAGMA full_column_names=on; 132 SELECT rowid, * FROM t1; 133 }] 134 lindex $r 1 135} {hi} 136 137# Here's a test for yet another bug found by Joel Lucsy. The code 138# below was causing an assertion failure. 139# 140do_test misc1-4.1 { 141 execsql { 142 BEGIN; 143 CREATE TABLE t2(a); 144 INSERT INTO t2 VALUES('This is a long string to use up a lot of disk -'); 145 UPDATE t2 SET a=a||a||a||a; 146 INSERT INTO t2 SELECT '1 - ' || a FROM t2; 147 INSERT INTO t2 SELECT '2 - ' || a FROM t2; 148 INSERT INTO t2 SELECT '3 - ' || a FROM t2; 149 INSERT INTO t2 SELECT '4 - ' || a FROM t2; 150 INSERT INTO t2 SELECT '5 - ' || a FROM t2; 151 INSERT INTO t2 SELECT '6 - ' || a FROM t2; 152 COMMIT; 153 SELECT count(*) FROM t2; 154 } 155} {64} 156 157# Make sure we actually see a semicolon or end-of-file in the SQL input 158# before executing a command. Thus if "WHERE" is misspelled on an UPDATE, 159# the user won't accidently update every record. 160# 161do_test misc1-5.1 { 162 catchsql { 163 CREATE TABLE t3(a,b); 164 INSERT INTO t3 VALUES(1,2); 165 INSERT INTO t3 VALUES(3,4); 166 UPDATE t3 SET a=0 WHEREwww b=2; 167 } 168} {1 {near "WHEREwww": syntax error}} 169do_test misc1-5.2 { 170 execsql { 171 SELECT * FROM t3 ORDER BY a; 172 } 173} {1 2 3 4} 174 175# Certain keywords (especially non-standard keywords like "REPLACE") can 176# also be used as identifiers. The way this works in the parser is that 177# the parser first detects a syntax error, the error handling routine 178# sees that the special keyword caused the error, then replaces the keyword 179# with "ID" and tries again. 180# 181# Check the operation of this logic. 182# 183do_test misc1-6.1 { 184 catchsql { 185 CREATE TABLE t4( 186 abort, asc, begin, cluster, conflict, copy, delimiters, desc, end, 187 explain, fail, ignore, key, offset, pragma, replace, temp, 188 vacuum, view 189 ); 190 } 191} {0 {}} 192do_test misc1-6.2 { 193 catchsql { 194 INSERT INTO t4 195 VALUES(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19); 196 } 197} {0 {}} 198do_test misc1-6.3 { 199 execsql { 200 SELECT * FROM t4 201 } 202} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19} 203do_test misc1-6.4 { 204 execsql { 205 SELECT abort+asc,max(key,pragma,temp) FROM t4 206 } 207} {3 17} 208 209# Test for multi-column primary keys, and for multiple primary keys. 210# 211do_test misc1-7.1 { 212 catchsql { 213 CREATE TABLE error1( 214 a TYPE PRIMARY KEY, 215 b TYPE PRIMARY KEY 216 ); 217 } 218} {1 {table "error1" has more than one primary key}} 219do_test misc1-7.2 { 220 catchsql { 221 CREATE TABLE error1( 222 a INTEGER PRIMARY KEY, 223 b TYPE PRIMARY KEY 224 ); 225 } 226} {1 {table "error1" has more than one primary key}} 227do_test misc1-7.3 { 228 execsql { 229 CREATE TABLE t5(a,b,c,PRIMARY KEY(a,b)); 230 INSERT INTO t5 VALUES(1,2,3); 231 SELECT * FROM t5 ORDER BY a; 232 } 233} {1 2 3} 234do_test misc1-7.4 { 235 catchsql { 236 INSERT INTO t5 VALUES(1,2,4); 237 } 238} {1 {columns a, b are not unique}} 239do_test misc1-7.5 { 240 catchsql { 241 INSERT INTO t5 VALUES(0,2,4); 242 } 243} {0 {}} 244do_test misc1-7.6 { 245 execsql { 246 SELECT * FROM t5 ORDER BY a; 247 } 248} {0 2 4 1 2 3} 249 250do_test misc1-8.1 { 251 catchsql { 252 SELECT *; 253 } 254} {1 {no tables specified}} 255do_test misc1-8.2 { 256 catchsql { 257 SELECT t1.*; 258 } 259} {1 {no such table: t1}} 260 261execsql { 262 DROP TABLE t1; 263 DROP TABLE t2; 264 DROP TABLE t3; 265 DROP TABLE t4; 266} 267 268# 64-bit integers are represented exactly. 269# 270do_test misc1-9.1 { 271 catchsql { 272 CREATE TABLE t1(a unique not null, b unique not null); 273 INSERT INTO t1 VALUES('a',1234567890123456789); 274 INSERT INTO t1 VALUES('b',1234567891123456789); 275 INSERT INTO t1 VALUES('c',1234567892123456789); 276 SELECT * FROM t1; 277 } 278} {0 {a 1234567890123456789 b 1234567891123456789 c 1234567892123456789}} 279 280# A WHERE clause is not allowed to contain more than 99 terms. Check to 281# make sure this limit is enforced. 282# 283do_test misc1-10.0 { 284 execsql {SELECT count(*) FROM manycol} 285} {9} 286do_test misc1-10.1 { 287 set ::where {WHERE x0>=0} 288 for {set i 1} {$i<=99} {incr i} { 289 append ::where " AND x$i<>0" 290 } 291 catchsql "SELECT count(*) FROM manycol $::where" 292} {0 9} 293do_test misc1-10.2 { 294 catchsql "SELECT count(*) FROM manycol $::where AND rowid>0" 295} {1 {WHERE clause too complex - no more than 100 terms allowed}} 296do_test misc1-10.3 { 297 regsub "x0>=0" $::where "x0=0" ::where 298 catchsql "DELETE FROM manycol $::where" 299} {0 {}} 300do_test misc1-10.4 { 301 execsql {SELECT count(*) FROM manycol} 302} {8} 303do_test misc1-10.5 { 304 catchsql "DELETE FROM manycol $::where AND rowid>0" 305} {1 {WHERE clause too complex - no more than 100 terms allowed}} 306do_test misc1-10.6 { 307 execsql {SELECT x1 FROM manycol WHERE x0=100} 308} {101} 309do_test misc1-10.7 { 310 regsub "x0=0" $::where "x0=100" ::where 311 catchsql "UPDATE manycol SET x1=x1+1 $::where" 312} {0 {}} 313do_test misc1-10.8 { 314 execsql {SELECT x1 FROM manycol WHERE x0=100} 315} {102} 316do_test misc1-10.9 { 317 catchsql "UPDATE manycol SET x1=x1+1 $::where AND rowid>0" 318} {1 {WHERE clause too complex - no more than 100 terms allowed}} 319do_test misc1-10.10 { 320 execsql {SELECT x1 FROM manycol WHERE x0=100} 321} {102} 322 323# Make sure the initialization works even if a database is opened while 324# another process has the database locked. 325# 326# Update for v3: The BEGIN doesn't lock the database so the schema is read 327# and the SELECT returns successfully. 328do_test misc1-11.1 { 329 execsql {BEGIN} 330 execsql {UPDATE t1 SET a=0 WHERE 0} 331 sqlite db2 test.db 332 set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg] 333 lappend rc $msg 334# v2 result: {1 {database is locked}} 335} {0 3} 336do_test misc1-11.2 { 337 execsql {COMMIT} 338 set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg] 339 db2 close 340 lappend rc $msg 341} {0 3} 342 343# Make sure string comparisons really do compare strings in format4+. 344# Similar tests in the format3.test file show that for format3 and earlier 345# all comparisions where numeric if either operand looked like a number. 346# 347do_test misc1-12.1 { 348 execsql {SELECT '0'=='0.0'} 349} {1} 350do_test misc1-12.2 { 351 execsql {SELECT '0'==0.0} 352} {1} 353do_test misc1-12.3 { 354 execsql {SELECT '12345678901234567890'=='12345678901234567891'} 355} {0} 356do_test misc1-12.4 { 357 execsql { 358 CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE); 359 INSERT INTO t6 VALUES('0','0.0'); 360 SELECT * FROM t6; 361 } 362} {0 0.0} 363do_test misc1-12.5 { 364 execsql { 365 INSERT OR IGNORE INTO t6 VALUES(0.0,'x'); 366 SELECT * FROM t6; 367 } 368} {0 0.0} 369do_test misc1-12.6 { 370 execsql { 371 INSERT OR IGNORE INTO t6 VALUES('y',0); 372 SELECT * FROM t6; 373 } 374} {0 0.0 y 0} 375do_test misc1-12.7 { 376 execsql { 377 CREATE TABLE t7(x INTEGER, y TEXT, z); 378 INSERT INTO t7 VALUES(0,0,1); 379 INSERT INTO t7 VALUES(0.0,0,2); 380 INSERT INTO t7 VALUES(0,0.0,3); 381 INSERT INTO t7 VALUES(0.0,0.0,4); 382 SELECT DISTINCT x, y FROM t7 ORDER BY z; 383 } 384} {0 0 0 0.0} 385do_test misc1-12.8 { 386 execsql { 387 SELECT min(z), max(z), count(z) FROM t7 GROUP BY x ORDER BY 1; 388 } 389} {1 4 4} 390do_test misc1-12.9 { 391 execsql { 392 SELECT min(z), max(z), count(z) FROM t7 GROUP BY y ORDER BY 1; 393 } 394} {1 2 2 3 4 2} 395 396# This used to be an error. But we changed the code so that arbitrary 397# identifiers can be used as a collating sequence. Collation is by text 398# if the identifier contains "text", "blob", or "clob" and is numeric 399# otherwise. 400# 401# Update: In v3, it is an error again. 402# 403#do_test misc1-12.10 { 404# catchsql { 405# SELECT * FROM t6 ORDER BY a COLLATE unknown; 406# } 407#} {0 {0 0.0 y 0}} 408do_test misc1-12.11 { 409 execsql { 410 CREATE TABLE t8(x TEXT COLLATE numeric, y INTEGER COLLATE text, z); 411 INSERT INTO t8 VALUES(0,0,1); 412 INSERT INTO t8 VALUES(0.0,0,2); 413 INSERT INTO t8 VALUES(0,0.0,3); 414 INSERT INTO t8 VALUES(0.0,0.0,4); 415 SELECT DISTINCT x, y FROM t8 ORDER BY z; 416 } 417} {0 0 0.0 0} 418do_test misc1-12.12 { 419 execsql { 420 SELECT min(z), max(z), count(z) FROM t8 GROUP BY x ORDER BY 1; 421 } 422} {1 3 2 2 4 2} 423do_test misc1-12.13 { 424 execsql { 425 SELECT min(z), max(z), count(z) FROM t8 GROUP BY y ORDER BY 1; 426 } 427} {1 4 4} 428 429# There was a problem with realloc() in the OP_MemStore operation of 430# the VDBE. A buffer was being reallocated but some pointers into 431# the old copy of the buffer were not being moved over to the new copy. 432# The following code tests for the problem. 433# 434do_test misc1-13.1 { 435 execsql { 436 CREATE TABLE t9(x,y); 437 INSERT INTO t9 VALUES('one',1); 438 INSERT INTO t9 VALUES('two',2); 439 INSERT INTO t9 VALUES('three',3); 440 INSERT INTO t9 VALUES('four',4); 441 INSERT INTO t9 VALUES('five',5); 442 INSERT INTO t9 VALUES('six',6); 443 INSERT INTO t9 VALUES('seven',7); 444 INSERT INTO t9 VALUES('eight',8); 445 INSERT INTO t9 VALUES('nine',9); 446 INSERT INTO t9 VALUES('ten',10); 447 INSERT INTO t9 VALUES('eleven',11); 448 SELECT y FROM t9 449 WHERE x=(SELECT x FROM t9 WHERE y=1) 450 OR x=(SELECT x FROM t9 WHERE y=2) 451 OR x=(SELECT x FROM t9 WHERE y=3) 452 OR x=(SELECT x FROM t9 WHERE y=4) 453 OR x=(SELECT x FROM t9 WHERE y=5) 454 OR x=(SELECT x FROM t9 WHERE y=6) 455 OR x=(SELECT x FROM t9 WHERE y=7) 456 OR x=(SELECT x FROM t9 WHERE y=8) 457 OR x=(SELECT x FROM t9 WHERE y=9) 458 OR x=(SELECT x FROM t9 WHERE y=10) 459 OR x=(SELECT x FROM t9 WHERE y=11) 460 OR x=(SELECT x FROM t9 WHERE y=12) 461 OR x=(SELECT x FROM t9 WHERE y=13) 462 OR x=(SELECT x FROM t9 WHERE y=14) 463 ; 464 } 465} {1 2 3 4 5 6 7 8 9 10 11} 466 467# Make sure a database connection still works after changing the 468# working directory. 469# 470do_test misc1-14.1 { 471 file mkdir tempdir 472 cd tempdir 473 execsql {BEGIN} 474 file exists ./test.db-journal 475} {0} 476do_test misc1-14.2 { 477 execsql {UPDATE t1 SET a=0 WHERE 0} 478 file exists ../test.db-journal 479} {1} 480do_test misc1-14.3 { 481 cd .. 482 file delete tempdir 483 execsql {COMMIT} 484 file exists ./test.db-journal 485} {0} 486 487# A failed create table should not leave the table in the internal 488# data structures. Ticket #238. 489# 490do_test misc1-15.1 { 491 catchsql { 492 CREATE TABLE t10 AS SELECT c1; 493 } 494} {1 {no such column: c1}} 495do_test misc1-15.2 { 496 catchsql { 497 CREATE TABLE t10 AS SELECT 1; 498 } 499 # The bug in ticket #238 causes the statement above to fail with 500 # the error "table t10 alread exists" 501} {0 {}} 502 503# Test for memory leaks when a CREATE TABLE containing a primary key 504# fails. Ticket #249. 505# 506do_test misc1-16.1 { 507 catchsql {SELECT name FROM sqlite_master LIMIT 1} 508 catchsql { 509 CREATE TABLE test(a integer, primary key(a)); 510 } 511} {0 {}} 512do_test misc1-16.2 { 513 catchsql { 514 CREATE TABLE test(a integer, primary key(a)); 515 } 516} {1 {table test already exists}} 517do_test misc1-16.3 { 518 catchsql { 519 CREATE TABLE test2(a text primary key, b text, primary key(a,b)); 520 } 521} {1 {table "test2" has more than one primary key}} 522do_test misc1-16.4 { 523 execsql { 524 INSERT INTO test VALUES(1); 525 SELECT rowid, a FROM test; 526 } 527} {1 1} 528do_test misc1-16.5 { 529 execsql { 530 INSERT INTO test VALUES(5); 531 SELECT rowid, a FROM test; 532 } 533} {1 1 5 5} 534do_test misc1-16.6 { 535 execsql { 536 INSERT INTO test VALUES(NULL); 537 SELECT rowid, a FROM test; 538 } 539} {1 1 5 5 6 6} 540 541# Ticket #333: Temp triggers that modify persistent tables. 542# 543do_test misc1-17.1 { 544 execsql { 545 BEGIN; 546 CREATE TABLE RealTable(TestID INTEGER PRIMARY KEY, TestString TEXT); 547 CREATE TEMP TABLE TempTable(TestID INTEGER PRIMARY KEY, TestString TEXT); 548 CREATE TEMP TRIGGER trigTest_1 AFTER UPDATE ON TempTable BEGIN 549 INSERT INTO RealTable(TestString) 550 SELECT new.TestString FROM TempTable LIMIT 1; 551 END; 552 INSERT INTO TempTable(TestString) VALUES ('1'); 553 INSERT INTO TempTable(TestString) VALUES ('2'); 554 UPDATE TempTable SET TestString = TestString + 1 WHERE TestID IN (1, 2); 555 COMMIT; 556 SELECT TestString FROM RealTable ORDER BY 1; 557 } 558} {2 3} 559 560finish_test 561