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.6 2004/01/14 21:59:24 drh Exp $ 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# Ticket #529. Make sure an ABORT does not damage the in-memory cache 22# that will be used by subsequent statements in the same transaction. 23# 24do_test misc3-1.1 { 25 execsql { 26 CREATE TABLE t1(a UNIQUE,b); 27 INSERT INTO t1 28 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_'); 29 UPDATE t1 SET b=b||b; 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 INSERT INTO t1 VALUES(2,'x'); 35 UPDATE t1 SET b=substr(b,1,500); 36 BEGIN; 37 } 38 catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';} 39 execsql { 40 CREATE TABLE t2(x,y); 41 COMMIT; 42 PRAGMA integrity_check; 43 } 44} ok 45do_test misc3-1.2 { 46 execsql { 47 DROP TABLE t1; 48 DROP TABLE t2; 49 VACUUM; 50 CREATE TABLE t1(a UNIQUE,b); 51 INSERT INTO t1 52 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_'); 53 INSERT INTO t1 SELECT a+1, b||b FROM t1; 54 INSERT INTO t1 SELECT a+2, b||b FROM t1; 55 INSERT INTO t1 SELECT a+4, b FROM t1; 56 INSERT INTO t1 SELECT a+8, b FROM t1; 57 INSERT INTO t1 SELECT a+16, b FROM t1; 58 INSERT INTO t1 SELECT a+32, b FROM t1; 59 INSERT INTO t1 SELECT a+64, b FROM t1; 60 61 BEGIN; 62 } 63 catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';} 64 execsql { 65 INSERT INTO t1 VALUES(200,'hello out there'); 66 COMMIT; 67 PRAGMA integrity_check; 68 } 69} ok 70 71# Tests of the sqliteAtoF() function in util.c 72# 73do_test misc3-2.1 { 74 execsql {SELECT 2e-25*0.5e25} 75} 1 76do_test misc3-2.2 { 77 execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025} 78} 1 79do_test misc3-2.3 { 80 execsql {SELECT 000000000002e-0000000025*0.5e25} 81} 1 82do_test misc3-2.4 { 83 execsql {SELECT 2e-25*0.5e250} 84} 1e+225 85do_test misc3-2.5 { 86 execsql {SELECT 2.0e-250*0.5e25} 87} 1e-225 88do_test misc3-2.6 { 89 execsql {SELECT '-2.0e-127' * '-0.5e27'} 90} 1e-100 91do_test misc3-2.7 { 92 execsql {SELECT '+2.0e-127' * '-0.5e27'} 93} -1e-100 94do_test misc3-2.8 { 95 execsql {SELECT 2.0e-27 * '+0.5e+127'} 96} 1e+100 97do_test misc3-2.9 { 98 execsql {SELECT 2.0e-27 * '+0.000005e+132'} 99} 1e+100 100 101# Ticket #522. Make sure integer overflow is handled properly in 102# indices. 103# 104do_test misc3-3.1 { 105 execsql {PRAGMA integrity_check} 106} ok 107do_test misc3-3.2 { 108 execsql { 109 CREATE TABLE t2(a INT UNIQUE); 110 PRAGMA integrity_check; 111 } 112} ok 113do_test misc3-3.3 { 114 execsql { 115 INSERT INTO t2 VALUES(2147483648); 116 PRAGMA integrity_check; 117 } 118} ok 119do_test misc3-3.4 { 120 execsql { 121 INSERT INTO t2 VALUES(-2147483649); 122 PRAGMA integrity_check; 123 } 124} ok 125do_test misc3-3.5 { 126 execsql { 127 INSERT INTO t2 VALUES(+2147483649); 128 PRAGMA integrity_check; 129 } 130} ok 131do_test misc3-3.6 { 132 execsql { 133 INSERT INTO t2 VALUES(+2147483647); 134 INSERT INTO t2 VALUES(-2147483648); 135 INSERT INTO t2 VALUES(-2147483647); 136 INSERT INTO t2 VALUES(2147483646); 137 SELECT * FROM t2 ORDER BY a; 138 } 139} {-2147483649 -2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} 140do_test misc3-3.7 { 141 execsql { 142 SELECT * FROM t2 WHERE a>=-2147483648 ORDER BY a; 143 } 144} {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} 145do_test misc3-3.8 { 146 execsql { 147 SELECT * FROM t2 WHERE a>-2147483648 ORDER BY a; 148 } 149} {-2147483647 2147483646 2147483647 2147483648 2147483649} 150do_test misc3-3.9 { 151 execsql { 152 SELECT * FROM t2 WHERE a>-2147483649 ORDER BY a; 153 } 154} {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} 155do_test misc3-3.10 { 156 execsql { 157 SELECT * FROM t2 WHERE a>=0 AND a<2147483649 ORDER BY a DESC; 158 } 159} {2147483648 2147483647 2147483646} 160do_test misc3-3.11 { 161 execsql { 162 SELECT * FROM t2 WHERE a>=0 AND a<=2147483648 ORDER BY a DESC; 163 } 164} {2147483648 2147483647 2147483646} 165do_test misc3-3.12 { 166 execsql { 167 SELECT * FROM t2 WHERE a>=0 AND a<2147483648 ORDER BY a DESC; 168 } 169} {2147483647 2147483646} 170do_test misc3-3.13 { 171 execsql { 172 SELECT * FROM t2 WHERE a>=0 AND a<=2147483647 ORDER BY a DESC; 173 } 174} {2147483647 2147483646} 175do_test misc3-3.14 { 176 execsql { 177 SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC; 178 } 179} {2147483646} 180 181# Ticket #565. A stack overflow is occurring when the subquery to the 182# right of an IN operator contains many NULLs 183# 184do_test misc3-4.1 { 185 execsql { 186 CREATE TABLE t3(a INTEGER PRIMARY KEY, b); 187 INSERT INTO t3(b) VALUES('abc'); 188 INSERT INTO t3(b) VALUES('xyz'); 189 INSERT INTO t3(b) VALUES(NULL); 190 INSERT INTO t3(b) VALUES(NULL); 191 INSERT INTO t3(b) SELECT b||'d' FROM t3; 192 INSERT INTO t3(b) SELECT b||'e' FROM t3; 193 INSERT INTO t3(b) SELECT b||'f' FROM t3; 194 INSERT INTO t3(b) SELECT b||'g' FROM t3; 195 INSERT INTO t3(b) SELECT b||'h' FROM t3; 196 SELECT count(a), count(b) FROM t3; 197 } 198} {128 64} 199do_test misc3-4.2 { 200 execsql { 201 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3); 202 } 203} {64} 204do_test misc3-4.3 { 205 execsql { 206 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1); 207 } 208} {64} 209 210 211finish_test 212