AryaWu/sqlite
0
1# 2003 December 172#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# $Id: misc3.test,v 1.20 2009/05/06 00:49:01 drh Exp $17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21ifcapable {integrityck} {22 # Ticket #529. Make sure an ABORT does not damage the in-memory cache23 # that will be used by subsequent statements in the same transaction.24 #25 do_test misc3-1.1 {26 execsql {27 CREATE TABLE t1(a UNIQUE,b);28 INSERT INTO t129 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');30 UPDATE t1 SET b=b||b;31 UPDATE t1 SET b=b||b;32 UPDATE t1 SET b=b||b;33 UPDATE t1 SET b=b||b;34 UPDATE t1 SET b=b||b;35 INSERT INTO t1 VALUES(2,'x');36 UPDATE t1 SET b=substr(b,1,500);37 BEGIN;38 }39 catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';}40 execsql {41 CREATE TABLE t2(x,y);42 COMMIT;43 PRAGMA integrity_check;44 }45 } ok46}47ifcapable {integrityck} {48 do_test misc3-1.2 {49 execsql {50 DROP TABLE t1;51 DROP TABLE t2;52 }53 ifcapable {vacuum} {execsql VACUUM}54 execsql {55 CREATE TABLE t1(a UNIQUE,b);56 INSERT INTO t157 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');58 INSERT INTO t1 SELECT a+1, b||b FROM t1;59 INSERT INTO t1 SELECT a+2, b||b FROM t1;60 INSERT INTO t1 SELECT a+4, b FROM t1;61 INSERT INTO t1 SELECT a+8, b FROM t1;62 INSERT INTO t1 SELECT a+16, b FROM t1;63 INSERT INTO t1 SELECT a+32, b FROM t1;64 INSERT INTO t1 SELECT a+64, b FROM t1;65 BEGIN;66 }67 catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';}68 execsql {69 INSERT INTO t1 VALUES(200,'hello out there');70 COMMIT;71 PRAGMA integrity_check;72 }73 } ok74}75 76# Tests of the sqliteAtoF() function in util.c77#78do_test misc3-2.1 {79 execsql {SELECT 2e-25*0.5e25}80} 1.081do_test misc3-2.2 {82 execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025}83} 1.084do_test misc3-2.3 {85 execsql {SELECT 000000000002e-0000000025*0.5e25}86} 1.087do_test misc3-2.4 {88 execsql {SELECT 2e-25*0.5e250}89} 1e+22590do_test misc3-2.5 {91 execsql {SELECT format('%.15e',2.0e-250*0.5e25)}92} {1.0000000000000e-225}93do_test misc3-2.6 {94 execsql {SELECT '-2.0e-127' * '-0.5e27'}95} 1e-10096do_test misc3-2.7 {97 execsql {SELECT '+2.0e-127' * '-0.5e27'}98} -1e-10099do_test misc3-2.8 {100 execsql {SELECT 2.0e-27 * '+0.5e+127'}101} 1e+100102do_test misc3-2.9 {103 execsql {SELECT 2.0e-27 * '+0.000005e+132'}104} 1e+100105 106# Ticket #522. Make sure integer overflow is handled properly in107# indices.108#109integrity_check misc3-3.1110do_test misc3-3.2 {111 execsql {112 CREATE TABLE t2(a INT UNIQUE);113 }114} {}115integrity_check misc3-3.2.1116do_test misc3-3.3 {117 execsql {118 INSERT INTO t2 VALUES(2147483648);119 }120} {}121integrity_check misc3-3.3.1122do_test misc3-3.4 {123 execsql {124 INSERT INTO t2 VALUES(-2147483649);125 }126} {}127integrity_check misc3-3.4.1128do_test misc3-3.5 {129 execsql {130 INSERT INTO t2 VALUES(+2147483649);131 }132} {}133integrity_check misc3-3.5.1134do_test misc3-3.6 {135 execsql {136 INSERT INTO t2 VALUES(+2147483647);137 INSERT INTO t2 VALUES(-2147483648);138 INSERT INTO t2 VALUES(-2147483647);139 INSERT INTO t2 VALUES(2147483646);140 SELECT * FROM t2 ORDER BY a;141 }142} {-2147483649 -2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}143do_test misc3-3.7 {144 execsql {145 SELECT * FROM t2 WHERE a>=-2147483648 ORDER BY a;146 }147} {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}148do_test misc3-3.8 {149 execsql {150 SELECT * FROM t2 WHERE a>-2147483648 ORDER BY a;151 }152} {-2147483647 2147483646 2147483647 2147483648 2147483649}153do_test misc3-3.9 {154 execsql {155 SELECT * FROM t2 WHERE a>-2147483649 ORDER BY a;156 }157} {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}158do_test misc3-3.10 {159 execsql {160 SELECT * FROM t2 WHERE a>=0 AND a<2147483649 ORDER BY a DESC;161 }162} {2147483648 2147483647 2147483646}163do_test misc3-3.11 {164 execsql {165 SELECT * FROM t2 WHERE a>=0 AND a<=2147483648 ORDER BY a DESC;166 }167} {2147483648 2147483647 2147483646}168do_test misc3-3.12 {169 execsql {170 SELECT * FROM t2 WHERE a>=0 AND a<2147483648 ORDER BY a DESC;171 }172} {2147483647 2147483646}173do_test misc3-3.13 {174 execsql {175 SELECT * FROM t2 WHERE a>=0 AND a<=2147483647 ORDER BY a DESC;176 }177} {2147483647 2147483646}178do_test misc3-3.14 {179 execsql {180 SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC;181 }182} {2147483646}183 184# Ticket #565. A stack overflow is occurring when the subquery to the185# right of an IN operator contains many NULLs186#187do_test misc3-4.1 {188 execsql {189 CREATE TABLE t3(a INTEGER PRIMARY KEY, b);190 INSERT INTO t3(b) VALUES('abc');191 INSERT INTO t3(b) VALUES('xyz');192 INSERT INTO t3(b) VALUES(NULL);193 INSERT INTO t3(b) VALUES(NULL);194 INSERT INTO t3(b) SELECT b||'d' FROM t3;195 INSERT INTO t3(b) SELECT b||'e' FROM t3;196 INSERT INTO t3(b) SELECT b||'f' FROM t3;197 INSERT INTO t3(b) SELECT b||'g' FROM t3;198 INSERT INTO t3(b) SELECT b||'h' FROM t3;199 SELECT count(a), count(b) FROM t3;200 }201} {128 64}202ifcapable subquery {203do_test misc3-4.2 {204 execsql {205 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3);206 }207 } {64}208 do_test misc3-4.3 {209 execsql {210 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);211 }212 } {64}213}214 215# Ticket #601: Putting a left join inside "SELECT * FROM (<join-here>)"216# gives different results that if the outer "SELECT * FROM ..." is omitted.217#218ifcapable subquery {219 do_test misc3-5.1 {220 execsql {221 CREATE TABLE x1 (b, c);222 INSERT INTO x1 VALUES('dog',3);223 INSERT INTO x1 VALUES('cat',1);224 INSERT INTO x1 VALUES('dog',4);225 CREATE TABLE x2 (c, e);226 INSERT INTO x2 VALUES(1,'one');227 INSERT INTO x2 VALUES(2,'two');228 INSERT INTO x2 VALUES(3,'three');229 INSERT INTO x2 VALUES(4,'four');230 SELECT x2.c AS c, e, b FROM x2 LEFT JOIN231 (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)232 USING(c);233 }234 } {1 one cat 2 two {} 3 three {} 4 four dog}235 do_test misc3-5.2 {236 execsql {237 SELECT * FROM (238 SELECT x2.c AS c, e, b FROM x2 LEFT JOIN239 (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)240 USING(c)241 );242 }243 } {1 one cat 2 two {} 3 three {} 4 four dog}244}245 246ifcapable {explain} {247 # Ticket #626: make sure EXPLAIN prevents BEGIN and COMMIT from working.248 #249 do_test misc3-6.1 {250 execsql {EXPLAIN BEGIN}251 catchsql {BEGIN}252 } {0 {}}253 do_test misc3-6.2 {254 execsql {EXPLAIN COMMIT}255 catchsql {COMMIT}256 } {0 {}}257 do_test misc3-6.3 {258 execsql {BEGIN; EXPLAIN ROLLBACK}259 catchsql {ROLLBACK}260 } {0 {}}261 262 # Do some additional EXPLAIN operations to exercise the displayP4 logic.263 do_test misc3-6.10 {264 set x [execsql {265 CREATE TABLE ex1(266 a INTEGER DEFAULT 54321,267 b TEXT DEFAULT "hello",268 c REAL DEFAULT 3.1415926269 );270 CREATE UNIQUE INDEX ex1i1 ON ex1(a);271 EXPLAIN REINDEX;272 }]273 ifcapable mergesort {274 regexp { SorterCompare \d+ \d+ \d+ } $x275 } else {276 regexp { IsUnique \d+ \d+ \d+ \d+ } $x277 }278 } {1}279 if {[regexp {16} [db one {PRAGMA encoding}]]} {280 do_test misc3-6.11-utf16 {281 set x [execsql {282 EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC283 }]284 set y [regexp { 123456789012 } $x]285 lappend y [regexp { 4.5678 } $x]286 lappend y [regexp {,-B} $x]287 } {1 1 1}288 } else {289 do_test misc3-6.11-utf8 {290 set x [execsql {291 EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC292 }]293 set y [regexp { 123456789012 } $x]294 lappend y [regexp { 4.5678 } $x]295 lappend y [regexp { hello } $x]296 lappend y [regexp {,-B} $x]297 } {1 1 1 1}298 }299}300 301ifcapable {trigger} {302# Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside303# of a trigger.304#305do_test misc3-7.1 {306 execsql {307 BEGIN;308 CREATE TABLE y1(a);309 CREATE TABLE y2(b);310 CREATE TABLE y3(c);311 CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN312 INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1;313 END;314 INSERT INTO y1 VALUES(1);315 INSERT INTO y1 VALUES(2);316 INSERT INTO y1 SELECT a+2 FROM y1;317 INSERT INTO y1 SELECT a+4 FROM y1;318 INSERT INTO y1 SELECT a+8 FROM y1;319 INSERT INTO y1 SELECT a+16 FROM y1;320 INSERT INTO y2 SELECT a FROM y1;321 COMMIT;322 SELECT count(*) FROM y1;323 }324} 32325do_test misc3-7.2 {326 execsql {327 DELETE FROM y1;328 SELECT count(*) FROM y1;329 }330} 0331do_test misc3-7.3 {332 execsql {333 SELECT count(*) FROM y3;334 }335} 32336} ;# endif trigger337 338# Ticket #668: VDBE stack overflow occurs when the left-hand side339# of an IN expression is NULL and the result is used as an integer, not340# as a jump.341#342ifcapable subquery {343 do_test misc-8.1 {344 execsql {345 SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3346 }347 } {2}348 do_test misc-8.2 {349 execsql {350 SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2351 }352 } {2}353}354 355finish_test356