1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/misc3.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,347 @@
1.4 +# 2003 December 17
1.5 +#
1.6 +# Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.
1.7 +#
1.8 +# The author disclaims copyright to this source code. In place of
1.9 +# a legal notice, here is a blessing:
1.10 +#
1.11 +# May you do good and not evil.
1.12 +# May you find forgiveness for yourself and forgive others.
1.13 +# May you share freely, never taking more than you give.
1.14 +#
1.15 +#***********************************************************************
1.16 +# This file implements regression tests for SQLite library.
1.17 +#
1.18 +# This file implements tests for miscellanous features that were
1.19 +# left out of other test files.
1.20 +#
1.21 +# $Id: misc3.test,v 1.19 2008/06/25 02:47:57 drh Exp $
1.22 +
1.23 +set testdir [file dirname $argv0]
1.24 +source $testdir/tester.tcl
1.25 +
1.26 +ifcapable {integrityck} {
1.27 + # Ticket #529. Make sure an ABORT does not damage the in-memory cache
1.28 + # that will be used by subsequent statements in the same transaction.
1.29 + #
1.30 + do_test misc3-1.1 {
1.31 + execsql {
1.32 + CREATE TABLE t1(a UNIQUE,b);
1.33 + INSERT INTO t1
1.34 + VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
1.35 + UPDATE t1 SET b=b||b;
1.36 + UPDATE t1 SET b=b||b;
1.37 + UPDATE t1 SET b=b||b;
1.38 + UPDATE t1 SET b=b||b;
1.39 + UPDATE t1 SET b=b||b;
1.40 + INSERT INTO t1 VALUES(2,'x');
1.41 + UPDATE t1 SET b=substr(b,1,500);
1.42 + BEGIN;
1.43 + }
1.44 + catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';}
1.45 + execsql {
1.46 + CREATE TABLE t2(x,y);
1.47 + COMMIT;
1.48 + PRAGMA integrity_check;
1.49 + }
1.50 + } ok
1.51 +}
1.52 +ifcapable {integrityck} {
1.53 + do_test misc3-1.2 {
1.54 + execsql {
1.55 + DROP TABLE t1;
1.56 + DROP TABLE t2;
1.57 + }
1.58 + ifcapable {vacuum} {execsql VACUUM}
1.59 + execsql {
1.60 + CREATE TABLE t1(a UNIQUE,b);
1.61 + INSERT INTO t1
1.62 + VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
1.63 + INSERT INTO t1 SELECT a+1, b||b FROM t1;
1.64 + INSERT INTO t1 SELECT a+2, b||b FROM t1;
1.65 + INSERT INTO t1 SELECT a+4, b FROM t1;
1.66 + INSERT INTO t1 SELECT a+8, b FROM t1;
1.67 + INSERT INTO t1 SELECT a+16, b FROM t1;
1.68 + INSERT INTO t1 SELECT a+32, b FROM t1;
1.69 + INSERT INTO t1 SELECT a+64, b FROM t1;
1.70 + BEGIN;
1.71 + }
1.72 + catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';}
1.73 + execsql {
1.74 + INSERT INTO t1 VALUES(200,'hello out there');
1.75 + COMMIT;
1.76 + PRAGMA integrity_check;
1.77 + }
1.78 + } ok
1.79 +}
1.80 +
1.81 +# Tests of the sqliteAtoF() function in util.c
1.82 +#
1.83 +do_test misc3-2.1 {
1.84 + execsql {SELECT 2e-25*0.5e25}
1.85 +} 1.0
1.86 +do_test misc3-2.2 {
1.87 + execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025}
1.88 +} 1.0
1.89 +do_test misc3-2.3 {
1.90 + execsql {SELECT 000000000002e-0000000025*0.5e25}
1.91 +} 1.0
1.92 +do_test misc3-2.4 {
1.93 + execsql {SELECT 2e-25*0.5e250}
1.94 +} 1e+225
1.95 +do_test misc3-2.5 {
1.96 + execsql {SELECT 2.0e-250*0.5e25}
1.97 +} 1e-225
1.98 +#
1.99 +# Symbian OS: the next test fails due to problems in sprintf/printf formatting.
1.100 +if {$::tcl_platform(platform)!="symbian"} {
1.101 + do_test misc3-2.6 {
1.102 + execsql {SELECT '-2.0e-127' * '-0.5e27'}
1.103 + } 1e-100
1.104 + do_test misc3-2.7 {
1.105 + execsql {SELECT '+2.0e-127' * '-0.5e27'}
1.106 + } -1e-100
1.107 +}
1.108 +do_test misc3-2.8 {
1.109 + execsql {SELECT 2.0e-27 * '+0.5e+127'}
1.110 +} 1e+100
1.111 +do_test misc3-2.9 {
1.112 + execsql {SELECT 2.0e-27 * '+0.000005e+132'}
1.113 +} 1e+100
1.114 +
1.115 +# Ticket #522. Make sure integer overflow is handled properly in
1.116 +# indices.
1.117 +#
1.118 +integrity_check misc3-3.1
1.119 +do_test misc3-3.2 {
1.120 + execsql {
1.121 + CREATE TABLE t2(a INT UNIQUE);
1.122 + }
1.123 +} {}
1.124 +integrity_check misc3-3.2.1
1.125 +do_test misc3-3.3 {
1.126 + execsql {
1.127 + INSERT INTO t2 VALUES(2147483648);
1.128 + }
1.129 +} {}
1.130 +integrity_check misc3-3.3.1
1.131 +do_test misc3-3.4 {
1.132 + execsql {
1.133 + INSERT INTO t2 VALUES(-2147483649);
1.134 + }
1.135 +} {}
1.136 +integrity_check misc3-3.4.1
1.137 +do_test misc3-3.5 {
1.138 + execsql {
1.139 + INSERT INTO t2 VALUES(+2147483649);
1.140 + }
1.141 +} {}
1.142 +integrity_check misc3-3.5.1
1.143 +do_test misc3-3.6 {
1.144 + execsql {
1.145 + INSERT INTO t2 VALUES(+2147483647);
1.146 + INSERT INTO t2 VALUES(-2147483648);
1.147 + INSERT INTO t2 VALUES(-2147483647);
1.148 + INSERT INTO t2 VALUES(2147483646);
1.149 + SELECT * FROM t2 ORDER BY a;
1.150 + }
1.151 +} {-2147483649 -2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}
1.152 +do_test misc3-3.7 {
1.153 + execsql {
1.154 + SELECT * FROM t2 WHERE a>=-2147483648 ORDER BY a;
1.155 + }
1.156 +} {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}
1.157 +do_test misc3-3.8 {
1.158 + execsql {
1.159 + SELECT * FROM t2 WHERE a>-2147483648 ORDER BY a;
1.160 + }
1.161 +} {-2147483647 2147483646 2147483647 2147483648 2147483649}
1.162 +do_test misc3-3.9 {
1.163 + execsql {
1.164 + SELECT * FROM t2 WHERE a>-2147483649 ORDER BY a;
1.165 + }
1.166 +} {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}
1.167 +do_test misc3-3.10 {
1.168 + execsql {
1.169 + SELECT * FROM t2 WHERE a>=0 AND a<2147483649 ORDER BY a DESC;
1.170 + }
1.171 +} {2147483648 2147483647 2147483646}
1.172 +do_test misc3-3.11 {
1.173 + execsql {
1.174 + SELECT * FROM t2 WHERE a>=0 AND a<=2147483648 ORDER BY a DESC;
1.175 + }
1.176 +} {2147483648 2147483647 2147483646}
1.177 +do_test misc3-3.12 {
1.178 + execsql {
1.179 + SELECT * FROM t2 WHERE a>=0 AND a<2147483648 ORDER BY a DESC;
1.180 + }
1.181 +} {2147483647 2147483646}
1.182 +do_test misc3-3.13 {
1.183 + execsql {
1.184 + SELECT * FROM t2 WHERE a>=0 AND a<=2147483647 ORDER BY a DESC;
1.185 + }
1.186 +} {2147483647 2147483646}
1.187 +do_test misc3-3.14 {
1.188 + execsql {
1.189 + SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC;
1.190 + }
1.191 +} {2147483646}
1.192 +
1.193 +# Ticket #565. A stack overflow is occurring when the subquery to the
1.194 +# right of an IN operator contains many NULLs
1.195 +#
1.196 +do_test misc3-4.1 {
1.197 + execsql {
1.198 + CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
1.199 + INSERT INTO t3(b) VALUES('abc');
1.200 + INSERT INTO t3(b) VALUES('xyz');
1.201 + INSERT INTO t3(b) VALUES(NULL);
1.202 + INSERT INTO t3(b) VALUES(NULL);
1.203 + INSERT INTO t3(b) SELECT b||'d' FROM t3;
1.204 + INSERT INTO t3(b) SELECT b||'e' FROM t3;
1.205 + INSERT INTO t3(b) SELECT b||'f' FROM t3;
1.206 + INSERT INTO t3(b) SELECT b||'g' FROM t3;
1.207 + INSERT INTO t3(b) SELECT b||'h' FROM t3;
1.208 + SELECT count(a), count(b) FROM t3;
1.209 + }
1.210 +} {128 64}
1.211 +ifcapable subquery {
1.212 +do_test misc3-4.2 {
1.213 + execsql {
1.214 + SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3);
1.215 + }
1.216 + } {64}
1.217 + do_test misc3-4.3 {
1.218 + execsql {
1.219 + SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);
1.220 + }
1.221 + } {64}
1.222 +}
1.223 +
1.224 +# Ticket #601: Putting a left join inside "SELECT * FROM (<join-here>)"
1.225 +# gives different results that if the outer "SELECT * FROM ..." is omitted.
1.226 +#
1.227 +ifcapable subquery {
1.228 + do_test misc3-5.1 {
1.229 + execsql {
1.230 + CREATE TABLE x1 (b, c);
1.231 + INSERT INTO x1 VALUES('dog',3);
1.232 + INSERT INTO x1 VALUES('cat',1);
1.233 + INSERT INTO x1 VALUES('dog',4);
1.234 + CREATE TABLE x2 (c, e);
1.235 + INSERT INTO x2 VALUES(1,'one');
1.236 + INSERT INTO x2 VALUES(2,'two');
1.237 + INSERT INTO x2 VALUES(3,'three');
1.238 + INSERT INTO x2 VALUES(4,'four');
1.239 + SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
1.240 + (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)
1.241 + USING(c);
1.242 + }
1.243 + } {1 one cat 2 two {} 3 three {} 4 four dog}
1.244 + do_test misc3-5.2 {
1.245 + execsql {
1.246 + SELECT * FROM (
1.247 + SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
1.248 + (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)
1.249 + USING(c)
1.250 + );
1.251 + }
1.252 + } {1 one cat 2 two {} 3 three {} 4 four dog}
1.253 +}
1.254 +
1.255 +ifcapable {explain} {
1.256 + # Ticket #626: make sure EXPLAIN prevents BEGIN and COMMIT from working.
1.257 + #
1.258 + do_test misc3-6.1 {
1.259 + execsql {EXPLAIN BEGIN}
1.260 + catchsql {BEGIN}
1.261 + } {0 {}}
1.262 + do_test misc3-6.2 {
1.263 + execsql {EXPLAIN COMMIT}
1.264 + catchsql {COMMIT}
1.265 + } {0 {}}
1.266 + do_test misc3-6.3 {
1.267 + execsql {BEGIN; EXPLAIN ROLLBACK}
1.268 + catchsql {ROLLBACK}
1.269 + } {0 {}}
1.270 +
1.271 + # Do some additional EXPLAIN operations to exercise the displayP4 logic.
1.272 + do_test misc3-6.10 {
1.273 + set x [execsql {
1.274 + CREATE TABLE ex1(
1.275 + a INTEGER DEFAULT 54321,
1.276 + b TEXT DEFAULT "hello",
1.277 + c REAL DEFAULT 3.1415926
1.278 + );
1.279 + CREATE UNIQUE INDEX ex1i1 ON ex1(a);
1.280 + EXPLAIN REINDEX;
1.281 + }]
1.282 + regexp { IsUnique \d+ \d+ \d+ \d+ } $x
1.283 + } {1}
1.284 + do_test misc3-6.11 {
1.285 + set x [execsql {
1.286 + EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC
1.287 + }]
1.288 + set y [regexp { 123456789012 } $x]
1.289 + lappend y [regexp { 4.5678 } $x]
1.290 + lappend y [regexp { hello } $x]
1.291 + lappend y [regexp {,-BINARY} $x]
1.292 + } {1 1 1 1}
1.293 +
1.294 +}
1.295 +
1.296 +ifcapable {trigger} {
1.297 +# Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside
1.298 +# of a trigger.
1.299 +#
1.300 +do_test misc3-7.1 {
1.301 + execsql {
1.302 + BEGIN;
1.303 + CREATE TABLE y1(a);
1.304 + CREATE TABLE y2(b);
1.305 + CREATE TABLE y3(c);
1.306 + CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN
1.307 + INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1;
1.308 + END;
1.309 + INSERT INTO y1 VALUES(1);
1.310 + INSERT INTO y1 VALUES(2);
1.311 + INSERT INTO y1 SELECT a+2 FROM y1;
1.312 + INSERT INTO y1 SELECT a+4 FROM y1;
1.313 + INSERT INTO y1 SELECT a+8 FROM y1;
1.314 + INSERT INTO y1 SELECT a+16 FROM y1;
1.315 + INSERT INTO y2 SELECT a FROM y1;
1.316 + COMMIT;
1.317 + SELECT count(*) FROM y1;
1.318 + }
1.319 +} 32
1.320 +do_test misc3-7.2 {
1.321 + execsql {
1.322 + DELETE FROM y1;
1.323 + SELECT count(*) FROM y1;
1.324 + }
1.325 +} 0
1.326 +do_test misc3-7.3 {
1.327 + execsql {
1.328 + SELECT count(*) FROM y3;
1.329 + }
1.330 +} 32
1.331 +} ;# endif trigger
1.332 +
1.333 +# Ticket #668: VDBE stack overflow occurs when the left-hand side
1.334 +# of an IN expression is NULL and the result is used as an integer, not
1.335 +# as a jump.
1.336 +#
1.337 +ifcapable subquery {
1.338 + do_test misc-8.1 {
1.339 + execsql {
1.340 + SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3
1.341 + }
1.342 + } {2}
1.343 + do_test misc-8.2 {
1.344 + execsql {
1.345 + SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2
1.346 + }
1.347 + } {2}
1.348 +}
1.349 +
1.350 +finish_test