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