1# 2003 December 17 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: misc3.test,v 1.14 2004/11/04 04:42:28 drh Exp $ 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21ifcapable {integrityck} { 22 # Ticket #529. Make sure an ABORT does not damage the in-memory cache 23 # 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 t1 29 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 } ok 46} 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 t1 57 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 } ok 74} 75 76# Tests of the sqliteAtoF() function in util.c 77# 78do_test misc3-2.1 { 79 execsql {SELECT 2e-25*0.5e25} 80} 1.0 81do_test misc3-2.2 { 82 execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025} 83} 1.0 84do_test misc3-2.3 { 85 execsql {SELECT 000000000002e-0000000025*0.5e25} 86} 1.0 87do_test misc3-2.4 { 88 execsql {SELECT 2e-25*0.5e250} 89} 1e+225 90do_test misc3-2.5 { 91 execsql {SELECT 2.0e-250*0.5e25} 92} 1e-225 93do_test misc3-2.6 { 94 execsql {SELECT '-2.0e-127' * '-0.5e27'} 95} 1e-100 96do_test misc3-2.7 { 97 execsql {SELECT '+2.0e-127' * '-0.5e27'} 98} -1e-100 99do_test misc3-2.8 { 100 execsql {SELECT 2.0e-27 * '+0.5e+127'} 101} 1e+100 102do_test misc3-2.9 { 103 execsql {SELECT 2.0e-27 * '+0.000005e+132'} 104} 1e+100 105 106# Ticket #522. Make sure integer overflow is handled properly in 107# indices. 108# 109do_test misc3-3.1 { 110 execsql {PRAGMA integrity_check} 111} ok 112do_test misc3-3.2 { 113 execsql { 114 CREATE TABLE t2(a INT UNIQUE); 115 PRAGMA integrity_check; 116 } 117} ok 118do_test misc3-3.3 { 119 execsql { 120 INSERT INTO t2 VALUES(2147483648); 121 PRAGMA integrity_check; 122 } 123} ok 124do_test misc3-3.4 { 125 execsql { 126 INSERT INTO t2 VALUES(-2147483649); 127 PRAGMA integrity_check; 128 } 129} ok 130do_test misc3-3.5 { 131 execsql { 132 INSERT INTO t2 VALUES(+2147483649); 133 PRAGMA integrity_check; 134 } 135} ok 136do_test misc3-3.6 { 137 execsql { 138 INSERT INTO t2 VALUES(+2147483647); 139 INSERT INTO t2 VALUES(-2147483648); 140 INSERT INTO t2 VALUES(-2147483647); 141 INSERT INTO t2 VALUES(2147483646); 142 SELECT * FROM t2 ORDER BY a; 143 } 144} {-2147483649 -2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} 145do_test misc3-3.7 { 146 execsql { 147 SELECT * FROM t2 WHERE a>=-2147483648 ORDER BY a; 148 } 149} {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} 150do_test misc3-3.8 { 151 execsql { 152 SELECT * FROM t2 WHERE a>-2147483648 ORDER BY a; 153 } 154} {-2147483647 2147483646 2147483647 2147483648 2147483649} 155do_test misc3-3.9 { 156 execsql { 157 SELECT * FROM t2 WHERE a>-2147483649 ORDER BY a; 158 } 159} {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} 160do_test misc3-3.10 { 161 execsql { 162 SELECT * FROM t2 WHERE a>=0 AND a<2147483649 ORDER BY a DESC; 163 } 164} {2147483648 2147483647 2147483646} 165do_test misc3-3.11 { 166 execsql { 167 SELECT * FROM t2 WHERE a>=0 AND a<=2147483648 ORDER BY a DESC; 168 } 169} {2147483648 2147483647 2147483646} 170do_test misc3-3.12 { 171 execsql { 172 SELECT * FROM t2 WHERE a>=0 AND a<2147483648 ORDER BY a DESC; 173 } 174} {2147483647 2147483646} 175do_test misc3-3.13 { 176 execsql { 177 SELECT * FROM t2 WHERE a>=0 AND a<=2147483647 ORDER BY a DESC; 178 } 179} {2147483647 2147483646} 180do_test misc3-3.14 { 181 execsql { 182 SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC; 183 } 184} {2147483646} 185 186# Ticket #565. A stack overflow is occurring when the subquery to the 187# right of an IN operator contains many NULLs 188# 189do_test misc3-4.1 { 190 execsql { 191 CREATE TABLE t3(a INTEGER PRIMARY KEY, b); 192 INSERT INTO t3(b) VALUES('abc'); 193 INSERT INTO t3(b) VALUES('xyz'); 194 INSERT INTO t3(b) VALUES(NULL); 195 INSERT INTO t3(b) VALUES(NULL); 196 INSERT INTO t3(b) SELECT b||'d' FROM t3; 197 INSERT INTO t3(b) SELECT b||'e' FROM t3; 198 INSERT INTO t3(b) SELECT b||'f' FROM t3; 199 INSERT INTO t3(b) SELECT b||'g' FROM t3; 200 INSERT INTO t3(b) SELECT b||'h' FROM t3; 201 SELECT count(a), count(b) FROM t3; 202 } 203} {128 64} 204do_test misc3-4.2 { 205 execsql { 206 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3); 207 } 208} {64} 209do_test misc3-4.3 { 210 execsql { 211 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1); 212 } 213} {64} 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# 218do_test misc3-5.1 { 219 execsql { 220 CREATE TABLE x1 (b, c); 221 INSERT INTO x1 VALUES('dog',3); 222 INSERT INTO x1 VALUES('cat',1); 223 INSERT INTO x1 VALUES('dog',4); 224 CREATE TABLE x2 (c, e); 225 INSERT INTO x2 VALUES(1,'one'); 226 INSERT INTO x2 VALUES(2,'two'); 227 INSERT INTO x2 VALUES(3,'three'); 228 INSERT INTO x2 VALUES(4,'four'); 229 SELECT x2.c AS c, e, b FROM x2 LEFT JOIN 230 (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b) 231 USING(c); 232 } 233} {1 one cat 2 two {} 3 three {} 4 four dog} 234do_test misc4-5.2 { 235 execsql { 236 SELECT * FROM ( 237 SELECT x2.c AS c, e, b FROM x2 LEFT JOIN 238 (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b) 239 USING(c) 240 ); 241 } 242} {1 one cat 2 two {} 3 three {} 4 four dog} 243 244ifcapable {explain} { 245 # Ticket #626: make sure EXPLAIN prevents BEGIN and COMMIT from working. 246 # 247 do_test misc3-6.1 { 248 execsql {EXPLAIN BEGIN} 249 catchsql {BEGIN} 250 } {0 {}} 251 do_test misc3-6.2 { 252 execsql {EXPLAIN COMMIT} 253 catchsql {COMMIT} 254 } {0 {}} 255 do_test misc3-6.3 { 256 execsql {BEGIN; EXPLAIN ROLLBACK} 257 catchsql {ROLLBACK} 258 } {0 {}} 259} 260 261ifcapable {trigger} { 262# Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside 263# of a trigger. 264# 265do_test misc3-7.1 { 266 execsql { 267 BEGIN; 268 CREATE TABLE y1(a); 269 CREATE TABLE y2(b); 270 CREATE TABLE y3(c); 271 CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN 272 INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1; 273 END; 274 INSERT INTO y1 VALUES(1); 275 INSERT INTO y1 VALUES(2); 276 INSERT INTO y1 SELECT a+2 FROM y1; 277 INSERT INTO y1 SELECT a+4 FROM y1; 278 INSERT INTO y1 SELECT a+8 FROM y1; 279 INSERT INTO y1 SELECT a+16 FROM y1; 280 INSERT INTO y2 SELECT a FROM y1; 281 COMMIT; 282 SELECT count(*) FROM y1; 283 } 284} 32 285do_test misc3-7.2 { 286 execsql { 287 DELETE FROM y1; 288 SELECT count(*) FROM y1; 289 } 290} 0 291do_test misc3-7.3 { 292 execsql { 293 SELECT count(*) FROM y3; 294 } 295} 32 296} ;# endif trigger 297 298# Ticket #668: VDBE stack overflow occurs when the left-hand side 299# of an IN expression is NULL and the result is used as an integer, not 300# as a jump. 301# 302do_test misc-8.1 { 303 execsql { 304 SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3 305 } 306} {2} 307do_test misc-8.2 { 308 execsql { 309 SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2 310 } 311} {2} 312 313finish_test 314