sl@0: # 2003 December 17 sl@0: # sl@0: # Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved. sl@0: # sl@0: # The author disclaims copyright to this source code. In place of sl@0: # a legal notice, here is a blessing: sl@0: # sl@0: # May you do good and not evil. sl@0: # May you find forgiveness for yourself and forgive others. sl@0: # May you share freely, never taking more than you give. sl@0: # sl@0: #*********************************************************************** sl@0: # This file implements regression tests for SQLite library. sl@0: # sl@0: # This file implements tests for miscellanous features that were sl@0: # left out of other test files. sl@0: # sl@0: # $Id: misc3.test,v 1.19 2008/06/25 02:47:57 drh Exp $ sl@0: sl@0: set testdir [file dirname $argv0] sl@0: source $testdir/tester.tcl sl@0: sl@0: ifcapable {integrityck} { sl@0: # Ticket #529. Make sure an ABORT does not damage the in-memory cache sl@0: # that will be used by subsequent statements in the same transaction. sl@0: # sl@0: do_test misc3-1.1 { sl@0: execsql { sl@0: CREATE TABLE t1(a UNIQUE,b); sl@0: INSERT INTO t1 sl@0: VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_'); sl@0: UPDATE t1 SET b=b||b; sl@0: UPDATE t1 SET b=b||b; sl@0: UPDATE t1 SET b=b||b; sl@0: UPDATE t1 SET b=b||b; sl@0: UPDATE t1 SET b=b||b; sl@0: INSERT INTO t1 VALUES(2,'x'); sl@0: UPDATE t1 SET b=substr(b,1,500); sl@0: BEGIN; sl@0: } sl@0: catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';} sl@0: execsql { sl@0: CREATE TABLE t2(x,y); sl@0: COMMIT; sl@0: PRAGMA integrity_check; sl@0: } sl@0: } ok sl@0: } sl@0: ifcapable {integrityck} { sl@0: do_test misc3-1.2 { sl@0: execsql { sl@0: DROP TABLE t1; sl@0: DROP TABLE t2; sl@0: } sl@0: ifcapable {vacuum} {execsql VACUUM} sl@0: execsql { sl@0: CREATE TABLE t1(a UNIQUE,b); sl@0: INSERT INTO t1 sl@0: VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_'); sl@0: INSERT INTO t1 SELECT a+1, b||b FROM t1; sl@0: INSERT INTO t1 SELECT a+2, b||b FROM t1; sl@0: INSERT INTO t1 SELECT a+4, b FROM t1; sl@0: INSERT INTO t1 SELECT a+8, b FROM t1; sl@0: INSERT INTO t1 SELECT a+16, b FROM t1; sl@0: INSERT INTO t1 SELECT a+32, b FROM t1; sl@0: INSERT INTO t1 SELECT a+64, b FROM t1; sl@0: BEGIN; sl@0: } sl@0: catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';} sl@0: execsql { sl@0: INSERT INTO t1 VALUES(200,'hello out there'); sl@0: COMMIT; sl@0: PRAGMA integrity_check; sl@0: } sl@0: } ok sl@0: } sl@0: sl@0: # Tests of the sqliteAtoF() function in util.c sl@0: # sl@0: do_test misc3-2.1 { sl@0: execsql {SELECT 2e-25*0.5e25} sl@0: } 1.0 sl@0: do_test misc3-2.2 { sl@0: execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025} sl@0: } 1.0 sl@0: do_test misc3-2.3 { sl@0: execsql {SELECT 000000000002e-0000000025*0.5e25} sl@0: } 1.0 sl@0: do_test misc3-2.4 { sl@0: execsql {SELECT 2e-25*0.5e250} sl@0: } 1e+225 sl@0: do_test misc3-2.5 { sl@0: execsql {SELECT 2.0e-250*0.5e25} sl@0: } 1e-225 sl@0: # sl@0: # Symbian OS: the next test fails due to problems in sprintf/printf formatting. sl@0: if {$::tcl_platform(platform)!="symbian"} { sl@0: do_test misc3-2.6 { sl@0: execsql {SELECT '-2.0e-127' * '-0.5e27'} sl@0: } 1e-100 sl@0: do_test misc3-2.7 { sl@0: execsql {SELECT '+2.0e-127' * '-0.5e27'} sl@0: } -1e-100 sl@0: } sl@0: do_test misc3-2.8 { sl@0: execsql {SELECT 2.0e-27 * '+0.5e+127'} sl@0: } 1e+100 sl@0: do_test misc3-2.9 { sl@0: execsql {SELECT 2.0e-27 * '+0.000005e+132'} sl@0: } 1e+100 sl@0: sl@0: # Ticket #522. Make sure integer overflow is handled properly in sl@0: # indices. sl@0: # sl@0: integrity_check misc3-3.1 sl@0: do_test misc3-3.2 { sl@0: execsql { sl@0: CREATE TABLE t2(a INT UNIQUE); sl@0: } sl@0: } {} sl@0: integrity_check misc3-3.2.1 sl@0: do_test misc3-3.3 { sl@0: execsql { sl@0: INSERT INTO t2 VALUES(2147483648); sl@0: } sl@0: } {} sl@0: integrity_check misc3-3.3.1 sl@0: do_test misc3-3.4 { sl@0: execsql { sl@0: INSERT INTO t2 VALUES(-2147483649); sl@0: } sl@0: } {} sl@0: integrity_check misc3-3.4.1 sl@0: do_test misc3-3.5 { sl@0: execsql { sl@0: INSERT INTO t2 VALUES(+2147483649); sl@0: } sl@0: } {} sl@0: integrity_check misc3-3.5.1 sl@0: do_test misc3-3.6 { sl@0: execsql { sl@0: INSERT INTO t2 VALUES(+2147483647); sl@0: INSERT INTO t2 VALUES(-2147483648); sl@0: INSERT INTO t2 VALUES(-2147483647); sl@0: INSERT INTO t2 VALUES(2147483646); sl@0: SELECT * FROM t2 ORDER BY a; sl@0: } sl@0: } {-2147483649 -2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} sl@0: do_test misc3-3.7 { sl@0: execsql { sl@0: SELECT * FROM t2 WHERE a>=-2147483648 ORDER BY a; sl@0: } sl@0: } {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} sl@0: do_test misc3-3.8 { sl@0: execsql { sl@0: SELECT * FROM t2 WHERE a>-2147483648 ORDER BY a; sl@0: } sl@0: } {-2147483647 2147483646 2147483647 2147483648 2147483649} sl@0: do_test misc3-3.9 { sl@0: execsql { sl@0: SELECT * FROM t2 WHERE a>-2147483649 ORDER BY a; sl@0: } sl@0: } {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649} sl@0: do_test misc3-3.10 { sl@0: execsql { sl@0: SELECT * FROM t2 WHERE a>=0 AND a<2147483649 ORDER BY a DESC; sl@0: } sl@0: } {2147483648 2147483647 2147483646} sl@0: do_test misc3-3.11 { sl@0: execsql { sl@0: SELECT * FROM t2 WHERE a>=0 AND a<=2147483648 ORDER BY a DESC; sl@0: } sl@0: } {2147483648 2147483647 2147483646} sl@0: do_test misc3-3.12 { sl@0: execsql { sl@0: SELECT * FROM t2 WHERE a>=0 AND a<2147483648 ORDER BY a DESC; sl@0: } sl@0: } {2147483647 2147483646} sl@0: do_test misc3-3.13 { sl@0: execsql { sl@0: SELECT * FROM t2 WHERE a>=0 AND a<=2147483647 ORDER BY a DESC; sl@0: } sl@0: } {2147483647 2147483646} sl@0: do_test misc3-3.14 { sl@0: execsql { sl@0: SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC; sl@0: } sl@0: } {2147483646} sl@0: sl@0: # Ticket #565. A stack overflow is occurring when the subquery to the sl@0: # right of an IN operator contains many NULLs sl@0: # sl@0: do_test misc3-4.1 { sl@0: execsql { sl@0: CREATE TABLE t3(a INTEGER PRIMARY KEY, b); sl@0: INSERT INTO t3(b) VALUES('abc'); sl@0: INSERT INTO t3(b) VALUES('xyz'); sl@0: INSERT INTO t3(b) VALUES(NULL); sl@0: INSERT INTO t3(b) VALUES(NULL); sl@0: INSERT INTO t3(b) SELECT b||'d' FROM t3; sl@0: INSERT INTO t3(b) SELECT b||'e' FROM t3; sl@0: INSERT INTO t3(b) SELECT b||'f' FROM t3; sl@0: INSERT INTO t3(b) SELECT b||'g' FROM t3; sl@0: INSERT INTO t3(b) SELECT b||'h' FROM t3; sl@0: SELECT count(a), count(b) FROM t3; sl@0: } sl@0: } {128 64} sl@0: ifcapable subquery { sl@0: do_test misc3-4.2 { sl@0: execsql { sl@0: SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3); sl@0: } sl@0: } {64} sl@0: do_test misc3-4.3 { sl@0: execsql { sl@0: SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1); sl@0: } sl@0: } {64} sl@0: } sl@0: sl@0: # Ticket #601: Putting a left join inside "SELECT * FROM ()" sl@0: # gives different results that if the outer "SELECT * FROM ..." is omitted. sl@0: # sl@0: ifcapable subquery { sl@0: do_test misc3-5.1 { sl@0: execsql { sl@0: CREATE TABLE x1 (b, c); sl@0: INSERT INTO x1 VALUES('dog',3); sl@0: INSERT INTO x1 VALUES('cat',1); sl@0: INSERT INTO x1 VALUES('dog',4); sl@0: CREATE TABLE x2 (c, e); sl@0: INSERT INTO x2 VALUES(1,'one'); sl@0: INSERT INTO x2 VALUES(2,'two'); sl@0: INSERT INTO x2 VALUES(3,'three'); sl@0: INSERT INTO x2 VALUES(4,'four'); sl@0: SELECT x2.c AS c, e, b FROM x2 LEFT JOIN sl@0: (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b) sl@0: USING(c); sl@0: } sl@0: } {1 one cat 2 two {} 3 three {} 4 four dog} sl@0: do_test misc3-5.2 { sl@0: execsql { sl@0: SELECT * FROM ( sl@0: SELECT x2.c AS c, e, b FROM x2 LEFT JOIN sl@0: (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b) sl@0: USING(c) sl@0: ); sl@0: } sl@0: } {1 one cat 2 two {} 3 three {} 4 four dog} sl@0: } sl@0: sl@0: ifcapable {explain} { sl@0: # Ticket #626: make sure EXPLAIN prevents BEGIN and COMMIT from working. sl@0: # sl@0: do_test misc3-6.1 { sl@0: execsql {EXPLAIN BEGIN} sl@0: catchsql {BEGIN} sl@0: } {0 {}} sl@0: do_test misc3-6.2 { sl@0: execsql {EXPLAIN COMMIT} sl@0: catchsql {COMMIT} sl@0: } {0 {}} sl@0: do_test misc3-6.3 { sl@0: execsql {BEGIN; EXPLAIN ROLLBACK} sl@0: catchsql {ROLLBACK} sl@0: } {0 {}} sl@0: sl@0: # Do some additional EXPLAIN operations to exercise the displayP4 logic. sl@0: do_test misc3-6.10 { sl@0: set x [execsql { sl@0: CREATE TABLE ex1( sl@0: a INTEGER DEFAULT 54321, sl@0: b TEXT DEFAULT "hello", sl@0: c REAL DEFAULT 3.1415926 sl@0: ); sl@0: CREATE UNIQUE INDEX ex1i1 ON ex1(a); sl@0: EXPLAIN REINDEX; sl@0: }] sl@0: regexp { IsUnique \d+ \d+ \d+ \d+ } $x sl@0: } {1} sl@0: do_test misc3-6.11 { sl@0: set x [execsql { sl@0: EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC sl@0: }] sl@0: set y [regexp { 123456789012 } $x] sl@0: lappend y [regexp { 4.5678 } $x] sl@0: lappend y [regexp { hello } $x] sl@0: lappend y [regexp {,-BINARY} $x] sl@0: } {1 1 1 1} sl@0: sl@0: } sl@0: sl@0: ifcapable {trigger} { sl@0: # Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside sl@0: # of a trigger. sl@0: # sl@0: do_test misc3-7.1 { sl@0: execsql { sl@0: BEGIN; sl@0: CREATE TABLE y1(a); sl@0: CREATE TABLE y2(b); sl@0: CREATE TABLE y3(c); sl@0: CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN sl@0: INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1; sl@0: END; sl@0: INSERT INTO y1 VALUES(1); sl@0: INSERT INTO y1 VALUES(2); sl@0: INSERT INTO y1 SELECT a+2 FROM y1; sl@0: INSERT INTO y1 SELECT a+4 FROM y1; sl@0: INSERT INTO y1 SELECT a+8 FROM y1; sl@0: INSERT INTO y1 SELECT a+16 FROM y1; sl@0: INSERT INTO y2 SELECT a FROM y1; sl@0: COMMIT; sl@0: SELECT count(*) FROM y1; sl@0: } sl@0: } 32 sl@0: do_test misc3-7.2 { sl@0: execsql { sl@0: DELETE FROM y1; sl@0: SELECT count(*) FROM y1; sl@0: } sl@0: } 0 sl@0: do_test misc3-7.3 { sl@0: execsql { sl@0: SELECT count(*) FROM y3; sl@0: } sl@0: } 32 sl@0: } ;# endif trigger sl@0: sl@0: # Ticket #668: VDBE stack overflow occurs when the left-hand side sl@0: # of an IN expression is NULL and the result is used as an integer, not sl@0: # as a jump. sl@0: # sl@0: ifcapable subquery { sl@0: do_test misc-8.1 { sl@0: execsql { sl@0: SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3 sl@0: } sl@0: } {2} sl@0: do_test misc-8.2 { sl@0: execsql { sl@0: SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2 sl@0: } sl@0: } {2} sl@0: } sl@0: sl@0: finish_test