/ Check-in [e2684ece]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix for [4065ac8595]: Do not order CROSS or LEFT joins, even if the right-hand-side is a virtual table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e2684ece455f53563ae6da7cbb5505d9a4a3076a
User & Date: dan 2013-11-12 12:17:16
Context
2013-11-12
12:30
Update test command [explain_i] to handle the opcodes used by virtual tables (VNext, VFilter etc.). check-in: 1b215ee3 user: dan tags: trunk
12:17
Fix for [4065ac8595]: Do not order CROSS or LEFT joins, even if the right-hand-side is a virtual table. check-in: e2684ece user: dan tags: trunk
01:11
Fix an error message in the spellfix extension so that it conforms to the style of error messages in the core. check-in: b896ae3d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to ext/rtree/rtreeC.test.

   103    103   
   104    104   do_eqp_test 2.5 {
   105    105     SELECT * FROM t, r_tree
   106    106   } {
   107    107     0 0 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:}
   108    108     0 1 0 {SCAN TABLE t} 
   109    109   }
          110  +
          111  +#-------------------------------------------------------------------------
          112  +# Test that the special CROSS JOIN handling works with rtree tables.
          113  +#
          114  +do_execsql_test 3.1 {
          115  +  CREATE TABLE t1(x);
          116  +  CREATE TABLE t2(y);
          117  +  CREATE VIRTUAL TABLE t3 USING rtree(z, x1,x2, y1,y2);
          118  +}
          119  +
          120  +do_eqp_test 3.2.1 { SELECT * FROM t1 CROSS JOIN t2 } {
          121  +  0 0 0 {SCAN TABLE t1} 
          122  +  0 1 1 {SCAN TABLE t2}
          123  +}
          124  +do_eqp_test 3.2.2 { SELECT * FROM t2 CROSS JOIN t1 } {
          125  +  0 0 0 {SCAN TABLE t2} 0 1 1 {SCAN TABLE t1}
          126  +}
          127  +
          128  +do_eqp_test 3.3.1 { SELECT * FROM t1 CROSS JOIN t3 } {
          129  +  0 0 0 {SCAN TABLE t1}
          130  +  0 1 1 {SCAN TABLE t3 VIRTUAL TABLE INDEX 2:} 
          131  +}
          132  +do_eqp_test 3.3.2 { SELECT * FROM t3 CROSS JOIN t1 } {
          133  +  0 0 0 {SCAN TABLE t3 VIRTUAL TABLE INDEX 2:} 
          134  +  0 1 1 {SCAN TABLE t1}
          135  +}
          136  +
          137  +#--------------------------------------------------------------------
          138  +# Test that LEFT JOINs are not reordered if the right-hand-side is
          139  +# a virtual table.
          140  +#
          141  +reset_db
          142  +do_execsql_test 4.1 {
          143  +  CREATE TABLE t1(a);
          144  +  CREATE VIRTUAL TABLE t2 USING rtree(b, x1,x2);
          145  +
          146  +  INSERT INTO t1 VALUES(1);
          147  +  INSERT INTO t1 VALUES(2);
          148  +
          149  +  INSERT INTO t2 VALUES(1, 0.0, 0.1);
          150  +  INSERT INTO t2 VALUES(3, 0.0, 0.1);
          151  +}
          152  +
          153  +do_execsql_test 4.2 {
          154  +  SELECT a, b FROM t1 LEFT JOIN t2 ON (+a = +b);
          155  +} {1 1 2 {}}
          156  +
          157  +do_execsql_test 4.3 {
          158  +  SELECT b, a FROM t2 LEFT JOIN t1 ON (+a = +b);
          159  +} {1 1 3 {}}
   110    160   
   111    161   finish_test
   112    162   

Changes to src/where.c.

  4698   4698   
  4699   4699   #ifndef SQLITE_OMIT_VIRTUALTABLE
  4700   4700   /*
  4701   4701   ** Add all WhereLoop objects for a table of the join identified by
  4702   4702   ** pBuilder->pNew->iTab.  That table is guaranteed to be a virtual table.
  4703   4703   */
  4704   4704   static int whereLoopAddVirtual(
  4705         -  WhereLoopBuilder *pBuilder   /* WHERE clause information */
         4705  +  WhereLoopBuilder *pBuilder,  /* WHERE clause information */
         4706  +  Bitmask mExtra
  4706   4707   ){
  4707   4708     WhereInfo *pWInfo;           /* WHERE analysis context */
  4708   4709     Parse *pParse;               /* The parsing context */
  4709   4710     WhereClause *pWC;            /* The WHERE clause */
  4710   4711     struct SrcList_item *pSrc;   /* The FROM clause term to search */
  4711   4712     Table *pTab;
  4712   4713     sqlite3 *db;
................................................................................
  4788   4789       pIdxInfo->needToFreeIdxStr = 0;
  4789   4790       pIdxInfo->orderByConsumed = 0;
  4790   4791       pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
  4791   4792       pIdxInfo->estimatedRows = 25;
  4792   4793       rc = vtabBestIndex(pParse, pTab, pIdxInfo);
  4793   4794       if( rc ) goto whereLoopAddVtab_exit;
  4794   4795       pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
  4795         -    pNew->prereq = 0;
         4796  +    pNew->prereq = mExtra;
  4796   4797       mxTerm = -1;
  4797   4798       assert( pNew->nLSlot>=nConstraint );
  4798   4799       for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;
  4799   4800       pNew->u.vtab.omitMask = 0;
  4800   4801       for(i=0; i<nConstraint; i++, pIdxCons++){
  4801   4802         if( (iTerm = pUsage[i].argvIndex - 1)>=0 ){
  4802   4803           j = pIdxCons->iTermOffset;
................................................................................
  4915   4916             sSubBuild.pWC = &tempWC;
  4916   4917           }else{
  4917   4918             continue;
  4918   4919           }
  4919   4920           sCur.n = 0;
  4920   4921   #ifndef SQLITE_OMIT_VIRTUALTABLE
  4921   4922           if( IsVirtual(pItem->pTab) ){
  4922         -          rc = whereLoopAddVirtual(&sSubBuild);
  4923         -          for(i=0; i<sCur.n; i++) sCur.a[i].prereq |= mExtra;
         4923  +          rc = whereLoopAddVirtual(&sSubBuild, mExtra);
  4924   4924           }else
  4925   4925   #endif
  4926   4926           {
  4927   4927             rc = whereLoopAddBtree(&sSubBuild, mExtra);
  4928   4928           }
  4929   4929           assert( rc==SQLITE_OK || sCur.n==0 );
  4930   4930           if( sCur.n==0 ){
................................................................................
  4986   4986       pNew->iTab = iTab;
  4987   4987       pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor);
  4988   4988       if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){
  4989   4989         mExtra = mPrior;
  4990   4990       }
  4991   4991       priorJoinType = pItem->jointype;
  4992   4992       if( IsVirtual(pItem->pTab) ){
  4993         -      rc = whereLoopAddVirtual(pBuilder);
         4993  +      rc = whereLoopAddVirtual(pBuilder, mExtra);
  4994   4994       }else{
  4995   4995         rc = whereLoopAddBtree(pBuilder, mExtra);
  4996   4996       }
  4997   4997       if( rc==SQLITE_OK ){
  4998   4998         rc = whereLoopAddOr(pBuilder, mExtra);
  4999   4999       }
  5000   5000       mPrior |= pNew->maskSelf;