Annotation of embedaddon/sqlite3/test/misc3.test, revision 1.1
1.1 ! misho 1: # 2003 December 17
! 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: misc3.test,v 1.20 2009/05/06 00:49:01 drh Exp $
! 17:
! 18: set testdir [file dirname $argv0]
! 19: source $testdir/tester.tcl
! 20:
! 21: ifcapable {integrityck} {
! 22: # Ticket #529. Make sure an ABORT does not damage the in-memory cache
! 23: # that will be used by subsequent statements in the same transaction.
! 24: #
! 25: do_test misc3-1.1 {
! 26: execsql {
! 27: CREATE TABLE t1(a UNIQUE,b);
! 28: INSERT INTO t1
! 29: VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
! 30: UPDATE t1 SET b=b||b;
! 31: UPDATE t1 SET b=b||b;
! 32: UPDATE t1 SET b=b||b;
! 33: UPDATE t1 SET b=b||b;
! 34: UPDATE t1 SET b=b||b;
! 35: INSERT INTO t1 VALUES(2,'x');
! 36: UPDATE t1 SET b=substr(b,1,500);
! 37: BEGIN;
! 38: }
! 39: catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';}
! 40: execsql {
! 41: CREATE TABLE t2(x,y);
! 42: COMMIT;
! 43: PRAGMA integrity_check;
! 44: }
! 45: } ok
! 46: }
! 47: ifcapable {integrityck} {
! 48: do_test misc3-1.2 {
! 49: execsql {
! 50: DROP TABLE t1;
! 51: DROP TABLE t2;
! 52: }
! 53: ifcapable {vacuum} {execsql VACUUM}
! 54: execsql {
! 55: CREATE TABLE t1(a UNIQUE,b);
! 56: INSERT INTO t1
! 57: VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
! 58: INSERT INTO t1 SELECT a+1, b||b FROM t1;
! 59: INSERT INTO t1 SELECT a+2, b||b FROM t1;
! 60: INSERT INTO t1 SELECT a+4, b FROM t1;
! 61: INSERT INTO t1 SELECT a+8, b FROM t1;
! 62: INSERT INTO t1 SELECT a+16, b FROM t1;
! 63: INSERT INTO t1 SELECT a+32, b FROM t1;
! 64: INSERT INTO t1 SELECT a+64, b FROM t1;
! 65: BEGIN;
! 66: }
! 67: catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';}
! 68: execsql {
! 69: INSERT INTO t1 VALUES(200,'hello out there');
! 70: COMMIT;
! 71: PRAGMA integrity_check;
! 72: }
! 73: } ok
! 74: }
! 75:
! 76: # Tests of the sqliteAtoF() function in util.c
! 77: #
! 78: do_test misc3-2.1 {
! 79: execsql {SELECT 2e-25*0.5e25}
! 80: } 1.0
! 81: do_test misc3-2.2 {
! 82: execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025}
! 83: } 1.0
! 84: do_test misc3-2.3 {
! 85: execsql {SELECT 000000000002e-0000000025*0.5e25}
! 86: } 1.0
! 87: do_test misc3-2.4 {
! 88: execsql {SELECT 2e-25*0.5e250}
! 89: } 1e+225
! 90: do_test misc3-2.5 {
! 91: execsql {SELECT 2.0e-250*0.5e25}
! 92: } 1e-225
! 93: do_test misc3-2.6 {
! 94: execsql {SELECT '-2.0e-127' * '-0.5e27'}
! 95: } 1e-100
! 96: do_test misc3-2.7 {
! 97: execsql {SELECT '+2.0e-127' * '-0.5e27'}
! 98: } -1e-100
! 99: do_test misc3-2.8 {
! 100: execsql {SELECT 2.0e-27 * '+0.5e+127'}
! 101: } 1e+100
! 102: do_test misc3-2.9 {
! 103: execsql {SELECT 2.0e-27 * '+0.000005e+132'}
! 104: } 1e+100
! 105:
! 106: # Ticket #522. Make sure integer overflow is handled properly in
! 107: # indices.
! 108: #
! 109: integrity_check misc3-3.1
! 110: do_test misc3-3.2 {
! 111: execsql {
! 112: CREATE TABLE t2(a INT UNIQUE);
! 113: }
! 114: } {}
! 115: integrity_check misc3-3.2.1
! 116: do_test misc3-3.3 {
! 117: execsql {
! 118: INSERT INTO t2 VALUES(2147483648);
! 119: }
! 120: } {}
! 121: integrity_check misc3-3.3.1
! 122: do_test misc3-3.4 {
! 123: execsql {
! 124: INSERT INTO t2 VALUES(-2147483649);
! 125: }
! 126: } {}
! 127: integrity_check misc3-3.4.1
! 128: do_test misc3-3.5 {
! 129: execsql {
! 130: INSERT INTO t2 VALUES(+2147483649);
! 131: }
! 132: } {}
! 133: integrity_check misc3-3.5.1
! 134: do_test misc3-3.6 {
! 135: execsql {
! 136: INSERT INTO t2 VALUES(+2147483647);
! 137: INSERT INTO t2 VALUES(-2147483648);
! 138: INSERT INTO t2 VALUES(-2147483647);
! 139: INSERT INTO t2 VALUES(2147483646);
! 140: SELECT * FROM t2 ORDER BY a;
! 141: }
! 142: } {-2147483649 -2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}
! 143: do_test misc3-3.7 {
! 144: execsql {
! 145: SELECT * FROM t2 WHERE a>=-2147483648 ORDER BY a;
! 146: }
! 147: } {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}
! 148: do_test misc3-3.8 {
! 149: execsql {
! 150: SELECT * FROM t2 WHERE a>-2147483648 ORDER BY a;
! 151: }
! 152: } {-2147483647 2147483646 2147483647 2147483648 2147483649}
! 153: do_test misc3-3.9 {
! 154: execsql {
! 155: SELECT * FROM t2 WHERE a>-2147483649 ORDER BY a;
! 156: }
! 157: } {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}
! 158: do_test misc3-3.10 {
! 159: execsql {
! 160: SELECT * FROM t2 WHERE a>=0 AND a<2147483649 ORDER BY a DESC;
! 161: }
! 162: } {2147483648 2147483647 2147483646}
! 163: do_test misc3-3.11 {
! 164: execsql {
! 165: SELECT * FROM t2 WHERE a>=0 AND a<=2147483648 ORDER BY a DESC;
! 166: }
! 167: } {2147483648 2147483647 2147483646}
! 168: do_test misc3-3.12 {
! 169: execsql {
! 170: SELECT * FROM t2 WHERE a>=0 AND a<2147483648 ORDER BY a DESC;
! 171: }
! 172: } {2147483647 2147483646}
! 173: do_test misc3-3.13 {
! 174: execsql {
! 175: SELECT * FROM t2 WHERE a>=0 AND a<=2147483647 ORDER BY a DESC;
! 176: }
! 177: } {2147483647 2147483646}
! 178: do_test misc3-3.14 {
! 179: execsql {
! 180: SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC;
! 181: }
! 182: } {2147483646}
! 183:
! 184: # Ticket #565. A stack overflow is occurring when the subquery to the
! 185: # right of an IN operator contains many NULLs
! 186: #
! 187: do_test misc3-4.1 {
! 188: execsql {
! 189: CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
! 190: INSERT INTO t3(b) VALUES('abc');
! 191: INSERT INTO t3(b) VALUES('xyz');
! 192: INSERT INTO t3(b) VALUES(NULL);
! 193: INSERT INTO t3(b) VALUES(NULL);
! 194: INSERT INTO t3(b) SELECT b||'d' FROM t3;
! 195: INSERT INTO t3(b) SELECT b||'e' FROM t3;
! 196: INSERT INTO t3(b) SELECT b||'f' FROM t3;
! 197: INSERT INTO t3(b) SELECT b||'g' FROM t3;
! 198: INSERT INTO t3(b) SELECT b||'h' FROM t3;
! 199: SELECT count(a), count(b) FROM t3;
! 200: }
! 201: } {128 64}
! 202: ifcapable subquery {
! 203: do_test misc3-4.2 {
! 204: execsql {
! 205: SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3);
! 206: }
! 207: } {64}
! 208: do_test misc3-4.3 {
! 209: execsql {
! 210: SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);
! 211: }
! 212: } {64}
! 213: }
! 214:
! 215: # Ticket #601: Putting a left join inside "SELECT * FROM (<join-here>)"
! 216: # gives different results that if the outer "SELECT * FROM ..." is omitted.
! 217: #
! 218: ifcapable subquery {
! 219: do_test misc3-5.1 {
! 220: execsql {
! 221: CREATE TABLE x1 (b, c);
! 222: INSERT INTO x1 VALUES('dog',3);
! 223: INSERT INTO x1 VALUES('cat',1);
! 224: INSERT INTO x1 VALUES('dog',4);
! 225: CREATE TABLE x2 (c, e);
! 226: INSERT INTO x2 VALUES(1,'one');
! 227: INSERT INTO x2 VALUES(2,'two');
! 228: INSERT INTO x2 VALUES(3,'three');
! 229: INSERT INTO x2 VALUES(4,'four');
! 230: SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
! 231: (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)
! 232: USING(c);
! 233: }
! 234: } {1 one cat 2 two {} 3 three {} 4 four dog}
! 235: do_test misc3-5.2 {
! 236: execsql {
! 237: SELECT * FROM (
! 238: SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
! 239: (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)
! 240: USING(c)
! 241: );
! 242: }
! 243: } {1 one cat 2 two {} 3 three {} 4 four dog}
! 244: }
! 245:
! 246: ifcapable {explain} {
! 247: # Ticket #626: make sure EXPLAIN prevents BEGIN and COMMIT from working.
! 248: #
! 249: do_test misc3-6.1 {
! 250: execsql {EXPLAIN BEGIN}
! 251: catchsql {BEGIN}
! 252: } {0 {}}
! 253: do_test misc3-6.2 {
! 254: execsql {EXPLAIN COMMIT}
! 255: catchsql {COMMIT}
! 256: } {0 {}}
! 257: do_test misc3-6.3 {
! 258: execsql {BEGIN; EXPLAIN ROLLBACK}
! 259: catchsql {ROLLBACK}
! 260: } {0 {}}
! 261:
! 262: # Do some additional EXPLAIN operations to exercise the displayP4 logic.
! 263: do_test misc3-6.10 {
! 264: set x [execsql {
! 265: CREATE TABLE ex1(
! 266: a INTEGER DEFAULT 54321,
! 267: b TEXT DEFAULT "hello",
! 268: c REAL DEFAULT 3.1415926
! 269: );
! 270: CREATE UNIQUE INDEX ex1i1 ON ex1(a);
! 271: EXPLAIN REINDEX;
! 272: }]
! 273: ifcapable mergesort {
! 274: regexp { SorterCompare \d+ \d+ \d+ } $x
! 275: } else {
! 276: regexp { IsUnique \d+ \d+ \d+ \d+ } $x
! 277: }
! 278: } {1}
! 279: if {[regexp {16} [db one {PRAGMA encoding}]]} {
! 280: do_test misc3-6.11-utf16 {
! 281: set x [execsql {
! 282: EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC
! 283: }]
! 284: set y [regexp { 123456789012 } $x]
! 285: lappend y [regexp { 4.5678 } $x]
! 286: lappend y [regexp {,-BINARY} $x]
! 287: } {1 1 1}
! 288: } else {
! 289: do_test misc3-6.11-utf8 {
! 290: set x [execsql {
! 291: EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC
! 292: }]
! 293: set y [regexp { 123456789012 } $x]
! 294: lappend y [regexp { 4.5678 } $x]
! 295: lappend y [regexp { hello } $x]
! 296: lappend y [regexp {,-BINARY} $x]
! 297: } {1 1 1 1}
! 298: }
! 299: }
! 300:
! 301: ifcapable {trigger} {
! 302: # Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside
! 303: # of a trigger.
! 304: #
! 305: do_test misc3-7.1 {
! 306: execsql {
! 307: BEGIN;
! 308: CREATE TABLE y1(a);
! 309: CREATE TABLE y2(b);
! 310: CREATE TABLE y3(c);
! 311: CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN
! 312: INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1;
! 313: END;
! 314: INSERT INTO y1 VALUES(1);
! 315: INSERT INTO y1 VALUES(2);
! 316: INSERT INTO y1 SELECT a+2 FROM y1;
! 317: INSERT INTO y1 SELECT a+4 FROM y1;
! 318: INSERT INTO y1 SELECT a+8 FROM y1;
! 319: INSERT INTO y1 SELECT a+16 FROM y1;
! 320: INSERT INTO y2 SELECT a FROM y1;
! 321: COMMIT;
! 322: SELECT count(*) FROM y1;
! 323: }
! 324: } 32
! 325: do_test misc3-7.2 {
! 326: execsql {
! 327: DELETE FROM y1;
! 328: SELECT count(*) FROM y1;
! 329: }
! 330: } 0
! 331: do_test misc3-7.3 {
! 332: execsql {
! 333: SELECT count(*) FROM y3;
! 334: }
! 335: } 32
! 336: } ;# endif trigger
! 337:
! 338: # Ticket #668: VDBE stack overflow occurs when the left-hand side
! 339: # of an IN expression is NULL and the result is used as an integer, not
! 340: # as a jump.
! 341: #
! 342: ifcapable subquery {
! 343: do_test misc-8.1 {
! 344: execsql {
! 345: SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3
! 346: }
! 347: } {2}
! 348: do_test misc-8.2 {
! 349: execsql {
! 350: SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2
! 351: }
! 352: } {2}
! 353: }
! 354:
! 355: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to misc3.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