/ Check-in [4d46685f]
Login

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

Overview
Comment:Prevent an == constraint specified using the table-valued-function argument syntax from being used to optimize any scan not related to the virtual table for which it was specified as an argument.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4d46685f282409f7154be288719cbea4b743d7ea5315a55a91462003497469f7
User & Date: dan 2018-10-26 15:36:53
Context
2018-10-26
17:05
Add the sqlite3session_config() interface. For configuring global parameters belonging to the sessions module. check-in: 1e69f3ff user: dan tags: trunk
15:36
Prevent an == constraint specified using the table-valued-function argument syntax from being used to optimize any scan not related to the virtual table for which it was specified as an argument. check-in: 4d46685f user: dan tags: trunk
2018-10-25
14:15
In the WHERE-constraint propagation optimization, if there are duplicate constraint, make sure only one of them propagates. Proposed fix for ticket [cf5ed20fc8621b165]. check-in: 5d5b596f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

  1569   1569     Expr *pTerm;
  1570   1570     if( pItem->fg.isTabFunc==0 ) return;
  1571   1571     pTab = pItem->pTab;
  1572   1572     assert( pTab!=0 );
  1573   1573     pArgs = pItem->u1.pFuncArg;
  1574   1574     if( pArgs==0 ) return;
  1575   1575     for(j=k=0; j<pArgs->nExpr; j++){
         1576  +    Expr *pRhs;
  1576   1577       while( k<pTab->nCol && (pTab->aCol[k].colFlags & COLFLAG_HIDDEN)==0 ){k++;}
  1577   1578       if( k>=pTab->nCol ){
  1578   1579         sqlite3ErrorMsg(pParse, "too many arguments on %s() - max %d",
  1579   1580                         pTab->zName, j);
  1580   1581         return;
  1581   1582       }
  1582   1583       pColRef = sqlite3ExprAlloc(pParse->db, TK_COLUMN, 0, 0);
  1583   1584       if( pColRef==0 ) return;
  1584   1585       pColRef->iTable = pItem->iCursor;
  1585   1586       pColRef->iColumn = k++;
  1586   1587       pColRef->y.pTab = pTab;
  1587         -    pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef,
  1588         -                         sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0));
         1588  +    pRhs = sqlite3PExpr(pParse, TK_UPLUS, 
         1589  +        sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
         1590  +    pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef, pRhs);
  1589   1591       whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
  1590   1592     }
  1591   1593   }

Changes to test/bestindex4.test.

   112    112               set {} {}
   113    113           } {}
   114    114         }
   115    115       }
   116    116   
   117    117     }
   118    118   }
          119  +
          120  +#-------------------------------------------------------------------------
          121  +# Test that a parameter passed to a table-valued function cannot be
          122  +# used to drive an index. i.e. that in the following:
          123  +#
          124  +#   SELECT * FROM tbl, vtab(tbl.x);
          125  +#
          126  +# The implicit constraint "tbl.x = vtab.hidden" is not optimized using
          127  +# an index on tbl.x.
          128  +#
          129  +reset_db
          130  +register_tcl_module db
          131  +proc vtab_command {method args} {
          132  +  switch -- $method {
          133  +    xConnect {
          134  +      return "CREATE TABLE t1(a, b, c, d HIDDEN)"
          135  +    }
          136  +
          137  +    xBestIndex {
          138  +      set clist [lindex $args 0]
          139  +      if {[llength $clist]!=1} { error "unexpected constraint list" }
          140  +      catch { array unset C }
          141  +      array set C [lindex $clist 0]
          142  +      if {$C(usable)} {
          143  +        return [list omit 0 idxnum 555 rows 10 cost 100]
          144  +      }
          145  +      return [list cost 100000000]
          146  +    }
          147  +
          148  +  }
          149  +
          150  +  return {}
          151  +}
          152  +
          153  +do_execsql_test 2.0 {
          154  +  CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
          155  +  CREATE TABLE t1 (x INT PRIMARY KEY);
          156  +} {}
          157  +
          158  +do_execsql_test 2.1 {
          159  +  EXPLAIN QUERY PLAN SELECT * FROM t1, x1 WHERE x1.d=t1.x;
          160  +} {
          161  +  3 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 0:}
          162  +  7 0 0 {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (x=?)}
          163  +}
          164  +
          165  +do_execsql_test 2.2 {
          166  +  EXPLAIN QUERY PLAN SELECT * FROM t1, x1(t1.x)
          167  +} {
          168  +  3 0 0 {SCAN TABLE t1} 
          169  +  5 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:}
          170  +}
          171  +
   119    172   
   120    173   finish_test