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