1 2#pragma ident "%Z%%M% %I% %E% SMI" 3 4# 2003 December 17 5# 6# The author disclaims copyright to this source code. In place of 7# a legal notice, here is a blessing: 8# 9# May you do good and not evil. 10# May you find forgiveness for yourself and forgive others. 11# May you share freely, never taking more than you give. 12# 13#*********************************************************************** 14# This file implements regression tests for SQLite library. 15# 16# This file implements tests for miscellanous features that were 17# left out of other test files. 18# 19# $Id: misc3.test,v 1.10 2004/03/17 23:32:08 drh Exp $ 20 21set testdir [file dirname $argv0] 22source $testdir/tester.tcl 23 24# Ticket #529. Make sure an ABORT does not damage the in-memory cache 25# that will be used by subsequent statements in the same transaction. 26# 27do_test misc3-1.1 { 28 execsql { 29 CREATE TABLE t1(a UNIQUE,b); 30 INSERT INTO t1 31 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_'); 32 UPDATE t1 SET b=b||b; 33 UPDATE t1 SET b=b||b; 34 UPDATE t1 SET b=b||b; 35 UPDATE t1 SET b=b||b; 36 UPDATE t1 SET b=b||b; 37 INSERT INTO t1 VALUES(2,'x'); 38 UPDATE t1 SET b=substr(b,1,500); 39 BEGIN; 40 } 41 catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';} 42 execsql { 43 CREATE TABLE t2(x,y); 44 COMMIT; 45 PRAGMA integrity_check; 46 } 47} ok 48do_test misc3-1.2 { 49 execsql { 50 DROP TABLE t1; 51 DROP TABLE t2; 52 VACUUM; 53 CREATE TABLE t1(a UNIQUE,b); 54 INSERT INTO t1 55 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_'); 56 INSERT INTO t1 SELECT a+1, b||b FROM t1; 57 INSERT INTO t1 SELECT a+2, b||b FROM t1; 58 INSERT INTO t1 SELECT a+4, b FROM t1; 59 INSERT INTO t1 SELECT a+8, b FROM t1; 60 INSERT INTO t1 SELECT a+16, b FROM t1; 61 INSERT INTO t1 SELECT a+32, b FROM t1; 62 INSERT INTO t1 SELECT a+64, b FROM t1; 63 64 BEGIN; 65 } 66 catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';} 67 execsql { 68 INSERT INTO t1 VALUES(200,'hello out there'); 69 COMMIT; 70 PRAGMA integrity_check; 71 } 72} ok 73 74# Tests of the sqliteAtoF() function in util.c 75# 76do_test misc3-2.1 { 77 execsql {SELECT 2e-25*0.5e25} 78} 1 79do_test misc3-2.2 { 80 execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025} 81} 1 82do_test misc3-2.3 { 83 execsql {SELECT 000000000002e-0000000025*0.5e25} 84} 1 85do_test misc3-2.4 { 86 execsql {SELECT 2e-25*0.5e250} 87} 1e+225 88do_test misc3-2.5 { 89 execsql {SELECT 2.0e-250*0.5e25} 90} 1e-225 91do_test misc3-2.6 { 92 execsql {SELECT '-2.0e-127' * '-0.5e27'} 93} 1e-100 94do_test misc3-2.7 { 95 execsql {SELECT '+2.0e-127' * '-0.5e27'} 96} -1e-100 97do_test misc3-2.8 { 98 execsql {SELECT 2.0e-27 * '+0.5e+127'} 99} 1e+100 100do_test misc3-2.9 { 101 execsql {SELECT 2.0e-27 * '+0.000005e+132'} 102} 1e+100 103 104# Ticket #522. Make sure integer overflow is handled properly in 105# indices. 106# 107do_test misc3-3.1 { 108 execsql {PRAGMA integrity_check} 109} ok 110do_test misc3-3.2 { 111 execsql { 112 CREATE TABLE t2(a INT UNIQUE); 113 PRAGMA integrity_check; 114 } 115} ok 116do_test misc3-3.3 { 117 execsql { 118 INSERT INTO t2 VALUES(2147483648); 119 PRAGMA integrity_check; 120 } 121} ok 122do_test misc3-3.4 { 123 execsql { 124 INSERT INTO t2 VALUES(-2147483649); 125 PRAGMA integrity_check; 126 } 127} ok 128do_test misc3-3.5 { 129 execsql { 130 INSERT INTO t2 VALUES(+2147483649); 131 PRAGMA integrity_check; 132 } 133} ok 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) 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) AS c FROM x1 GROUP BY b) 237 USING(c) 238 ); 239 } 240} {1 one cat 2 two {} 3 three {} 4 four dog} 241 242# Ticket #626: make sure EXPLAIN prevents BEGIN and COMMIT from working. 243# 244do_test misc3-6.1 { 245 execsql {EXPLAIN BEGIN} 246 catchsql {BEGIN} 247} {0 {}} 248do_test misc3-6.2 { 249 execsql {EXPLAIN COMMIT} 250 catchsql {COMMIT} 251} {0 {}} 252do_test misc3-6.3 { 253 execsql {BEGIN; EXPLAIN ROLLBACK} 254 catchsql {ROLLBACK} 255} {0 {}} 256 257# Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside 258# of a trigger. 259# 260do_test misc3-7.1 { 261 execsql { 262 BEGIN; 263 CREATE TABLE y1(a); 264 CREATE TABLE y2(b); 265 CREATE TABLE y3(c); 266 CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN 267 INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1; 268 END; 269 INSERT INTO y1 VALUES(1); 270 INSERT INTO y1 VALUES(2); 271 INSERT INTO y1 SELECT a+2 FROM y1; 272 INSERT INTO y1 SELECT a+4 FROM y1; 273 INSERT INTO y1 SELECT a+8 FROM y1; 274 INSERT INTO y1 SELECT a+16 FROM y1; 275 INSERT INTO y2 SELECT a FROM y1; 276 COMMIT; 277 SELECT count(*) FROM y1; 278 } 279} 32 280do_test misc3-7.2 { 281 execsql { 282 DELETE FROM y1; 283 SELECT count(*) FROM y1; 284 } 285} 0 286do_test misc3-7.3 { 287 execsql { 288 SELECT count(*) FROM y3; 289 } 290} 32 291 292# Ticket #668: VDBE stack overflow occurs when the left-hand side 293# of an IN expression is NULL and the result is used as an integer, not 294# as a jump. 295# 296do_test misc-8.1 { 297 execsql { 298 SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3 299 } 300} {2} 301do_test misc-8.2 { 302 execsql { 303 SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2 304 } 305} {2} 306 307finish_test 308