Annotation of embedaddon/sqlite3/test/misc2.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to misc2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test