1782d68a4Sdrh# 2012 November 9 2782d68a4Sdrh# 3782d68a4Sdrh# The author disclaims copyright to this source code. In place of 4782d68a4Sdrh# a legal notice, here is a blessing: 5782d68a4Sdrh# 6782d68a4Sdrh# May you do good and not evil. 7782d68a4Sdrh# May you find forgiveness for yourself and forgive others. 8782d68a4Sdrh# May you share freely, never taking more than you give. 9782d68a4Sdrh# 10782d68a4Sdrh#*********************************************************************** 11782d68a4Sdrh# This file implements regression tests for SQLite library. The 12782d68a4Sdrh# focus of this file is testing the query planner to make sure it 13782d68a4Sdrh# is making good planning decisions. 14782d68a4Sdrh# 15782d68a4Sdrh 16782d68a4Sdrh 17782d68a4Sdrhset testdir [file dirname $argv0] 18782d68a4Sdrhsource $testdir/tester.tcl 19782d68a4Sdrhset ::testprefix whereE 20782d68a4Sdrh 21*37f3ac8fSdan# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 22*37f3ac8fSdanifcapable !altertable { 23*37f3ac8fSdan finish_test 24*37f3ac8fSdan return 25*37f3ac8fSdan} 26*37f3ac8fSdan 27782d68a4Sdrhdo_execsql_test 1.1 { 28782d68a4Sdrh CREATE TABLE t1(a,b); 29782d68a4Sdrh INSERT INTO t1 VALUES(1,10), (2,20), (3,30), (2,22), (3, 33); 30782d68a4Sdrh INSERT INTO t1 SELECT * FROM t1; 31782d68a4Sdrh INSERT INTO t1 SELECT * FROM t1; 32782d68a4Sdrh INSERT INTO t1 SELECT * FROM t1; 33782d68a4Sdrh INSERT INTO t1 SELECT * FROM t1; 34782d68a4Sdrh INSERT INTO t1 SELECT * FROM t1; 35782d68a4Sdrh INSERT INTO t1 SELECT * FROM t1; 36782d68a4Sdrh INSERT INTO t1 SELECT * FROM t1; 37782d68a4Sdrh INSERT INTO t1 SELECT * FROM t1; 38782d68a4Sdrh INSERT INTO t1 SELECT * FROM t1; 39782d68a4Sdrh INSERT INTO t1 SELECT * FROM t1; 40782d68a4Sdrh ALTER TABLE t1 ADD COLUMN c; 41782d68a4Sdrh UPDATE t1 SET c=a*rowid+10000; 42782d68a4Sdrh CREATE INDEX t1ab ON t1(a,b); 43782d68a4Sdrh 44782d68a4Sdrh CREATE TABLE t2(x,y); 45782d68a4Sdrh INSERT INTO t2 VALUES(4,44),(5,55),(6,66),(7,77); 46782d68a4Sdrh INSERT INTO t2 SELECT x+4, (x+4)*11 FROM t2; 47782d68a4Sdrh INSERT INTO t2 SELECT x+8, (x+8)*11 FROM t2; 48782d68a4Sdrh INSERT INTO t2 SELECT x+16, (x+16)*11 FROM t2; 49782d68a4Sdrh INSERT INTO t2 SELECT x+32, (x+32)*11 FROM t2; 50782d68a4Sdrh INSERT INTO t2 SELECT x+64, (x+32)*11 FROM t2; 51782d68a4Sdrh ALTER TABLE t2 ADD COLUMN z; 52782d68a4Sdrh UPDATE t2 SET z=2; 53782d68a4Sdrh CREATE UNIQUE INDEX t2zx ON t2(z,x); 54782d68a4Sdrh 55782d68a4Sdrh EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x; 568210233cSdrh} {/.*SCAN t1.*SEARCH t2.*/} 57782d68a4Sdrhdo_execsql_test 1.2 { 58782d68a4Sdrh EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x; 598210233cSdrh} {/.*SCAN t1.*SEARCH t2.*/} 60782d68a4Sdrhdo_execsql_test 1.3 { 61782d68a4Sdrh ANALYZE; 62782d68a4Sdrh EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x; 638210233cSdrh} {/.*SCAN t1.*SEARCH t2.*/} 64782d68a4Sdrhdo_execsql_test 1.4 { 65782d68a4Sdrh EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x; 668210233cSdrh} {/.*SCAN t1.*SEARCH t2.*/} 67782d68a4Sdrh 68782d68a4Sdrhfinish_test 69