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 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# 160ifcapable tclvar { 161 do_test misc2-7.1 { 162 db close 163 file delete -force test.db 164 sqlite3 db test.db 165 execsql { 166 CREATE TABLE t1(x); 167 INSERT INTO t1 VALUES(1); 168 INSERT INTO t1 VALUES(2); 169 INSERT INTO t1 VALUES(3); 170 SELECT * FROM t1; 171 } 172 } {1 2 3} 173 do_test misc2-7.2 { 174 set rc [catch { 175 db eval {SELECT rowid FROM t1} {} { 176 db eval "DELETE FROM t1 WHERE rowid=$rowid" 177 } 178 } msg] 179 lappend rc $msg 180 } {0 {}} 181 do_test misc2-7.3 { 182 execsql {SELECT * FROM t1} 183 } {} 184 do_test misc2-7.4 { 185 execsql { 186 DELETE FROM t1; 187 INSERT INTO t1 VALUES(1); 188 INSERT INTO t1 VALUES(2); 189 INSERT INTO t1 VALUES(3); 190 INSERT INTO t1 VALUES(4); 191 } 192 db eval {SELECT rowid, x FROM t1} { 193 if {$x & 1} { 194 db eval {DELETE FROM t1 WHERE rowid=$rowid} 195 } 196 } 197 execsql {SELECT * FROM t1} 198 } {2 4} 199 do_test misc2-7.5 { 200 execsql { 201 DELETE FROM t1; 202 INSERT INTO t1 VALUES(1); 203 INSERT INTO t1 VALUES(2); 204 INSERT INTO t1 VALUES(3); 205 INSERT INTO t1 VALUES(4); 206 } 207 db eval {SELECT rowid, x FROM t1} { 208 if {$x & 1} { 209 db eval {DELETE FROM t1 WHERE rowid=$rowid+1} 210 } 211 } 212 execsql {SELECT * FROM t1} 213 } {1 3} 214 do_test misc2-7.6 { 215 execsql { 216 DELETE FROM t1; 217 INSERT INTO t1 VALUES(1); 218 INSERT INTO t1 VALUES(2); 219 INSERT INTO t1 VALUES(3); 220 INSERT INTO t1 VALUES(4); 221 } 222 db eval {SELECT rowid, x FROM t1} { 223 if {$x & 1} { 224 db eval {DELETE FROM t1} 225 } 226 } 227 execsql {SELECT * FROM t1} 228 } {} 229 do_test misc2-7.7 { 230 execsql { 231 DELETE FROM t1; 232 INSERT INTO t1 VALUES(1); 233 INSERT INTO t1 VALUES(2); 234 INSERT INTO t1 VALUES(3); 235 INSERT INTO t1 VALUES(4); 236 } 237 db eval {SELECT rowid, x FROM t1} { 238 if {$x & 1} { 239 db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid} 240 } 241 } 242 execsql {SELECT * FROM t1} 243 } {101 2 103 4} 244 do_test misc2-7.8 { 245 execsql { 246 DELETE FROM t1; 247 INSERT INTO t1 VALUES(1); 248 } 249 db eval {SELECT rowid, x FROM t1} { 250 if {$x<10} { 251 db eval {INSERT INTO t1 VALUES($x+1)} 252 } 253 } 254 execsql {SELECT * FROM t1} 255 } {1 2 3 4 5 6 7 8 9 10} 256 257 # Repeat the tests 7.1 through 7.8 about but this time do the SELECTs 258 # in reverse order so that we exercise the sqlite3BtreePrev() routine 259 # instead of sqlite3BtreeNext() 260 # 261 do_test misc2-7.11 { 262 db close 263 file delete -force test.db 264 sqlite3 db test.db 265 execsql { 266 CREATE TABLE t1(x); 267 INSERT INTO t1 VALUES(1); 268 INSERT INTO t1 VALUES(2); 269 INSERT INTO t1 VALUES(3); 270 SELECT * FROM t1; 271 } 272 } {1 2 3} 273 do_test misc2-7.12 { 274 set rc [catch { 275 db eval {SELECT rowid FROM t1 ORDER BY rowid DESC} {} { 276 db eval "DELETE FROM t1 WHERE rowid=$rowid" 277 } 278 } msg] 279 lappend rc $msg 280 } {0 {}} 281 do_test misc2-7.13 { 282 execsql {SELECT * FROM t1} 283 } {} 284 do_test misc2-7.14 { 285 execsql { 286 DELETE FROM t1; 287 INSERT INTO t1 VALUES(1); 288 INSERT INTO t1 VALUES(2); 289 INSERT INTO t1 VALUES(3); 290 INSERT INTO t1 VALUES(4); 291 } 292 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} { 293 if {$x & 1} { 294 db eval {DELETE FROM t1 WHERE rowid=$rowid} 295 } 296 } 297 execsql {SELECT * FROM t1} 298 } {2 4} 299 do_test misc2-7.15 { 300 execsql { 301 DELETE FROM t1; 302 INSERT INTO t1 VALUES(1); 303 INSERT INTO t1 VALUES(2); 304 INSERT INTO t1 VALUES(3); 305 INSERT INTO t1 VALUES(4); 306 } 307 db eval {SELECT rowid, x FROM t1} { 308 if {$x & 1} { 309 db eval {DELETE FROM t1 WHERE rowid=$rowid+1} 310 } 311 } 312 execsql {SELECT * FROM t1} 313 } {1 3} 314 do_test misc2-7.16 { 315 execsql { 316 DELETE FROM t1; 317 INSERT INTO t1 VALUES(1); 318 INSERT INTO t1 VALUES(2); 319 INSERT INTO t1 VALUES(3); 320 INSERT INTO t1 VALUES(4); 321 } 322 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} { 323 if {$x & 1} { 324 db eval {DELETE FROM t1} 325 } 326 } 327 execsql {SELECT * FROM t1} 328 } {} 329 do_test misc2-7.17 { 330 execsql { 331 DELETE FROM t1; 332 INSERT INTO t1 VALUES(1); 333 INSERT INTO t1 VALUES(2); 334 INSERT INTO t1 VALUES(3); 335 INSERT INTO t1 VALUES(4); 336 } 337 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} { 338 if {$x & 1} { 339 db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid} 340 } 341 } 342 execsql {SELECT * FROM t1} 343 } {101 2 103 4} 344 do_test misc2-7.18 { 345 execsql { 346 DELETE FROM t1; 347 INSERT INTO t1(rowid,x) VALUES(10,10); 348 } 349 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} { 350 if {$x>1} { 351 db eval {INSERT INTO t1(rowid,x) VALUES($x-1,$x-1)} 352 } 353 } 354 execsql {SELECT * FROM t1} 355 } {1 2 3 4 5 6 7 8 9 10} 356} 357 358db close 359file delete -force test.db 360sqlite3 db test.db 361 362# Ticket #453. If the SQL ended with "-", the tokenizer was calling that 363# an incomplete token, which caused problem. The solution was to just call 364# it a minus sign. 365# 366do_test misc2-8.1 { 367 catchsql {-} 368} {1 {near "-": syntax error}} 369 370# Ticket #513. Make sure the VDBE stack does not grow on a 3-way join. 371# 372ifcapable tempdb { 373 do_test misc2-9.1 { 374 execsql { 375 BEGIN; 376 CREATE TABLE counts(n INTEGER PRIMARY KEY); 377 INSERT INTO counts VALUES(0); 378 INSERT INTO counts VALUES(1); 379 INSERT INTO counts SELECT n+2 FROM counts; 380 INSERT INTO counts SELECT n+4 FROM counts; 381 INSERT INTO counts SELECT n+8 FROM counts; 382 COMMIT; 383 384 CREATE TEMP TABLE x AS 385 SELECT dim1.n, dim2.n, dim3.n 386 FROM counts AS dim1, counts AS dim2, counts AS dim3 387 WHERE dim1.n<10 AND dim2.n<10 AND dim3.n<10; 388 389 SELECT count(*) FROM x; 390 } 391 } {1000} 392 do_test misc2-9.2 { 393 execsql { 394 DROP TABLE x; 395 CREATE TEMP TABLE x AS 396 SELECT dim1.n, dim2.n, dim3.n 397 FROM counts AS dim1, counts AS dim2, counts AS dim3 398 WHERE dim1.n>=6 AND dim2.n>=6 AND dim3.n>=6; 399 400 SELECT count(*) FROM x; 401 } 402 } {1000} 403 do_test misc2-9.3 { 404 execsql { 405 DROP TABLE x; 406 CREATE TEMP TABLE x AS 407 SELECT dim1.n, dim2.n, dim3.n, dim4.n 408 FROM counts AS dim1, counts AS dim2, counts AS dim3, counts AS dim4 409 WHERE dim1.n<5 AND dim2.n<5 AND dim3.n<5 AND dim4.n<5; 410 411 SELECT count(*) FROM x; 412 } 413 } [expr 5*5*5*5] 414} 415 416# Ticket #1229. Sometimes when a "NEW.X" appears in a SELECT without 417# a FROM clause deep within a trigger, the code generator is unable to 418# trace the NEW.X back to an original table and thus figure out its 419# declared datatype. 420# 421# The SQL code below was causing a segfault. 422# 423ifcapable subquery&&trigger { 424 do_test misc2-10.1 { 425 execsql { 426 CREATE TABLE t1229(x); 427 CREATE TRIGGER r1229 BEFORE INSERT ON t1229 BEGIN 428 INSERT INTO t1229 SELECT y FROM (SELECT new.x y); 429 END; 430 INSERT INTO t1229 VALUES(1); 431 } 432 } {} 433} 434 435finish_test 436