1 files changed, 115 insertions, 0 deletions
diff --git a/test/whereF.test b/test/whereF.test
new file mode 100644
index 0000000..57bdbee
--- /dev/null
+++ b/test/whereF.test
@@ -0,0 +1,115 @@
+# 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 x
+
+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 .*SEARCH TABLE t1 .*/}
+}
+
+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 .*SEARCH TABLE t1 .*/}
+}
+
+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 .*SEARCH TABLE t1 .*/}
+}
+
+finish_test