gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/misc2.test (about) 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_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 65 ifcapable 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. 76 ifcapable 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 # 86 do_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 # 95 do_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} 101 do_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} 108 do_test misc2-4.3 { 109 execsql { 110 SELECT a FROM t1 WHERE a<2147483648; 111 } 112 } {1 2147483647} 113 do_test misc2-4.4 { 114 execsql { 115 SELECT a FROM t1 WHERE a<=2147483648; 116 } 117 } {1 2147483648 2147483647} 118 do_test misc2-4.5 { 119 execsql { 120 SELECT a FROM t1 WHERE a<10000000000; 121 } 122 } {1 4000000000 2147483648 2147483647} 123 do_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 # 134 ifcapable view { 135 do_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 # 151 do_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 # 168 ifcapable 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 366 db close 367 forcedelete test.db 368 sqlite3 db test.db 369 catchsql { 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 # 375 do_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 # 381 ifcapable 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 # 432 ifcapable 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 444 finish_test