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