Fix for [4065ac8595]: Do not order CROSS or LEFT joins, even if the right-hand-side is a virtual table.

FossilOrigin-Name: e2684ece455f53563ae6da7cbb5505d9a4a3076a

4 files changed, 64 insertions, 14 deletions

diff --git a/ext/rtree/rtreeC.test b/ext/rtree/rtreeC.test
index 47e858148..2e5bedec9 100644
--- a/ext/rtree/rtreeC.test
+++ b/ext/rtree/rtreeC.test
@@ -108,5 +108,55 @@ do_eqp_test 2.5 {
   0 1 0 {SCAN TABLE t} 
 }
 
+#-------------------------------------------------------------------------
+# Test that the special CROSS JOIN handling works with rtree tables.
+#
+do_execsql_test 3.1 {
+  CREATE TABLE t1(x);
+  CREATE TABLE t2(y);
+  CREATE VIRTUAL TABLE t3 USING rtree(z, x1,x2, y1,y2);
+}
+
+do_eqp_test 3.2.1 { SELECT * FROM t1 CROSS JOIN t2 } {
+  0 0 0 {SCAN TABLE t1} 
+  0 1 1 {SCAN TABLE t2}
+}
+do_eqp_test 3.2.2 { SELECT * FROM t2 CROSS JOIN t1 } {
+  0 0 0 {SCAN TABLE t2} 0 1 1 {SCAN TABLE t1}
+}
+
+do_eqp_test 3.3.1 { SELECT * FROM t1 CROSS JOIN t3 } {
+  0 0 0 {SCAN TABLE t1}
+  0 1 1 {SCAN TABLE t3 VIRTUAL TABLE INDEX 2:} 
+}
+do_eqp_test 3.3.2 { SELECT * FROM t3 CROSS JOIN t1 } {
+  0 0 0 {SCAN TABLE t3 VIRTUAL TABLE INDEX 2:} 
+  0 1 1 {SCAN TABLE t1}
+}
+
+#--------------------------------------------------------------------
+# Test that LEFT JOINs are not reordered if the right-hand-side is
+# a virtual table.
+#
+reset_db
+do_execsql_test 4.1 {
+  CREATE TABLE t1(a);
+  CREATE VIRTUAL TABLE t2 USING rtree(b, x1,x2);
+
+  INSERT INTO t1 VALUES(1);
+  INSERT INTO t1 VALUES(2);
+
+  INSERT INTO t2 VALUES(1, 0.0, 0.1);
+  INSERT INTO t2 VALUES(3, 0.0, 0.1);
+}
+
+do_execsql_test 4.2 {
+  SELECT a, b FROM t1 LEFT JOIN t2 ON (+a = +b);
+} {1 1 2 {}}
+
+do_execsql_test 4.3 {
+  SELECT b, a FROM t2 LEFT JOIN t1 ON (+a = +b);
+} {1 1 3 {}}
+
 finish_test
 
diff --git a/manifest b/manifest
index f5ac47808..3be81e9f5 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Fix\san\serror\smessage\sin\sthe\sspellfix\sextension\sso\sthat\sit\sconforms\sto\sthe\s\nstyle\sof\serror\smessages\sin\sthe\score.
-D 2013-11-12T01:11:56.532
+C Fix\sfor\s[4065ac8595]:\sDo\snot\sorder\sCROSS\sor\sLEFT\sjoins,\seven\sif\sthe\sright-hand-side\sis\sa\svirtual\stable.
+D 2013-11-12T12:17:16.498
 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f
 F Makefile.in d12e4455cf7a36e42d3949876c1c3b88ff70867a
 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23
@@ -133,7 +133,7 @@ F ext/rtree/rtree8.test db79c812f9e4a11f9b1f3f9934007884610a713a
 F ext/rtree/rtree9.test d86ebf08ff6328895613ed577dd8a2a37c472c34
 F ext/rtree/rtreeA.test ace05e729a36e342d40cf94e9efc7b4723d9dcdf
 F ext/rtree/rtreeB.test 983e567b49b5dca165940f66b87e161aa30e82b2
-F ext/rtree/rtreeC.test 0c3dcd379e012c76df5e59d53bc99a8b48f1603b
+F ext/rtree/rtreeC.test 03975565f40a0bee165f613143e4dec716dd5a59
 F ext/rtree/rtree_perf.tcl 6c18c1f23cd48e0f948930c98dfdd37dfccb5195
 F ext/rtree/rtree_util.tcl 06aab2ed5b826545bf215fff90ecb9255a8647ea
 F ext/rtree/sqlite3rtree.h c34c1e41d1ab80bb8ad09aae402c9c956871a765
@@ -293,7 +293,7 @@ F src/vtab.c 5a423b042eb1402ef77697d03d6a67378d97bc8d
 F src/wal.c 7dc3966ef98b74422267e7e6e46e07ff6c6eb1b4
 F src/wal.h df01efe09c5cb8c8e391ff1715cca294f89668a4
 F src/walker.c e9e593d5bb798c3e67fc3893dfe7055c9e7d8d74
-F src/where.c 85766647d693ed7cfba42dfce07e2cb9c3384bd9
+F src/where.c a80cab07e904b65a57849f7f2f6a1bc075fbdbec
 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2
 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2
 F test/aggnested.test 45c0201e28045ad38a530b5a144b73cd4aa2cfd6
@@ -1136,7 +1136,7 @@ F tool/vdbe-compress.tcl f12c884766bd14277f4fcedcae07078011717381
 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4
 F tool/warnings.sh d1a6de74685f360ab718efda6265994b99bbea01
 F tool/win/sqlite.vsix 030f3eeaf2cb811a3692ab9c14d021a75ce41fff
-P 239648f8ccf057eb05841ce65b108da53fdbf0a4
-R 2677f6e85637bf6e8c8ea745ac432e8d
-U drh
-Z a8af653a4c5ae1f03aaed37205420a9d
+P b896ae3d2787c370be3ff5d09da7d631a16d3a2a
+R ffe171a8a1918ba685dd4b2b6f762faa
+U dan
+Z ee7dd90da28d3c8ec4e8ef9356d19c65
diff --git a/manifest.uuid b/manifest.uuid
index 16e5f4218..a014d4dab 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-b896ae3d2787c370be3ff5d09da7d631a16d3a2a
\ No newline at end of file
+e2684ece455f53563ae6da7cbb5505d9a4a3076a
\ No newline at end of file
diff --git a/src/where.c b/src/where.c
index efbd31097..60a9471da 100644
--- a/src/where.c
+++ b/src/where.c
@@ -4702,7 +4702,8 @@ static int whereLoopAddBtree(
 ** pBuilder->pNew->iTab.  That table is guaranteed to be a virtual table.
 */
 static int whereLoopAddVirtual(
-  WhereLoopBuilder *pBuilder   /* WHERE clause information */
+  WhereLoopBuilder *pBuilder,  /* WHERE clause information */
+  Bitmask mExtra
 ){
   WhereInfo *pWInfo;           /* WHERE analysis context */
   Parse *pParse;               /* The parsing context */
@@ -4792,7 +4793,7 @@ static int whereLoopAddVirtual(
     rc = vtabBestIndex(pParse, pTab, pIdxInfo);
     if( rc ) goto whereLoopAddVtab_exit;
     pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
-    pNew->prereq = 0;
+    pNew->prereq = mExtra;
     mxTerm = -1;
     assert( pNew->nLSlot>=nConstraint );
     for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;
@@ -4919,8 +4920,7 @@ static int whereLoopAddOr(WhereLoopBuilder *pBuilder, Bitmask mExtra){
         sCur.n = 0;
 #ifndef SQLITE_OMIT_VIRTUALTABLE
         if( IsVirtual(pItem->pTab) ){
-          rc = whereLoopAddVirtual(&sSubBuild);
-          for(i=0; i<sCur.n; i++) sCur.a[i].prereq |= mExtra;
+          rc = whereLoopAddVirtual(&sSubBuild, mExtra);
         }else
 #endif
         {
@@ -4990,7 +4990,7 @@ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){
     }
     priorJoinType = pItem->jointype;
     if( IsVirtual(pItem->pTab) ){
-      rc = whereLoopAddVirtual(pBuilder);
+      rc = whereLoopAddVirtual(pBuilder, mExtra);
     }else{
       rc = whereLoopAddBtree(pBuilder, mExtra);
     }