| # 2012 November 9 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # |
| # Test cases for query planning decisions. |
| |
| |
| # |
| # The tests in this file demonstrate the behaviour of the query planner |
| # in determining the order in which joined tables are scanned. |
| # |
| # Assume there are two tables being joined - t1 and t2. Each has a cost |
| # if it is the outer loop, and a cost if it is the inner loop. As follows: |
| # |
| # t1(outer) - cost of scanning t1 as the outer loop. |
| # t1(inner) - cost of scanning t1 as the inner loop. |
| # t2(outer) - cost of scanning t2 as the outer loop. |
| # t2(inner) - cost of scanning t2 as the inner loop. |
| # |
| # Depending on the order in which the planner nests the scans, the total |
| # cost of the join query is one of: |
| # |
| # t1(outer) * t2(inner) |
| # t2(outer) * t1(inner) |
| # |
| # The tests in this file attempt to verify that the planner nests joins in |
| # the correct order when the following are true: |
| # |
| # + (t1(outer) * t2(inner)) > (t1(inner) * t2(outer) |
| # + t1(outer) < t2(outer) |
| # |
| # In other words, when the best overall query plan has t2 as the outer loop, |
| # but when the outer loop is considered independent of the inner, t1 is the |
| # most efficient choice. |
| # |
| # In order to make them more predictable, automatic indexes are turned off for |
| # the tests in this file. |
| # |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| set testprefix whereF |
| |
| do_execsql_test 1.0 { |
| PRAGMA automatic_index = 0; |
| CREATE TABLE t1(a, b, c); |
| CREATE TABLE t2(d, e, f); |
| CREATE UNIQUE INDEX i1 ON t1(a); |
| CREATE UNIQUE INDEX i2 ON t2(d); |
| } {} |
| |
| foreach {tn sql} { |
| 1 "SELECT * FROM t1, t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10" |
| 2 "SELECT * FROM t2, t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10" |
| 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10" |
| } { |
| do_test 1.$tn { |
| db eval "EXPLAIN QUERY PLAN $sql" |
| } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} |
| } |
| |
| do_execsql_test 2.0 { |
| DROP TABLE t1; |
| DROP TABLE t2; |
| CREATE TABLE t1(a, b, c); |
| CREATE TABLE t2(d, e, f); |
| |
| CREATE UNIQUE INDEX i1 ON t1(a); |
| CREATE UNIQUE INDEX i2 ON t1(b); |
| CREATE UNIQUE INDEX i3 ON t2(d); |
| } {} |
| |
| foreach {tn sql} { |
| 1 "SELECT * FROM t1, t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" |
| 2 "SELECT * FROM t2, t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" |
| 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" |
| } { |
| do_test 2.$tn { |
| db eval "EXPLAIN QUERY PLAN $sql" |
| } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} |
| } |
| |
| do_execsql_test 3.0 { |
| DROP TABLE t1; |
| DROP TABLE t2; |
| CREATE TABLE t1(a, b, c); |
| CREATE TABLE t2(d, e, f); |
| |
| CREATE UNIQUE INDEX i1 ON t1(a, b); |
| CREATE INDEX i2 ON t2(d); |
| } {} |
| |
| foreach {tn sql} { |
| 1 {SELECT t1.a, t1.b, t2.d, t2.e FROM t1, t2 |
| WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} |
| |
| 2 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2, t1 |
| WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} |
| |
| 3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1 |
| WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} |
| } { |
| do_test 3.$tn { |
| db eval "EXPLAIN QUERY PLAN $sql" |
| } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} |
| } |
| |
| do_execsql_test 4.0 { |
| CREATE TABLE t4(a,b,c,d,e, PRIMARY KEY(a,b,c)); |
| CREATE INDEX t4adc ON t4(a,d,c); |
| CREATE UNIQUE INDEX t4aebc ON t4(a,e,b,c); |
| EXPLAIN QUERY PLAN SELECT rowid FROM t4 WHERE a=? AND b=?; |
| } {/a=. AND b=./} |
| |
| #------------------------------------------------------------------------- |
| # Test the following case: |
| # |
| # ... FROM t1, t2 WHERE ( |
| # t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1) |
| # ) |
| # |
| # where there is an index on t2(f2). The planner should use "t1" as the |
| # outer loop. The inner loop, on "t2", is an OR optimization. One pass |
| # for: |
| # |
| # t2.rowid = $1 |
| # |
| # and another for: |
| # |
| # t2.f2=$1 AND $1!=-1 |
| # |
| # the test is to ensure that on the second pass, the ($1!=-1) condition |
| # is tested before any seek operations are performed - i.e. outside of |
| # the loop through the f2=$1 range of the t2(f2) index. |
| # |
| reset_db |
| do_execsql_test 5.0 { |
| CREATE TABLE t1(f1); |
| CREATE TABLE t2(f2); |
| CREATE INDEX t2f ON t2(f2); |
| |
| INSERT INTO t1 VALUES(-1); |
| INSERT INTO t1 VALUES(-1); |
| INSERT INTO t1 VALUES(-1); |
| INSERT INTO t1 VALUES(-1); |
| |
| WITH w(i) AS ( |
| SELECT 1 UNION ALL SELECT i+1 FROM w WHERE i<1000 |
| ) |
| INSERT INTO t2 SELECT -1 FROM w; |
| } |
| |
| do_execsql_test 5.1 { |
| SELECT count(*) FROM t1, t2 WHERE t2.rowid = +t1.rowid |
| } {4} |
| do_test 5.2 { expr [db status vmstep]<200 } 1 |
| |
| do_execsql_test 5.3 { |
| SELECT count(*) FROM t1, t2 WHERE ( |
| t2.rowid = +t1.rowid OR t2.f2 = t1.f1 |
| ) |
| } {4000} |
| do_test 5.4 { expr [db status vmstep]>1000 } 1 |
| |
| do_execsql_test 5.5 { |
| SELECT count(*) FROM t1, t2 WHERE ( |
| t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1) |
| ) |
| } {4} |
| do_test 5.6 { expr [db status vmstep]<200 } 1 |
| |
| finish_test |