1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/misc2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,435 @@
1.4 +# 2003 June 21
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# This file implements tests for miscellanous features that were
1.17 +# left out of other test files.
1.18 +#
1.19 +# $Id: misc2.test,v 1.28 2007/09/12 17:01:45 danielk1977 Exp $
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +ifcapable {trigger} {
1.25 +# Test for ticket #360
1.26 +#
1.27 +do_test misc2-1.1 {
1.28 + catchsql {
1.29 + CREATE TABLE FOO(bar integer);
1.30 + CREATE TRIGGER foo_insert BEFORE INSERT ON foo BEGIN
1.31 + SELECT CASE WHEN (NOT new.bar BETWEEN 0 AND 20)
1.32 + THEN raise(rollback, 'aiieee') END;
1.33 + END;
1.34 + INSERT INTO foo(bar) VALUES (1);
1.35 + }
1.36 +} {0 {}}
1.37 +do_test misc2-1.2 {
1.38 + catchsql {
1.39 + INSERT INTO foo(bar) VALUES (111);
1.40 + }
1.41 +} {1 aiieee}
1.42 +} ;# endif trigger
1.43 +
1.44 +# Make sure ROWID works on a view and a subquery. Ticket #364
1.45 +#
1.46 +do_test misc2-2.1 {
1.47 + execsql {
1.48 + CREATE TABLE t1(a,b,c);
1.49 + INSERT INTO t1 VALUES(1,2,3);
1.50 + CREATE TABLE t2(a,b,c);
1.51 + INSERT INTO t2 VALUES(7,8,9);
1.52 + }
1.53 +} {}
1.54 +ifcapable subquery {
1.55 + do_test misc2-2.2 {
1.56 + execsql {
1.57 + SELECT rowid, * FROM (SELECT * FROM t1, t2);
1.58 + }
1.59 + } {{} 1 2 3 7 8 9}
1.60 +}
1.61 +ifcapable view {
1.62 + do_test misc2-2.3 {
1.63 + execsql {
1.64 + CREATE VIEW v1 AS SELECT * FROM t1, t2;
1.65 + SELECT rowid, * FROM v1;
1.66 + }
1.67 + } {{} 1 2 3 7 8 9}
1.68 +} ;# ifcapable view
1.69 +
1.70 +# Ticket #2002 and #1952.
1.71 +ifcapable subquery {
1.72 + do_test misc2-2.4 {
1.73 + execsql2 {
1.74 + SELECT * FROM (SELECT a, b AS 'a', c AS 'a', 4 AS 'a' FROM t1)
1.75 + }
1.76 + } {a 1 a:1 2 a:2 3 a:3 4}
1.77 +}
1.78 +
1.79 +# Check name binding precedence. Ticket #387
1.80 +#
1.81 +do_test misc2-3.1 {
1.82 + catchsql {
1.83 + SELECT t1.b+t2.b AS a, t1.a, t2.a FROM t1, t2 WHERE a==10
1.84 + }
1.85 +} {1 {ambiguous column name: a}}
1.86 +
1.87 +# Make sure 32-bit integer overflow is handled properly in queries.
1.88 +# ticket #408
1.89 +#
1.90 +do_test misc2-4.1 {
1.91 + execsql {
1.92 + INSERT INTO t1 VALUES(4000000000,'a','b');
1.93 + SELECT a FROM t1 WHERE a>1;
1.94 + }
1.95 +} {4000000000}
1.96 +do_test misc2-4.2 {
1.97 + execsql {
1.98 + INSERT INTO t1 VALUES(2147483648,'b2','c2');
1.99 + INSERT INTO t1 VALUES(2147483647,'b3','c3');
1.100 + SELECT a FROM t1 WHERE a>2147483647;
1.101 + }
1.102 +} {4000000000 2147483648}
1.103 +do_test misc2-4.3 {
1.104 + execsql {
1.105 + SELECT a FROM t1 WHERE a<2147483648;
1.106 + }
1.107 +} {1 2147483647}
1.108 +do_test misc2-4.4 {
1.109 + execsql {
1.110 + SELECT a FROM t1 WHERE a<=2147483648;
1.111 + }
1.112 +} {1 2147483648 2147483647}
1.113 +do_test misc2-4.5 {
1.114 + execsql {
1.115 + SELECT a FROM t1 WHERE a<10000000000;
1.116 + }
1.117 +} {1 4000000000 2147483648 2147483647}
1.118 +do_test misc2-4.6 {
1.119 + execsql {
1.120 + SELECT a FROM t1 WHERE a<1000000000000 ORDER BY 1;
1.121 + }
1.122 +} {1 2147483647 2147483648 4000000000}
1.123 +
1.124 +# There were some issues with expanding a SrcList object using a call
1.125 +# to sqliteSrcListAppend() if the SrcList had previously been duplicated
1.126 +# using a call to sqliteSrcListDup(). Ticket #416. The following test
1.127 +# makes sure the problem has been fixed.
1.128 +#
1.129 +ifcapable view {
1.130 +do_test misc2-5.1 {
1.131 + execsql {
1.132 + CREATE TABLE x(a,b);
1.133 + CREATE VIEW y AS
1.134 + SELECT x1.b AS p, x2.b AS q FROM x AS x1, x AS x2 WHERE x1.a=x2.a;
1.135 + CREATE VIEW z AS
1.136 + SELECT y1.p, y2.p FROM y AS y1, y AS y2 WHERE y1.q=y2.q;
1.137 + SELECT * from z;
1.138 + }
1.139 +} {}
1.140 +}
1.141 +
1.142 +# Make sure we can open a database with an empty filename. What this
1.143 +# does is store the database in a temporary file that is deleted when
1.144 +# the database is closed. Ticket #432.
1.145 +#
1.146 +do_test misc2-6.1 {
1.147 + db close
1.148 + sqlite3 db {}
1.149 + execsql {
1.150 + CREATE TABLE t1(a,b);
1.151 + INSERT INTO t1 VALUES(1,2);
1.152 + SELECT * FROM t1;
1.153 + }
1.154 +} {1 2}
1.155 +
1.156 +# Make sure we get an error message (not a segfault) on an attempt to
1.157 +# update a table from within the callback of a select on that same
1.158 +# table.
1.159 +#
1.160 +# 2006-08-16: This has changed. It is now permitted to update
1.161 +# the table being SELECTed from within the callback of the query.
1.162 +#
1.163 +ifcapable tclvar {
1.164 + do_test misc2-7.1 {
1.165 + db close
1.166 + file delete -force test.db
1.167 + sqlite3 db test.db
1.168 + execsql {
1.169 + CREATE TABLE t1(x);
1.170 + INSERT INTO t1 VALUES(1);
1.171 + INSERT INTO t1 VALUES(2);
1.172 + INSERT INTO t1 VALUES(3);
1.173 + SELECT * FROM t1;
1.174 + }
1.175 + } {1 2 3}
1.176 + do_test misc2-7.2 {
1.177 + set rc [catch {
1.178 + db eval {SELECT rowid FROM t1} {} {
1.179 + db eval "DELETE FROM t1 WHERE rowid=$rowid"
1.180 + }
1.181 + } msg]
1.182 + lappend rc $msg
1.183 + } {0 {}}
1.184 + do_test misc2-7.3 {
1.185 + execsql {SELECT * FROM t1}
1.186 + } {}
1.187 + do_test misc2-7.4 {
1.188 + execsql {
1.189 + DELETE FROM t1;
1.190 + INSERT INTO t1 VALUES(1);
1.191 + INSERT INTO t1 VALUES(2);
1.192 + INSERT INTO t1 VALUES(3);
1.193 + INSERT INTO t1 VALUES(4);
1.194 + }
1.195 + db eval {SELECT rowid, x FROM t1} {
1.196 + if {$x & 1} {
1.197 + db eval {DELETE FROM t1 WHERE rowid=$rowid}
1.198 + }
1.199 + }
1.200 + execsql {SELECT * FROM t1}
1.201 + } {2 4}
1.202 + do_test misc2-7.5 {
1.203 + execsql {
1.204 + DELETE FROM t1;
1.205 + INSERT INTO t1 VALUES(1);
1.206 + INSERT INTO t1 VALUES(2);
1.207 + INSERT INTO t1 VALUES(3);
1.208 + INSERT INTO t1 VALUES(4);
1.209 + }
1.210 + db eval {SELECT rowid, x FROM t1} {
1.211 + if {$x & 1} {
1.212 + db eval {DELETE FROM t1 WHERE rowid=$rowid+1}
1.213 + }
1.214 + }
1.215 + execsql {SELECT * FROM t1}
1.216 + } {1 3}
1.217 + do_test misc2-7.6 {
1.218 + execsql {
1.219 + DELETE FROM t1;
1.220 + INSERT INTO t1 VALUES(1);
1.221 + INSERT INTO t1 VALUES(2);
1.222 + INSERT INTO t1 VALUES(3);
1.223 + INSERT INTO t1 VALUES(4);
1.224 + }
1.225 + db eval {SELECT rowid, x FROM t1} {
1.226 + if {$x & 1} {
1.227 + db eval {DELETE FROM t1}
1.228 + }
1.229 + }
1.230 + execsql {SELECT * FROM t1}
1.231 + } {}
1.232 + do_test misc2-7.7 {
1.233 + execsql {
1.234 + DELETE FROM t1;
1.235 + INSERT INTO t1 VALUES(1);
1.236 + INSERT INTO t1 VALUES(2);
1.237 + INSERT INTO t1 VALUES(3);
1.238 + INSERT INTO t1 VALUES(4);
1.239 + }
1.240 + db eval {SELECT rowid, x FROM t1} {
1.241 + if {$x & 1} {
1.242 + db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid}
1.243 + }
1.244 + }
1.245 + execsql {SELECT * FROM t1}
1.246 + } {101 2 103 4}
1.247 + do_test misc2-7.8 {
1.248 + execsql {
1.249 + DELETE FROM t1;
1.250 + INSERT INTO t1 VALUES(1);
1.251 + }
1.252 + db eval {SELECT rowid, x FROM t1} {
1.253 + if {$x<10} {
1.254 + db eval {INSERT INTO t1 VALUES($x+1)}
1.255 + }
1.256 + }
1.257 + execsql {SELECT * FROM t1}
1.258 + } {1 2 3 4 5 6 7 8 9 10}
1.259 +
1.260 + # Repeat the tests 7.1 through 7.8 about but this time do the SELECTs
1.261 + # in reverse order so that we exercise the sqlite3BtreePrev() routine
1.262 + # instead of sqlite3BtreeNext()
1.263 + #
1.264 + do_test misc2-7.11 {
1.265 + db close
1.266 + file delete -force test.db
1.267 + sqlite3 db test.db
1.268 + execsql {
1.269 + CREATE TABLE t1(x);
1.270 + INSERT INTO t1 VALUES(1);
1.271 + INSERT INTO t1 VALUES(2);
1.272 + INSERT INTO t1 VALUES(3);
1.273 + SELECT * FROM t1;
1.274 + }
1.275 + } {1 2 3}
1.276 + do_test misc2-7.12 {
1.277 + set rc [catch {
1.278 + db eval {SELECT rowid FROM t1 ORDER BY rowid DESC} {} {
1.279 + db eval "DELETE FROM t1 WHERE rowid=$rowid"
1.280 + }
1.281 + } msg]
1.282 + lappend rc $msg
1.283 + } {0 {}}
1.284 + do_test misc2-7.13 {
1.285 + execsql {SELECT * FROM t1}
1.286 + } {}
1.287 + do_test misc2-7.14 {
1.288 + execsql {
1.289 + DELETE FROM t1;
1.290 + INSERT INTO t1 VALUES(1);
1.291 + INSERT INTO t1 VALUES(2);
1.292 + INSERT INTO t1 VALUES(3);
1.293 + INSERT INTO t1 VALUES(4);
1.294 + }
1.295 + db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
1.296 + if {$x & 1} {
1.297 + db eval {DELETE FROM t1 WHERE rowid=$rowid}
1.298 + }
1.299 + }
1.300 + execsql {SELECT * FROM t1}
1.301 + } {2 4}
1.302 + do_test misc2-7.15 {
1.303 + execsql {
1.304 + DELETE FROM t1;
1.305 + INSERT INTO t1 VALUES(1);
1.306 + INSERT INTO t1 VALUES(2);
1.307 + INSERT INTO t1 VALUES(3);
1.308 + INSERT INTO t1 VALUES(4);
1.309 + }
1.310 + db eval {SELECT rowid, x FROM t1} {
1.311 + if {$x & 1} {
1.312 + db eval {DELETE FROM t1 WHERE rowid=$rowid+1}
1.313 + }
1.314 + }
1.315 + execsql {SELECT * FROM t1}
1.316 + } {1 3}
1.317 + do_test misc2-7.16 {
1.318 + execsql {
1.319 + DELETE FROM t1;
1.320 + INSERT INTO t1 VALUES(1);
1.321 + INSERT INTO t1 VALUES(2);
1.322 + INSERT INTO t1 VALUES(3);
1.323 + INSERT INTO t1 VALUES(4);
1.324 + }
1.325 + db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
1.326 + if {$x & 1} {
1.327 + db eval {DELETE FROM t1}
1.328 + }
1.329 + }
1.330 + execsql {SELECT * FROM t1}
1.331 + } {}
1.332 + do_test misc2-7.17 {
1.333 + execsql {
1.334 + DELETE FROM t1;
1.335 + INSERT INTO t1 VALUES(1);
1.336 + INSERT INTO t1 VALUES(2);
1.337 + INSERT INTO t1 VALUES(3);
1.338 + INSERT INTO t1 VALUES(4);
1.339 + }
1.340 + db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
1.341 + if {$x & 1} {
1.342 + db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid}
1.343 + }
1.344 + }
1.345 + execsql {SELECT * FROM t1}
1.346 + } {101 2 103 4}
1.347 + do_test misc2-7.18 {
1.348 + execsql {
1.349 + DELETE FROM t1;
1.350 + INSERT INTO t1(rowid,x) VALUES(10,10);
1.351 + }
1.352 + db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
1.353 + if {$x>1} {
1.354 + db eval {INSERT INTO t1(rowid,x) VALUES($x-1,$x-1)}
1.355 + }
1.356 + }
1.357 + execsql {SELECT * FROM t1}
1.358 + } {1 2 3 4 5 6 7 8 9 10}
1.359 +}
1.360 +
1.361 +db close
1.362 +file delete -force test.db
1.363 +sqlite3 db test.db
1.364 +
1.365 +# Ticket #453. If the SQL ended with "-", the tokenizer was calling that
1.366 +# an incomplete token, which caused problem. The solution was to just call
1.367 +# it a minus sign.
1.368 +#
1.369 +do_test misc2-8.1 {
1.370 + catchsql {-}
1.371 +} {1 {near "-": syntax error}}
1.372 +
1.373 +# Ticket #513. Make sure the VDBE stack does not grow on a 3-way join.
1.374 +#
1.375 +ifcapable tempdb {
1.376 + do_test misc2-9.1 {
1.377 + execsql {
1.378 + BEGIN;
1.379 + CREATE TABLE counts(n INTEGER PRIMARY KEY);
1.380 + INSERT INTO counts VALUES(0);
1.381 + INSERT INTO counts VALUES(1);
1.382 + INSERT INTO counts SELECT n+2 FROM counts;
1.383 + INSERT INTO counts SELECT n+4 FROM counts;
1.384 + INSERT INTO counts SELECT n+8 FROM counts;
1.385 + COMMIT;
1.386 +
1.387 + CREATE TEMP TABLE x AS
1.388 + SELECT dim1.n, dim2.n, dim3.n
1.389 + FROM counts AS dim1, counts AS dim2, counts AS dim3
1.390 + WHERE dim1.n<10 AND dim2.n<10 AND dim3.n<10;
1.391 +
1.392 + SELECT count(*) FROM x;
1.393 + }
1.394 + } {1000}
1.395 + do_test misc2-9.2 {
1.396 + execsql {
1.397 + DROP TABLE x;
1.398 + CREATE TEMP TABLE x AS
1.399 + SELECT dim1.n, dim2.n, dim3.n
1.400 + FROM counts AS dim1, counts AS dim2, counts AS dim3
1.401 + WHERE dim1.n>=6 AND dim2.n>=6 AND dim3.n>=6;
1.402 +
1.403 + SELECT count(*) FROM x;
1.404 + }
1.405 + } {1000}
1.406 + do_test misc2-9.3 {
1.407 + execsql {
1.408 + DROP TABLE x;
1.409 + CREATE TEMP TABLE x AS
1.410 + SELECT dim1.n, dim2.n, dim3.n, dim4.n
1.411 + FROM counts AS dim1, counts AS dim2, counts AS dim3, counts AS dim4
1.412 + WHERE dim1.n<5 AND dim2.n<5 AND dim3.n<5 AND dim4.n<5;
1.413 +
1.414 + SELECT count(*) FROM x;
1.415 + }
1.416 + } [expr 5*5*5*5]
1.417 +}
1.418 +
1.419 +# Ticket #1229. Sometimes when a "NEW.X" appears in a SELECT without
1.420 +# a FROM clause deep within a trigger, the code generator is unable to
1.421 +# trace the NEW.X back to an original table and thus figure out its
1.422 +# declared datatype.
1.423 +#
1.424 +# The SQL code below was causing a segfault.
1.425 +#
1.426 +ifcapable subquery&&trigger {
1.427 + do_test misc2-10.1 {
1.428 + execsql {
1.429 + CREATE TABLE t1229(x);
1.430 + CREATE TRIGGER r1229 BEFORE INSERT ON t1229 BEGIN
1.431 + INSERT INTO t1229 SELECT y FROM (SELECT new.x y);
1.432 + END;
1.433 + INSERT INTO t1229 VALUES(1);
1.434 + }
1.435 + } {}
1.436 +}
1.437 +
1.438 +finish_test