AryaWu/sqlite
0
1# 2003 June 212#3# The author disclaims copyright to this source code. In place of4# 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 were14# left out of other test files.15#16# $Id: misc2.test,v 1.28 2007/09/12 17:01:45 danielk1977 Exp $17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21# The tests in this file were written before SQLite supported recursive22# trigger invocation, and some tests depend on that to pass. So disable23# recursive triggers for this file.24catchsql { pragma recursive_triggers = off } 25 26ifcapable {trigger} {27# Test for ticket #36028#29do_test misc2-1.1 {30 catchsql {31 CREATE TABLE FOO(bar integer);32 CREATE TRIGGER foo_insert BEFORE INSERT ON foo BEGIN33 SELECT CASE WHEN (NOT new.bar BETWEEN 0 AND 20)34 THEN raise(rollback, 'aiieee') END;35 END;36 INSERT INTO foo(bar) VALUES (1);37 }38} {0 {}}39do_test misc2-1.2 {40 catchsql {41 INSERT INTO foo(bar) VALUES (111);42 }43} {1 aiieee}44} ;# endif trigger45 46# Make sure ROWID works on a view and a subquery. Ticket #36447#48do_test misc2-2.1 {49 execsql {50 CREATE TABLE t1(a,b,c);51 INSERT INTO t1 VALUES(1,2,3);52 CREATE TABLE t2(a,b,c);53 INSERT INTO t2 VALUES(7,8,9);54 }55} {}56ifcapable subquery {57 ifcapable allow_rowid_in_view {58 do_catchsql_test misc2-2.2 {59 SELECT rowid, * FROM (SELECT * FROM t1, t2);60 } {0 {{} 1 2 3 7 8 9}}61 } else {62 do_catchsql_test misc2-2.2 {63 SELECT rowid, * FROM (SELECT * FROM t1, t2);64 } {1 {no such column: rowid}}65 }66 do_catchsql_test misc2-2.2b {67 SELECT 'rowid', * FROM (SELECT * FROM t1, t2);68 } {0 {rowid 1 2 3 7 8 9}}69}70 71ifcapable view {72 ifcapable allow_rowid_in_view {73 do_catchsql_test misc2-2.3 {74 CREATE VIEW v1 AS SELECT * FROM t1, t2;75 SELECT rowid, * FROM v1;76 } {0 {{} 1 2 3 7 8 9}}77 } else {78 do_catchsql_test misc2-2.3 {79 CREATE VIEW v1 AS SELECT * FROM t1, t2;80 SELECT rowid, * FROM v1;81 } {1 {no such column: rowid}}82 }83 84 85 do_catchsql_test misc2-2.3b {86 SELECT 'rowid', * FROM v1;87 } {0 {rowid 1 2 3 7 8 9}}88} ;# ifcapable view89 90# Ticket #2002 and #1952.91ifcapable subquery {92 do_test misc2-2.4 {93 execsql2 {94 SELECT * FROM (SELECT a, b AS 'a', c AS 'a', 4 AS 'a' FROM t1)95 }96 } {a 1 a:1 2 a:2 3 a:3 4}97}98 99# Check name binding precedence. Ticket #387100#101do_test misc2-3.1 {102 catchsql {103 SELECT t1.b+t2.b AS a, t1.a, t2.a FROM t1, t2 WHERE a==10104 }105} {1 {ambiguous column name: a}}106 107# Make sure 32-bit integer overflow is handled properly in queries.108# ticket #408109#110do_test misc2-4.1 {111 execsql {112 INSERT INTO t1 VALUES(4000000000,'a','b');113 SELECT a FROM t1 WHERE a>1;114 }115} {4000000000}116do_test misc2-4.2 {117 execsql {118 INSERT INTO t1 VALUES(2147483648,'b2','c2');119 INSERT INTO t1 VALUES(2147483647,'b3','c3');120 SELECT a FROM t1 WHERE a>2147483647;121 }122} {4000000000 2147483648}123do_test misc2-4.3 {124 execsql {125 SELECT a FROM t1 WHERE a<2147483648;126 }127} {1 2147483647}128do_test misc2-4.4 {129 execsql {130 SELECT a FROM t1 WHERE a<=2147483648;131 }132} {1 2147483648 2147483647}133do_test misc2-4.5 {134 execsql {135 SELECT a FROM t1 WHERE a<10000000000;136 }137} {1 4000000000 2147483648 2147483647}138do_test misc2-4.6 {139 execsql {140 SELECT a FROM t1 WHERE a<1000000000000 ORDER BY 1;141 }142} {1 2147483647 2147483648 4000000000}143 144# There were some issues with expanding a SrcList object using a call145# to sqliteSrcListAppend() if the SrcList had previously been duplicated146# using a call to sqliteSrcListDup(). Ticket #416. The following test147# makes sure the problem has been fixed.148#149ifcapable view {150do_test misc2-5.1 {151 execsql {152 CREATE TABLE x(a,b);153 CREATE VIEW y AS 154 SELECT x1.b AS p, x2.b AS q FROM x AS x1, x AS x2 WHERE x1.a=x2.a;155 CREATE VIEW z AS156 SELECT y1.p, y2.p FROM y AS y1, y AS y2 WHERE y1.q=y2.q;157 SELECT * from z;158 }159} {}160}161 162# Make sure we can open a database with an empty filename. What this163# does is store the database in a temporary file that is deleted when164# the database is closed. Ticket #432.165#166do_test misc2-6.1 {167 db close168 sqlite3 db {}169 execsql {170 CREATE TABLE t1(a,b);171 INSERT INTO t1 VALUES(1,2);172 SELECT * FROM t1;173 }174} {1 2}175 176# Make sure we get an error message (not a segfault) on an attempt to177# update a table from within the callback of a select on that same178# table.179#180# 2006-08-16: This has changed. It is now permitted to update181# the table being SELECTed from within the callback of the query.182#183ifcapable tclvar {184 do_test misc2-7.1 {185 db close186 forcedelete test.db187 sqlite3 db test.db188 execsql {189 CREATE TABLE t1(x);190 INSERT INTO t1 VALUES(1);191 INSERT INTO t1 VALUES(2);192 INSERT INTO t1 VALUES(3);193 SELECT * FROM t1;194 }195 } {1 2 3}196 do_test misc2-7.2 {197 set rc [catch {198 db eval {SELECT rowid FROM t1} {} {199 db eval "DELETE FROM t1 WHERE rowid=$rowid"200 }201 } msg]202 lappend rc $msg203 } {0 {}}204 do_test misc2-7.3 {205 execsql {SELECT * FROM t1}206 } {}207 do_test misc2-7.4 {208 execsql {209 DELETE FROM t1;210 INSERT INTO t1 VALUES(1);211 INSERT INTO t1 VALUES(2);212 INSERT INTO t1 VALUES(3);213 INSERT INTO t1 VALUES(4);214 }215 db eval {SELECT rowid, x FROM t1} {216 if {$x & 1} {217 db eval {DELETE FROM t1 WHERE rowid=$rowid}218 }219 }220 execsql {SELECT * FROM t1}221 } {2 4}222 do_test misc2-7.5 {223 execsql {224 DELETE FROM t1;225 INSERT INTO t1 VALUES(1);226 INSERT INTO t1 VALUES(2);227 INSERT INTO t1 VALUES(3);228 INSERT INTO t1 VALUES(4);229 }230 db eval {SELECT rowid, x FROM t1} {231 if {$x & 1} {232 db eval {DELETE FROM t1 WHERE rowid=$rowid+1}233 }234 }235 execsql {SELECT * FROM t1}236 } {1 3}237 do_test misc2-7.6 {238 execsql {239 DELETE FROM t1;240 INSERT INTO t1 VALUES(1);241 INSERT INTO t1 VALUES(2);242 INSERT INTO t1 VALUES(3);243 INSERT INTO t1 VALUES(4);244 }245 db eval {SELECT rowid, x FROM t1} {246 if {$x & 1} {247 db eval {DELETE FROM t1}248 }249 }250 execsql {SELECT * FROM t1}251 } {}252 do_test misc2-7.7 {253 execsql {254 DELETE FROM t1;255 INSERT INTO t1 VALUES(1);256 INSERT INTO t1 VALUES(2);257 INSERT INTO t1 VALUES(3);258 INSERT INTO t1 VALUES(4);259 }260 db eval {SELECT rowid, x FROM t1} {261 if {$x & 1} {262 db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid}263 }264 }265 execsql {SELECT * FROM t1}266 } {101 2 103 4}267 do_test misc2-7.8 {268 execsql {269 DELETE FROM t1;270 INSERT INTO t1 VALUES(1);271 }272 db eval {SELECT rowid, x FROM t1} {273 if {$x<10} {274 db eval {INSERT INTO t1 VALUES($x+1)}275 }276 }277 execsql {SELECT * FROM t1}278 } {1 2 3 4 5 6 7 8 9 10}279 280 # Repeat the tests 7.1 through 7.8 about but this time do the SELECTs281 # in reverse order so that we exercise the sqlite3BtreePrev() routine282 # instead of sqlite3BtreeNext()283 #284 do_test misc2-7.11 {285 db close286 forcedelete test.db287 sqlite3 db test.db288 execsql {289 CREATE TABLE t1(x);290 INSERT INTO t1 VALUES(1);291 INSERT INTO t1 VALUES(2);292 INSERT INTO t1 VALUES(3);293 SELECT * FROM t1;294 }295 } {1 2 3}296 do_test misc2-7.12 {297 set rc [catch {298 db eval {SELECT rowid FROM t1 ORDER BY rowid DESC} {} {299 db eval "DELETE FROM t1 WHERE rowid=$rowid"300 }301 } msg]302 lappend rc $msg303 } {0 {}}304 do_test misc2-7.13 {305 execsql {SELECT * FROM t1}306 } {}307 do_test misc2-7.14 {308 execsql {309 DELETE FROM t1;310 INSERT INTO t1 VALUES(1);311 INSERT INTO t1 VALUES(2);312 INSERT INTO t1 VALUES(3);313 INSERT INTO t1 VALUES(4);314 }315 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {316 if {$x & 1} {317 db eval {DELETE FROM t1 WHERE rowid=$rowid}318 }319 }320 execsql {SELECT * FROM t1}321 } {2 4}322 do_test misc2-7.15 {323 execsql {324 DELETE FROM t1;325 INSERT INTO t1 VALUES(1);326 INSERT INTO t1 VALUES(2);327 INSERT INTO t1 VALUES(3);328 INSERT INTO t1 VALUES(4);329 }330 db eval {SELECT rowid, x FROM t1} {331 if {$x & 1} {332 db eval {DELETE FROM t1 WHERE rowid=$rowid+1}333 }334 }335 execsql {SELECT * FROM t1}336 } {1 3}337 do_test misc2-7.16 {338 execsql {339 DELETE FROM t1;340 INSERT INTO t1 VALUES(1);341 INSERT INTO t1 VALUES(2);342 INSERT INTO t1 VALUES(3);343 INSERT INTO t1 VALUES(4);344 }345 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {346 if {$x & 1} {347 db eval {DELETE FROM t1}348 }349 }350 execsql {SELECT * FROM t1}351 } {}352 do_test misc2-7.17 {353 execsql {354 DELETE FROM t1;355 INSERT INTO t1 VALUES(1);356 INSERT INTO t1 VALUES(2);357 INSERT INTO t1 VALUES(3);358 INSERT INTO t1 VALUES(4);359 }360 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {361 if {$x & 1} {362 db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid}363 }364 }365 execsql {SELECT * FROM t1}366 } {101 2 103 4}367 do_test misc2-7.18 {368 execsql {369 DELETE FROM t1;370 INSERT INTO t1(rowid,x) VALUES(10,10);371 }372 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {373 if {$x>1} {374 db eval {INSERT INTO t1(rowid,x) VALUES($x-1,$x-1)}375 }376 }377 execsql {SELECT * FROM t1}378 } {1 2 3 4 5 6 7 8 9 10}379}380 381db close382forcedelete test.db383sqlite3 db test.db384catchsql { pragma recursive_triggers = off } 385 386# Ticket #453. If the SQL ended with "-", the tokenizer was calling that387# an incomplete token, which caused problem. The solution was to just call388# it a minus sign.389#390do_test misc2-8.1 {391 catchsql {-}392} {1 {near "-": syntax error}}393 394# Ticket #513. Make sure the VDBE stack does not grow on a 3-way join.395#396ifcapable tempdb {397 do_test misc2-9.1 {398 execsql {399 BEGIN;400 CREATE TABLE counts(n INTEGER PRIMARY KEY);401 INSERT INTO counts VALUES(0);402 INSERT INTO counts VALUES(1);403 INSERT INTO counts SELECT n+2 FROM counts;404 INSERT INTO counts SELECT n+4 FROM counts;405 INSERT INTO counts SELECT n+8 FROM counts;406 COMMIT;407 408 CREATE TEMP TABLE x AS409 SELECT dim1.n, dim2.n, dim3.n410 FROM counts AS dim1, counts AS dim2, counts AS dim3411 WHERE dim1.n<10 AND dim2.n<10 AND dim3.n<10;412 413 SELECT count(*) FROM x;414 }415 } {1000}416 do_test misc2-9.2 {417 execsql {418 DROP TABLE x;419 CREATE TEMP TABLE x AS420 SELECT dim1.n, dim2.n, dim3.n421 FROM counts AS dim1, counts AS dim2, counts AS dim3422 WHERE dim1.n>=6 AND dim2.n>=6 AND dim3.n>=6;423 424 SELECT count(*) FROM x;425 }426 } {1000}427 do_test misc2-9.3 {428 execsql {429 DROP TABLE x;430 CREATE TEMP TABLE x AS431 SELECT dim1.n, dim2.n, dim3.n, dim4.n432 FROM counts AS dim1, counts AS dim2, counts AS dim3, counts AS dim4433 WHERE dim1.n<5 AND dim2.n<5 AND dim3.n<5 AND dim4.n<5;434 435 SELECT count(*) FROM x;436 }437 } [expr 5*5*5*5]438}439 440# Ticket #1229. Sometimes when a "NEW.X" appears in a SELECT without441# a FROM clause deep within a trigger, the code generator is unable to442# trace the NEW.X back to an original table and thus figure out its443# declared datatype.444#445# The SQL code below was causing a segfault.446#447ifcapable subquery&&trigger {448 do_test misc2-10.1 {449 execsql {450 CREATE TABLE t1229(x);451 CREATE TRIGGER r1229 BEFORE INSERT ON t1229 BEGIN452 INSERT INTO t1229 SELECT y FROM (SELECT new.x y);453 END;454 INSERT INTO t1229 VALUES(1);455 }456 } {}457}458 459finish_test460