| OLD | NEW |
|---|
| (Empty) |
| 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 | |
| 18 set testdir [file dirname $argv0] | |
| 19 source $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. | |
| 24 catchsql { pragma recursive_triggers = off } | |
| 25 | |
| 26 ifcapable {trigger} { | |
| 27 # Test for ticket #360 | |
| 28 # | |
| 29 do_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 {}} | |
| 39 do_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 # | |
| 48 do_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 } {} | |
| 56 ifcapable 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 } | |
| 63 ifcapable 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. | |
| 73 ifcapable 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 # | |
| 83 do_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 # | |
| 92 do_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} | |
| 98 do_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} | |
| 105 do_test misc2-4.3 { | |
| 106 execsql { | |
| 107 SELECT a FROM t1 WHERE a<2147483648; | |
| 108 } | |
| 109 } {1 2147483647} | |
| 110 do_test misc2-4.4 { | |
| 111 execsql { | |
| 112 SELECT a FROM t1 WHERE a<=2147483648; | |
| 113 } | |
| 114 } {1 2147483648 2147483647} | |
| 115 do_test misc2-4.5 { | |
| 116 execsql { | |
| 117 SELECT a FROM t1 WHERE a<10000000000; | |
| 118 } | |
| 119 } {1 4000000000 2147483648 2147483647} | |
| 120 do_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 # | |
| 131 ifcapable view { | |
| 132 do_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 # | |
| 148 do_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 # | |
| 165 ifcapable tclvar { | |
| 166 do_test misc2-7.1 { | |
| 167 db close | |
| 168 forcedelete 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 forcedelete 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 | |
| 363 db close | |
| 364 forcedelete test.db | |
| 365 sqlite3 db test.db | |
| 366 catchsql { 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 # | |
| 372 do_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 # | |
| 378 ifcapable 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 # | |
| 429 ifcapable 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 | |
| 441 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
