1*1da57d55SToomas Soome# 2c5c4113dSnw141292# 2003 December 17 3c5c4113dSnw141292# 4c5c4113dSnw141292# The author disclaims copyright to this source code. In place of 5c5c4113dSnw141292# a legal notice, here is a blessing: 6c5c4113dSnw141292# 7c5c4113dSnw141292# May you do good and not evil. 8c5c4113dSnw141292# May you find forgiveness for yourself and forgive others. 9c5c4113dSnw141292# May you share freely, never taking more than you give. 10c5c4113dSnw141292# 11c5c4113dSnw141292#*********************************************************************** 12c5c4113dSnw141292# This file implements regression tests for SQLite library. 13c5c4113dSnw141292# 14c5c4113dSnw141292# This file implements tests for miscellanous features that were 15c5c4113dSnw141292# left out of other test files. 16c5c4113dSnw141292# 17c5c4113dSnw141292# $Id: misc3.test,v 1.10 2004/03/17 23:32:08 drh Exp $ 18c5c4113dSnw141292 19c5c4113dSnw141292set testdir [file dirname $argv0] 20c5c4113dSnw141292source $testdir/tester.tcl 21c5c4113dSnw141292 22c5c4113dSnw141292# Ticket #529. Make sure an ABORT does not damage the in-memory cache 23c5c4113dSnw141292# that will be used by subsequent statements in the same transaction. 24c5c4113dSnw141292# 25c5c4113dSnw141292do_test misc3-1.1 { 26c5c4113dSnw141292 execsql { 27c5c4113dSnw141292 CREATE TABLE t1(a UNIQUE,b); 28c5c4113dSnw141292 INSERT INTO t1 29c5c4113dSnw141292 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_'); 30c5c4113dSnw141292 UPDATE t1 SET b=b||b; 31c5c4113dSnw141292 UPDATE t1 SET b=b||b; 32c5c4113dSnw141292 UPDATE t1 SET b=b||b; 33c5c4113dSnw141292 UPDATE t1 SET b=b||b; 34c5c4113dSnw141292 UPDATE t1 SET b=b||b; 35c5c4113dSnw141292 INSERT INTO t1 VALUES(2,'x'); 36c5c4113dSnw141292 UPDATE t1 SET b=substr(b,1,500); 37c5c4113dSnw141292 BEGIN; 38c5c4113dSnw141292 } 39c5c4113dSnw141292 catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';} 40c5c4113dSnw141292 execsql { 41c5c4113dSnw141292 CREATE TABLE t2(x,y); 42c5c4113dSnw141292 COMMIT; 43c5c4113dSnw141292 PRAGMA integrity_check; 44c5c4113dSnw141292 } 45c5c4113dSnw141292} ok 46c5c4113dSnw141292do_test misc3-1.2 { 47c5c4113dSnw141292 execsql { 48c5c4113dSnw141292 DROP TABLE t1; 49c5c4113dSnw141292 DROP TABLE t2; 50c5c4113dSnw141292 VACUUM; 51c5c4113dSnw141292 CREATE TABLE t1(a UNIQUE,b); 52c5c4113dSnw141292 INSERT INTO t1 53c5c4113dSnw141292 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_'); 54c5c4113dSnw141292 INSERT INTO t1 SELECT a+1, b||b FROM t1; 55c5c4113dSnw141292 INSERT INTO t1 SELECT a+2, b||b FROM t1; 56c5c4113dSnw141292 INSERT INTO t1 SELECT a+4, b FROM t1; 57c5c4113dSnw141292 INSERT INTO t1 SELECT a+8, b FROM t1; 58c5c4113dSnw141292 INSERT INTO t1 SELECT a+16, b FROM t1; 59c5c4113dSnw141292 INSERT INTO t1 SELECT a+32, b FROM t1; 60c5c4113dSnw141292 INSERT INTO t1 SELECT a+64, b FROM t1; 61c5c4113dSnw141292 62c5c4113dSnw141292 BEGIN; 63c5c4113dSnw141292 } 64c5c4113dSnw141292 catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';} 65c5c4113dSnw141292 execsql { 66c5c4113dSnw141292 INSERT INTO t1 VALUES(200,'hello out there'); 67c5c4113dSnw141292 COMMIT; 68c5c4113dSnw141292 PRAGMA integrity_check; 69c5c4113dSnw141292 } 70c5c4113dSnw141292} ok 71c5c4113dSnw141292 72c5c4113dSnw141292# Tests of the sqliteAtoF() function in util.c 73c5c4113dSnw141292# 74c5c4113dSnw141292do_test misc3-2.1 { 75c5c4113dSnw141292 execsql {SELECT 2e-25*0.5e25} 76c5c4113dSnw141292} 1 77c5c4113dSnw141292do_test misc3-2.2 { 78c5c4113dSnw141292 execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025} 79c5c4113dSnw141292} 1 80c5c4113dSnw141292do_test misc3-2.3 { 81c5c4113dSnw141292 execsql {SELECT 000000000002e-0000000025*0.5e25} 82c5c4113dSnw141292} 1 83c5c4113dSnw141292do_test misc3-2.4 { 84c5c4113dSnw141292 execsql {SELECT 2e-25*0.5e250} 85c5c4113dSnw141292} 1e+225 86c5c4113dSnw141292do_test misc3-2.5 { 87c5c4113dSnw141292 execsql {SELECT 2.0e-250*0.5e25} 88c5c4113dSnw141292} 1e-225 89c5c4113dSnw141292do_test misc3-2.6 { 90c5c4113dSnw141292 execsql {SELECT '-2.0e-127' * '-0.5e27'} 91c5c4113dSnw141292} 1e-100 92c5c4113dSnw141292do_test misc3-2.7 { 93c5c4113dSnw141292 execsql {SELECT '+2.0e-127' * '-0.5e27'} 94c5c4113dSnw141292} -1e-100 95c5c4113dSnw141292do_test misc3-2.8 { 96c5c4113dSnw141292 execsql {SELECT 2.0e-27 * '+0.5e+127'} 97c5c4113dSnw141292} 1e+100 98c5c4113dSnw141292do_test misc3-2.9 { 99c5c4113dSnw141292 execsql {SELECT 2.0e-27 * '+0.000005e+132'} 100c5c4113dSnw141292} 1e+100 101c5c4113dSnw141292 102c5c4113dSnw141292# Ticket #522. Make sure integer overflow is handled properly in 103c5c4113dSnw141292# indices. 104c5c4113dSnw141292# 105c5c4113dSnw141292do_test misc3-3.1 { 106c5c4113dSnw141292 execsql {PRAGMA integrity_check} 107c5c4113dSnw141292} ok 108c5c4113dSnw141292do_test misc3-3.2 { 109c5c4113dSnw141292 execsql { 110c5c4113dSnw141292 CREATE TABLE t2(a INT UNIQUE); 111c5c4113dSnw141292 PRAGMA integrity_check; 112c5c4113dSnw141292 } 113c5c4113dSnw141292} ok 114c5c4113dSnw141292do_test misc3-3.3 { 115c5c4113dSnw141292 execsql { 116c5c4113dSnw141292 INSERT INTO t2 VALUES(2147483648); 117c5c4113dSnw141292 PRAGMA integrity_check; 118c5c4113dSnw141292 } 119c5c4113dSnw141292} ok 120c5c4113dSnw141292do_test misc3-3.4 { 121c5c4113dSnw141292 execsql { 122c5c4113dSnw141292 INSERT INTO t2 VALUES(-2147483649); 123c5c4113dSnw141292 PRAGMA integrity_check; 124c5c4113dSnw141292 } 125c5c4113dSnw141292} ok 126c5c4113dSnw141292do_test misc3-3.5 { 127c5c4113dSnw141292 execsql { 128c5c4113dSnw141292 INSERT INTO t2 VALUES(+2147483649); 129c5c4113dSnw141292 PRAGMA integrity_check; 130c5c4113dSnw141292 } 131c5c4113dSnw141292} ok 132c5c4113dSnw141292do_test misc3-3.6 { 133c5c4113dSnw141292 execsql { 134c5c4113dSnw141292 INSERT INTO t2 VALUES(+2147483647); 135c5c4113dSnw141292 INSERT INTO t2 VALUES(-2147483648); 136c5c4113dSnw141292 INSERT INTO t2 VALUES(-2147483647); 137c5c4113dSnw141292 INSERT INTO t2 VALUES(2147483646); 138c5c4113dSnw141292 SELECT * FROM t2 ORDER BY a; 139c5c4113dSnw141292 } 140c5c4113dSnw141292} {-2147483649 -2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} 141c5c4113dSnw141292do_test misc3-3.7 { 142c5c4113dSnw141292 execsql { 143c5c4113dSnw141292 SELECT * FROM t2 WHERE a>=-2147483648 ORDER BY a; 144c5c4113dSnw141292 } 145c5c4113dSnw141292} {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} 146c5c4113dSnw141292do_test misc3-3.8 { 147c5c4113dSnw141292 execsql { 148c5c4113dSnw141292 SELECT * FROM t2 WHERE a>-2147483648 ORDER BY a; 149c5c4113dSnw141292 } 150c5c4113dSnw141292} {-2147483647 2147483646 2147483647 2147483648 2147483649} 151c5c4113dSnw141292do_test misc3-3.9 { 152c5c4113dSnw141292 execsql { 153c5c4113dSnw141292 SELECT * FROM t2 WHERE a>-2147483649 ORDER BY a; 154c5c4113dSnw141292 } 155c5c4113dSnw141292} {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} 156c5c4113dSnw141292do_test misc3-3.10 { 157c5c4113dSnw141292 execsql { 158c5c4113dSnw141292 SELECT * FROM t2 WHERE a>=0 AND a<2147483649 ORDER BY a DESC; 159c5c4113dSnw141292 } 160c5c4113dSnw141292} {2147483648 2147483647 2147483646} 161c5c4113dSnw141292do_test misc3-3.11 { 162c5c4113dSnw141292 execsql { 163c5c4113dSnw141292 SELECT * FROM t2 WHERE a>=0 AND a<=2147483648 ORDER BY a DESC; 164c5c4113dSnw141292 } 165c5c4113dSnw141292} {2147483648 2147483647 2147483646} 166c5c4113dSnw141292do_test misc3-3.12 { 167c5c4113dSnw141292 execsql { 168c5c4113dSnw141292 SELECT * FROM t2 WHERE a>=0 AND a<2147483648 ORDER BY a DESC; 169c5c4113dSnw141292 } 170c5c4113dSnw141292} {2147483647 2147483646} 171c5c4113dSnw141292do_test misc3-3.13 { 172c5c4113dSnw141292 execsql { 173c5c4113dSnw141292 SELECT * FROM t2 WHERE a>=0 AND a<=2147483647 ORDER BY a DESC; 174c5c4113dSnw141292 } 175c5c4113dSnw141292} {2147483647 2147483646} 176c5c4113dSnw141292do_test misc3-3.14 { 177c5c4113dSnw141292 execsql { 178c5c4113dSnw141292 SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC; 179c5c4113dSnw141292 } 180c5c4113dSnw141292} {2147483646} 181c5c4113dSnw141292 182c5c4113dSnw141292# Ticket #565. A stack overflow is occurring when the subquery to the 183c5c4113dSnw141292# right of an IN operator contains many NULLs 184c5c4113dSnw141292# 185c5c4113dSnw141292do_test misc3-4.1 { 186c5c4113dSnw141292 execsql { 187c5c4113dSnw141292 CREATE TABLE t3(a INTEGER PRIMARY KEY, b); 188c5c4113dSnw141292 INSERT INTO t3(b) VALUES('abc'); 189c5c4113dSnw141292 INSERT INTO t3(b) VALUES('xyz'); 190c5c4113dSnw141292 INSERT INTO t3(b) VALUES(NULL); 191c5c4113dSnw141292 INSERT INTO t3(b) VALUES(NULL); 192c5c4113dSnw141292 INSERT INTO t3(b) SELECT b||'d' FROM t3; 193c5c4113dSnw141292 INSERT INTO t3(b) SELECT b||'e' FROM t3; 194c5c4113dSnw141292 INSERT INTO t3(b) SELECT b||'f' FROM t3; 195c5c4113dSnw141292 INSERT INTO t3(b) SELECT b||'g' FROM t3; 196c5c4113dSnw141292 INSERT INTO t3(b) SELECT b||'h' FROM t3; 197c5c4113dSnw141292 SELECT count(a), count(b) FROM t3; 198c5c4113dSnw141292 } 199c5c4113dSnw141292} {128 64} 200c5c4113dSnw141292do_test misc3-4.2 { 201c5c4113dSnw141292 execsql { 202c5c4113dSnw141292 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3); 203c5c4113dSnw141292 } 204c5c4113dSnw141292} {64} 205c5c4113dSnw141292do_test misc3-4.3 { 206c5c4113dSnw141292 execsql { 207c5c4113dSnw141292 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1); 208c5c4113dSnw141292 } 209c5c4113dSnw141292} {64} 210c5c4113dSnw141292 211c5c4113dSnw141292# Ticket #601: Putting a left join inside "SELECT * FROM (<join-here>)" 212c5c4113dSnw141292# gives different results that if the outer "SELECT * FROM ..." is omitted. 213c5c4113dSnw141292# 214c5c4113dSnw141292do_test misc3-5.1 { 215c5c4113dSnw141292 execsql { 216c5c4113dSnw141292 CREATE TABLE x1 (b, c); 217c5c4113dSnw141292 INSERT INTO x1 VALUES('dog',3); 218c5c4113dSnw141292 INSERT INTO x1 VALUES('cat',1); 219c5c4113dSnw141292 INSERT INTO x1 VALUES('dog',4); 220c5c4113dSnw141292 CREATE TABLE x2 (c, e); 221c5c4113dSnw141292 INSERT INTO x2 VALUES(1,'one'); 222c5c4113dSnw141292 INSERT INTO x2 VALUES(2,'two'); 223c5c4113dSnw141292 INSERT INTO x2 VALUES(3,'three'); 224c5c4113dSnw141292 INSERT INTO x2 VALUES(4,'four'); 225c5c4113dSnw141292 SELECT x2.c AS c, e, b FROM x2 LEFT JOIN 226c5c4113dSnw141292 (SELECT b, max(c) AS c FROM x1 GROUP BY b) 227c5c4113dSnw141292 USING(c); 228c5c4113dSnw141292 } 229c5c4113dSnw141292} {1 one cat 2 two {} 3 three {} 4 four dog} 230c5c4113dSnw141292do_test misc4-5.2 { 231c5c4113dSnw141292 execsql { 232c5c4113dSnw141292 SELECT * FROM ( 233c5c4113dSnw141292 SELECT x2.c AS c, e, b FROM x2 LEFT JOIN 234c5c4113dSnw141292 (SELECT b, max(c) AS c FROM x1 GROUP BY b) 235c5c4113dSnw141292 USING(c) 236c5c4113dSnw141292 ); 237c5c4113dSnw141292 } 238c5c4113dSnw141292} {1 one cat 2 two {} 3 three {} 4 four dog} 239c5c4113dSnw141292 240c5c4113dSnw141292# Ticket #626: make sure EXPLAIN prevents BEGIN and COMMIT from working. 241c5c4113dSnw141292# 242c5c4113dSnw141292do_test misc3-6.1 { 243c5c4113dSnw141292 execsql {EXPLAIN BEGIN} 244c5c4113dSnw141292 catchsql {BEGIN} 245c5c4113dSnw141292} {0 {}} 246c5c4113dSnw141292do_test misc3-6.2 { 247c5c4113dSnw141292 execsql {EXPLAIN COMMIT} 248c5c4113dSnw141292 catchsql {COMMIT} 249c5c4113dSnw141292} {0 {}} 250c5c4113dSnw141292do_test misc3-6.3 { 251c5c4113dSnw141292 execsql {BEGIN; EXPLAIN ROLLBACK} 252c5c4113dSnw141292 catchsql {ROLLBACK} 253c5c4113dSnw141292} {0 {}} 254c5c4113dSnw141292 255c5c4113dSnw141292# Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside 256c5c4113dSnw141292# of a trigger. 257c5c4113dSnw141292# 258c5c4113dSnw141292do_test misc3-7.1 { 259c5c4113dSnw141292 execsql { 260c5c4113dSnw141292 BEGIN; 261c5c4113dSnw141292 CREATE TABLE y1(a); 262c5c4113dSnw141292 CREATE TABLE y2(b); 263c5c4113dSnw141292 CREATE TABLE y3(c); 264c5c4113dSnw141292 CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN 265c5c4113dSnw141292 INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1; 266c5c4113dSnw141292 END; 267c5c4113dSnw141292 INSERT INTO y1 VALUES(1); 268c5c4113dSnw141292 INSERT INTO y1 VALUES(2); 269c5c4113dSnw141292 INSERT INTO y1 SELECT a+2 FROM y1; 270c5c4113dSnw141292 INSERT INTO y1 SELECT a+4 FROM y1; 271c5c4113dSnw141292 INSERT INTO y1 SELECT a+8 FROM y1; 272c5c4113dSnw141292 INSERT INTO y1 SELECT a+16 FROM y1; 273c5c4113dSnw141292 INSERT INTO y2 SELECT a FROM y1; 274c5c4113dSnw141292 COMMIT; 275c5c4113dSnw141292 SELECT count(*) FROM y1; 276c5c4113dSnw141292 } 277c5c4113dSnw141292} 32 278c5c4113dSnw141292do_test misc3-7.2 { 279c5c4113dSnw141292 execsql { 280c5c4113dSnw141292 DELETE FROM y1; 281c5c4113dSnw141292 SELECT count(*) FROM y1; 282c5c4113dSnw141292 } 283c5c4113dSnw141292} 0 284c5c4113dSnw141292do_test misc3-7.3 { 285c5c4113dSnw141292 execsql { 286c5c4113dSnw141292 SELECT count(*) FROM y3; 287c5c4113dSnw141292 } 288c5c4113dSnw141292} 32 289c5c4113dSnw141292 290c5c4113dSnw141292# Ticket #668: VDBE stack overflow occurs when the left-hand side 291c5c4113dSnw141292# of an IN expression is NULL and the result is used as an integer, not 292c5c4113dSnw141292# as a jump. 293c5c4113dSnw141292# 294c5c4113dSnw141292do_test misc-8.1 { 295c5c4113dSnw141292 execsql { 296c5c4113dSnw141292 SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3 297c5c4113dSnw141292 } 298c5c4113dSnw141292} {2} 299c5c4113dSnw141292do_test misc-8.2 { 300c5c4113dSnw141292 execsql { 301c5c4113dSnw141292 SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2 302c5c4113dSnw141292 } 303c5c4113dSnw141292} {2} 304c5c4113dSnw141292 305c5c4113dSnw141292finish_test 306