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.20 2009/05/06 00:49:01 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} 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 JOIN 231 (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 JOIN 239 (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.1415926 269 ); 270 CREATE UNIQUE INDEX ex1i1 ON ex1(a); 271 EXPLAIN REINDEX; 272 }] 273 regexp { IsUnique \d+ \d+ \d+ \d+ } $x 274 } {1} 275 if {[regexp {16} [db one {PRAGMA encoding}]]} { 276 do_test misc3-6.11-utf16 { 277 set x [execsql { 278 EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC 279 }] 280 set y [regexp { 123456789012 } $x] 281 lappend y [regexp { 4.5678 } $x] 282 lappend y [regexp {,-BINARY} $x] 283 } {1 1 1} 284 } else { 285 do_test misc3-6.11-utf8 { 286 set x [execsql { 287 EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC 288 }] 289 set y [regexp { 123456789012 } $x] 290 lappend y [regexp { 4.5678 } $x] 291 lappend y [regexp { hello } $x] 292 lappend y [regexp {,-BINARY} $x] 293 } {1 1 1 1} 294 } 295} 296 297ifcapable {trigger} { 298# Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside 299# of a trigger. 300# 301do_test misc3-7.1 { 302 execsql { 303 BEGIN; 304 CREATE TABLE y1(a); 305 CREATE TABLE y2(b); 306 CREATE TABLE y3(c); 307 CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN 308 INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1; 309 END; 310 INSERT INTO y1 VALUES(1); 311 INSERT INTO y1 VALUES(2); 312 INSERT INTO y1 SELECT a+2 FROM y1; 313 INSERT INTO y1 SELECT a+4 FROM y1; 314 INSERT INTO y1 SELECT a+8 FROM y1; 315 INSERT INTO y1 SELECT a+16 FROM y1; 316 INSERT INTO y2 SELECT a FROM y1; 317 COMMIT; 318 SELECT count(*) FROM y1; 319 } 320} 32 321do_test misc3-7.2 { 322 execsql { 323 DELETE FROM y1; 324 SELECT count(*) FROM y1; 325 } 326} 0 327do_test misc3-7.3 { 328 execsql { 329 SELECT count(*) FROM y3; 330 } 331} 32 332} ;# endif trigger 333 334# Ticket #668: VDBE stack overflow occurs when the left-hand side 335# of an IN expression is NULL and the result is used as an integer, not 336# as a jump. 337# 338ifcapable subquery { 339 do_test misc-8.1 { 340 execsql { 341 SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3 342 } 343 } {2} 344 do_test misc-8.2 { 345 execsql { 346 SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2 347 } 348 } {2} 349} 350 351finish_test 352