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.15 2004/11/04 14:47:13 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# 109integrity_check misc3-3.1 110do_test misc3-3.2 { 111 execsql { 112 CREATE TABLE t2(a INT UNIQUE); 113 } 114} {} 115integrity_check misc3-3.2.1 116do_test misc3-3.3 { 117 execsql { 118 INSERT INTO t2 VALUES(2147483648); 119 } 120} {} 121integrity_check misc3-3.3.1 122do_test misc3-3.4 { 123 execsql { 124 INSERT INTO t2 VALUES(-2147483649); 125 } 126} {} 127integrity_check misc3-3.4.1 128do_test misc3-3.5 { 129 execsql { 130 INSERT INTO t2 VALUES(+2147483649); 131 } 132} {} 133integrity_check misc3-3.5.1 134do_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 the 185# right of an IN operator contains many NULLs 186# 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} 202do_test misc3-4.2 { 203 execsql { 204 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3); 205 } 206} {64} 207do_test misc3-4.3 { 208 execsql { 209 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1); 210 } 211} {64} 212 213# Ticket #601: Putting a left join inside "SELECT * FROM (<join-here>)" 214# gives different results that if the outer "SELECT * FROM ..." is omitted. 215# 216do_test misc3-5.1 { 217 execsql { 218 CREATE TABLE x1 (b, c); 219 INSERT INTO x1 VALUES('dog',3); 220 INSERT INTO x1 VALUES('cat',1); 221 INSERT INTO x1 VALUES('dog',4); 222 CREATE TABLE x2 (c, e); 223 INSERT INTO x2 VALUES(1,'one'); 224 INSERT INTO x2 VALUES(2,'two'); 225 INSERT INTO x2 VALUES(3,'three'); 226 INSERT INTO x2 VALUES(4,'four'); 227 SELECT x2.c AS c, e, b FROM x2 LEFT JOIN 228 (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b) 229 USING(c); 230 } 231} {1 one cat 2 two {} 3 three {} 4 four dog} 232do_test misc4-5.2 { 233 execsql { 234 SELECT * FROM ( 235 SELECT x2.c AS c, e, b FROM x2 LEFT JOIN 236 (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b) 237 USING(c) 238 ); 239 } 240} {1 one cat 2 two {} 3 three {} 4 four dog} 241 242ifcapable {explain} { 243 # Ticket #626: make sure EXPLAIN prevents BEGIN and COMMIT from working. 244 # 245 do_test misc3-6.1 { 246 execsql {EXPLAIN BEGIN} 247 catchsql {BEGIN} 248 } {0 {}} 249 do_test misc3-6.2 { 250 execsql {EXPLAIN COMMIT} 251 catchsql {COMMIT} 252 } {0 {}} 253 do_test misc3-6.3 { 254 execsql {BEGIN; EXPLAIN ROLLBACK} 255 catchsql {ROLLBACK} 256 } {0 {}} 257} 258 259ifcapable {trigger} { 260# Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside 261# of a trigger. 262# 263do_test misc3-7.1 { 264 execsql { 265 BEGIN; 266 CREATE TABLE y1(a); 267 CREATE TABLE y2(b); 268 CREATE TABLE y3(c); 269 CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN 270 INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1; 271 END; 272 INSERT INTO y1 VALUES(1); 273 INSERT INTO y1 VALUES(2); 274 INSERT INTO y1 SELECT a+2 FROM y1; 275 INSERT INTO y1 SELECT a+4 FROM y1; 276 INSERT INTO y1 SELECT a+8 FROM y1; 277 INSERT INTO y1 SELECT a+16 FROM y1; 278 INSERT INTO y2 SELECT a FROM y1; 279 COMMIT; 280 SELECT count(*) FROM y1; 281 } 282} 32 283do_test misc3-7.2 { 284 execsql { 285 DELETE FROM y1; 286 SELECT count(*) FROM y1; 287 } 288} 0 289do_test misc3-7.3 { 290 execsql { 291 SELECT count(*) FROM y3; 292 } 293} 32 294} ;# endif trigger 295 296# Ticket #668: VDBE stack overflow occurs when the left-hand side 297# of an IN expression is NULL and the result is used as an integer, not 298# as a jump. 299# 300do_test misc-8.1 { 301 execsql { 302 SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3 303 } 304} {2} 305do_test misc-8.2 { 306 execsql { 307 SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2 308 } 309} {2} 310 311finish_test 312