1# 2005 Mar 16 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: misc5.test,v 1.20 2008/01/23 12:52:41 drh Exp $ 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# Build records using the MakeRecord opcode such that the size of the 22# header is at the transition point in the size of a varint. 23# 24# This test causes an assertion failure or a buffer overrun in version 25# 3.1.5 and earlier. 26# 27for {set i 120} {$i<140} {incr i} { 28 do_test misc5-1.$i { 29 catchsql {DROP TABLE t1} 30 set sql1 {CREATE TABLE t1} 31 set sql2 {INSERT INTO t1 VALUES} 32 set sep ( 33 for {set j 0} {$j<$i} {incr j} { 34 append sql1 ${sep}a$j 35 append sql2 ${sep}$j 36 set sep , 37 } 38 append sql1 {);} 39 append sql2 {);} 40 execsql $sql1$sql2 41 } {} 42} 43 44# Make sure large integers are stored correctly. 45# 46ifcapable conflict { 47 do_test misc5-2.1 { 48 execsql { 49 create table t2(x unique); 50 insert into t2 values(1); 51 insert or ignore into t2 select x*2 from t2; 52 insert or ignore into t2 select x*4 from t2; 53 insert or ignore into t2 select x*16 from t2; 54 insert or ignore into t2 select x*256 from t2; 55 insert or ignore into t2 select x*65536 from t2; 56 insert or ignore into t2 select x*2147483648 from t2; 57 insert or ignore into t2 select x-1 from t2; 58 insert or ignore into t2 select x+1 from t2; 59 insert or ignore into t2 select -x from t2; 60 select count(*) from t2; 61 } 62 } 371 63} else { 64 do_test misc5-2.1 { 65 execsql { 66 BEGIN; 67 create table t2(x unique); 68 create table t2_temp(x); 69 insert into t2_temp values(1); 70 insert into t2_temp select x*2 from t2_temp; 71 insert into t2_temp select x*4 from t2_temp; 72 insert into t2_temp select x*16 from t2_temp; 73 insert into t2_temp select x*256 from t2_temp; 74 insert into t2_temp select x*65536 from t2_temp; 75 insert into t2_temp select x*2147483648 from t2_temp; 76 insert into t2_temp select x-1 from t2_temp; 77 insert into t2_temp select x+1 from t2_temp; 78 insert into t2_temp select -x from t2_temp; 79 INSERT INTO t2 SELECT DISTINCT(x) FROM t2_temp; 80 DROP TABLE t2_temp; 81 COMMIT; 82 select count(*) from t2; 83 } 84 } 371 85} 86do_test misc5-2.2 { 87 execsql { 88 select x from t2 order by x; 89 } 90} \ 91"-4611686018427387905\ 92-4611686018427387904\ 93-4611686018427387903\ 94-2305843009213693953\ 95-2305843009213693952\ 96-2305843009213693951\ 97-1152921504606846977\ 98-1152921504606846976\ 99-1152921504606846975\ 100-576460752303423489\ 101-576460752303423488\ 102-576460752303423487\ 103-288230376151711745\ 104-288230376151711744\ 105-288230376151711743\ 106-144115188075855873\ 107-144115188075855872\ 108-144115188075855871\ 109-72057594037927937\ 110-72057594037927936\ 111-72057594037927935\ 112-36028797018963969\ 113-36028797018963968\ 114-36028797018963967\ 115-18014398509481985\ 116-18014398509481984\ 117-18014398509481983\ 118-9007199254740993\ 119-9007199254740992\ 120-9007199254740991\ 121-4503599627370497\ 122-4503599627370496\ 123-4503599627370495\ 124-2251799813685249\ 125-2251799813685248\ 126-2251799813685247\ 127-1125899906842625\ 128-1125899906842624\ 129-1125899906842623\ 130-562949953421313\ 131-562949953421312\ 132-562949953421311\ 133-281474976710657\ 134-281474976710656\ 135-281474976710655\ 136-140737488355329\ 137-140737488355328\ 138-140737488355327\ 139-70368744177665\ 140-70368744177664\ 141-70368744177663\ 142-35184372088833\ 143-35184372088832\ 144-35184372088831\ 145-17592186044417\ 146-17592186044416\ 147-17592186044415\ 148-8796093022209\ 149-8796093022208\ 150-8796093022207\ 151-4398046511105\ 152-4398046511104\ 153-4398046511103\ 154-2199023255553\ 155-2199023255552\ 156-2199023255551\ 157-1099511627777\ 158-1099511627776\ 159-1099511627775\ 160-549755813889\ 161-549755813888\ 162-549755813887\ 163-274877906945\ 164-274877906944\ 165-274877906943\ 166-137438953473\ 167-137438953472\ 168-137438953471\ 169-68719476737\ 170-68719476736\ 171-68719476735\ 172-34359738369\ 173-34359738368\ 174-34359738367\ 175-17179869185\ 176-17179869184\ 177-17179869183\ 178-8589934593\ 179-8589934592\ 180-8589934591\ 181-4294967297\ 182-4294967296\ 183-4294967295\ 184-2147483649\ 185-2147483648\ 186-2147483647\ 187-1073741825\ 188-1073741824\ 189-1073741823\ 190-536870913\ 191-536870912\ 192-536870911\ 193-268435457\ 194-268435456\ 195-268435455\ 196-134217729\ 197-134217728\ 198-134217727\ 199-67108865\ 200-67108864\ 201-67108863\ 202-33554433\ 203-33554432\ 204-33554431\ 205-16777217\ 206-16777216\ 207-16777215\ 208-8388609\ 209-8388608\ 210-8388607\ 211-4194305\ 212-4194304\ 213-4194303\ 214-2097153\ 215-2097152\ 216-2097151\ 217-1048577\ 218-1048576\ 219-1048575\ 220-524289\ 221-524288\ 222-524287\ 223-262145\ 224-262144\ 225-262143\ 226-131073\ 227-131072\ 228-131071\ 229-65537\ 230-65536\ 231-65535\ 232-32769\ 233-32768\ 234-32767\ 235-16385\ 236-16384\ 237-16383\ 238-8193\ 239-8192\ 240-8191\ 241-4097\ 242-4096\ 243-4095\ 244-2049\ 245-2048\ 246-2047\ 247-1025\ 248-1024\ 249-1023\ 250-513\ 251-512\ 252-511\ 253-257\ 254-256\ 255-255\ 256-129\ 257-128\ 258-127\ 259-65\ 260-64\ 261-63\ 262-33\ 263-32\ 264-31\ 265-17\ 266-16\ 267-15\ 268-9\ 269-8\ 270-7\ 271-5\ 272-4\ 273-3\ 274-2\ 275-1\ 2760\ 2771\ 2782\ 2793\ 2804\ 2815\ 2827\ 2838\ 2849\ 28515\ 28616\ 28717\ 28831\ 28932\ 29033\ 29163\ 29264\ 29365\ 294127\ 295128\ 296129\ 297255\ 298256\ 299257\ 300511\ 301512\ 302513\ 3031023\ 3041024\ 3051025\ 3062047\ 3072048\ 3082049\ 3094095\ 3104096\ 3114097\ 3128191\ 3138192\ 3148193\ 31516383\ 31616384\ 31716385\ 31832767\ 31932768\ 32032769\ 32165535\ 32265536\ 32365537\ 324131071\ 325131072\ 326131073\ 327262143\ 328262144\ 329262145\ 330524287\ 331524288\ 332524289\ 3331048575\ 3341048576\ 3351048577\ 3362097151\ 3372097152\ 3382097153\ 3394194303\ 3404194304\ 3414194305\ 3428388607\ 3438388608\ 3448388609\ 34516777215\ 34616777216\ 34716777217\ 34833554431\ 34933554432\ 35033554433\ 35167108863\ 35267108864\ 35367108865\ 354134217727\ 355134217728\ 356134217729\ 357268435455\ 358268435456\ 359268435457\ 360536870911\ 361536870912\ 362536870913\ 3631073741823\ 3641073741824\ 3651073741825\ 3662147483647\ 3672147483648\ 3682147483649\ 3694294967295\ 3704294967296\ 3714294967297\ 3728589934591\ 3738589934592\ 3748589934593\ 37517179869183\ 37617179869184\ 37717179869185\ 37834359738367\ 37934359738368\ 38034359738369\ 38168719476735\ 38268719476736\ 38368719476737\ 384137438953471\ 385137438953472\ 386137438953473\ 387274877906943\ 388274877906944\ 389274877906945\ 390549755813887\ 391549755813888\ 392549755813889\ 3931099511627775\ 3941099511627776\ 3951099511627777\ 3962199023255551\ 3972199023255552\ 3982199023255553\ 3994398046511103\ 4004398046511104\ 4014398046511105\ 4028796093022207\ 4038796093022208\ 4048796093022209\ 40517592186044415\ 40617592186044416\ 40717592186044417\ 40835184372088831\ 40935184372088832\ 41035184372088833\ 41170368744177663\ 41270368744177664\ 41370368744177665\ 414140737488355327\ 415140737488355328\ 416140737488355329\ 417281474976710655\ 418281474976710656\ 419281474976710657\ 420562949953421311\ 421562949953421312\ 422562949953421313\ 4231125899906842623\ 4241125899906842624\ 4251125899906842625\ 4262251799813685247\ 4272251799813685248\ 4282251799813685249\ 4294503599627370495\ 4304503599627370496\ 4314503599627370497\ 4329007199254740991\ 4339007199254740992\ 4349007199254740993\ 43518014398509481983\ 43618014398509481984\ 43718014398509481985\ 43836028797018963967\ 43936028797018963968\ 44036028797018963969\ 44172057594037927935\ 44272057594037927936\ 44372057594037927937\ 444144115188075855871\ 445144115188075855872\ 446144115188075855873\ 447288230376151711743\ 448288230376151711744\ 449288230376151711745\ 450576460752303423487\ 451576460752303423488\ 452576460752303423489\ 4531152921504606846975\ 4541152921504606846976\ 4551152921504606846977\ 4562305843009213693951\ 4572305843009213693952\ 4582305843009213693953\ 4594611686018427387903\ 4604611686018427387904\ 4614611686018427387905" 462 463# Ticket #1210. Do proper reference counting of Table structures 464# so that deeply nested SELECT statements can be flattened correctly. 465# 466ifcapable subquery { 467 do_test misc5-3.1 { 468 execsql { 469 CREATE TABLE songs(songid, artist, timesplayed); 470 INSERT INTO songs VALUES(1,'one',1); 471 INSERT INTO songs VALUES(2,'one',2); 472 INSERT INTO songs VALUES(3,'two',3); 473 INSERT INTO songs VALUES(4,'three',5); 474 INSERT INTO songs VALUES(5,'one',7); 475 INSERT INTO songs VALUES(6,'two',11); 476 SELECT DISTINCT artist 477 FROM ( 478 SELECT DISTINCT artist 479 FROM songs 480 WHERE songid IN ( 481 SELECT songid 482 FROM songs 483 WHERE LOWER(artist) = ( 484 -- This sub-query is indeterminate. Because there is no ORDER BY, 485 -- it may return 'one', 'two' or 'three'. Because of this, the 486 -- outermost parent query may correctly return any of 'one', 'two' 487 -- or 'three' as well. 488 SELECT DISTINCT LOWER(artist) 489 FROM ( 490 -- This sub-query returns the table: 491 -- 492 -- two 14 493 -- one 10 494 -- three 5 495 -- 496 SELECT DISTINCT artist,sum(timesplayed) AS total 497 FROM songs 498 GROUP BY LOWER(artist) 499 ORDER BY total DESC 500 LIMIT 10 501 ) 502 WHERE artist <> '' 503 ) 504 ) 505 ) 506 ORDER BY LOWER(artist) ASC; 507 } 508 } {one} 509} 510 511# Ticket #1370. Do not overwrite small files (less than 1024 bytes) 512# when trying to open them as a database. 513# 514do_test misc5-4.1 { 515 db close 516 file delete -force test.db 517 set fd [open test.db w] 518 puts $fd "This is not really a database" 519 close $fd 520 sqlite3 db test.db 521 catchsql { 522 CREATE TABLE t1(a,b,c); 523 } 524} {1 {file is encrypted or is not a database}} 525 526# Ticket #1371. Allow floating point numbers of the form .N or N. 527# 528do_test misc5-5.1 { 529 execsql {SELECT .1 } 530} 0.1 531do_test misc5-5.2 { 532 execsql {SELECT 2. } 533} 2.0 534do_test misc5-5.3 { 535 execsql {SELECT 3.e0 } 536} 3.0 537do_test misc5-5.4 { 538 execsql {SELECT .4e+1} 539} 4.0 540 541# Ticket #1582. Ensure that an unknown table in a LIMIT clause applied to 542# a UNION ALL query causes an error, not a crash. 543# 544db close 545file delete -force test.db 546sqlite3 db test.db 547ifcapable subquery&&compound { 548 do_test misc5-6.1 { 549 catchsql { 550 SELECT * FROM sqlite_master 551 UNION ALL 552 SELECT * FROM sqlite_master 553 LIMIT (SELECT count(*) FROM blah); 554 } 555 } {1 {no such table: blah}} 556 do_test misc5-6.2 { 557 execsql { 558 CREATE TABLE logs(msg TEXT, timestamp INTEGER, dbtime TEXT); 559 } 560 catchsql { 561 SELECT * FROM logs WHERE logs.oid >= (SELECT head FROM logs_base) 562 UNION ALL 563 SELECT * FROM logs 564 LIMIT (SELECT lmt FROM logs_base) ; 565 } 566 } {1 {no such table: logs_base}} 567} 568 569# Overflow the lemon parser stack by providing an overly complex 570# expression. Make sure that the overflow is detected and reported. 571# 572do_test misc5-7.1 { 573 execsql {CREATE TABLE t1(x)} 574 set sql "INSERT INTO t1 VALUES(" 575 set tail "" 576 for {set i 0} {$i<200} {incr i} { 577 append sql "(1+" 578 append tail ")" 579 } 580 append sql 2$tail 581 catchsql $sql 582} {1 {parser stack overflow}} 583 584# Ticket #1911 585# 586ifcapable compound { 587 do_test misc5-9.1 { 588 execsql { 589 SELECT name, type FROM sqlite_master WHERE name IS NULL 590 UNION 591 SELECT type, name FROM sqlite_master WHERE type IS NULL 592 ORDER BY 1, 2, 1, 2, 1, 2 593 } 594 } {} 595 do_test misc5-9.2 { 596 execsql { 597 SELECT name, type FROM sqlite_master WHERE name IS NULL 598 UNION 599 SELECT type, name FROM sqlite_master WHERE type IS NULL 600 ORDER BY 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2 601 } 602 } {} 603} 604 605# Ticket #1912. Make the tokenizer require a space after a numeric 606# literal. 607# 608do_test misc5-10.1 { 609 catchsql { 610 SELECT 123abc 611 } 612} {1 {unrecognized token: "123abc"}} 613do_test misc5-10.2 { 614 catchsql { 615 SELECT 1*123.4e5ghi; 616 } 617} {1 {unrecognized token: "123.4e5ghi"}} 618 619 620 621finish_test 622