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