/ Check-in [2c55c3c2]
Login

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

Overview
Comment:Slight simplification to the query optimizer logic associated with IN (SELECT).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2c55c3c2950cafdc256ab540f60dc4609b9c354b
User & Date: drh 2016-03-09 15:34:51
Context
2016-03-09
18:17
Fix a problem in fts3/4 that was causing it to discard data cached in-memory if an 'optimize' command is run when there is no data on disk. The usual way this would happen is if the very first transaction that writes to the fts3/4 table also includes an 'optimize' command. check-in: 79338b99 user: dan tags: trunk
15:34
Slight simplification to the query optimizer logic associated with IN (SELECT). check-in: 2c55c3c2 user: drh tags: trunk
15:14
Add another test case for bug [5e3c8867]. check-in: d91e57e4 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1564   1564     if( sqlite3StrICmp(z, "_ROWID_")==0 ) return 1;
  1565   1565     if( sqlite3StrICmp(z, "ROWID")==0 ) return 1;
  1566   1566     if( sqlite3StrICmp(z, "OID")==0 ) return 1;
  1567   1567     return 0;
  1568   1568   }
  1569   1569   
  1570   1570   /*
  1571         -** Return true if we are able to the IN operator optimization on a
  1572         -** query of the form
  1573         -**
  1574         -**       x IN (SELECT ...)
  1575         -**
  1576         -** Where the SELECT... clause is as specified by the parameter to this
  1577         -** routine.
  1578         -**
  1579         -** The Select object passed in has already been preprocessed and no
  1580         -** errors have been found.
         1571  +** pX is the RHS of an IN operator.  If pX is a SELECT statement 
         1572  +** that can be simplified to a direct table access, then return
         1573  +** a pointer to the SELECT statement.  If pX is not a SELECT statement,
         1574  +** or if the SELECT statement needs to be manifested into a transient
         1575  +** table, then return NULL.
  1581   1576   */
  1582   1577   #ifndef SQLITE_OMIT_SUBQUERY
  1583         -static int isCandidateForInOpt(Select *p){
         1578  +static Select *isCandidateForInOpt(Expr *pX){
         1579  +  Select *p;
  1584   1580     SrcList *pSrc;
  1585   1581     ExprList *pEList;
         1582  +  Expr *pRes;
  1586   1583     Table *pTab;
  1587         -  Expr *pRes;                            /* Result expression */
  1588         -  if( p==0 ) return 0;                   /* right-hand side of IN is SELECT */
         1584  +  if( !ExprHasProperty(pX, EP_xIsSelect) ) return 0;  /* Not a subquery */
         1585  +  if( ExprHasProperty(pX, EP_VarSelect)  ) return 0;  /* Correlated subq */
         1586  +  p = pX->x.pSelect;
  1589   1587     if( p->pPrior ) return 0;              /* Not a compound SELECT */
  1590   1588     if( p->selFlags & (SF_Distinct|SF_Aggregate) ){
  1591   1589       testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct );
  1592   1590       testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate );
  1593   1591       return 0; /* No DISTINCT keyword and no aggregate functions */
  1594   1592     }
  1595   1593     assert( p->pGroupBy==0 );              /* Has no GROUP BY clause */
................................................................................
  1597   1595     assert( p->pOffset==0 );               /* No LIMIT means no OFFSET */
  1598   1596     if( p->pWhere ) return 0;              /* Has no WHERE clause */
  1599   1597     pSrc = p->pSrc;
  1600   1598     assert( pSrc!=0 );
  1601   1599     if( pSrc->nSrc!=1 ) return 0;          /* Single term in FROM clause */
  1602   1600     if( pSrc->a[0].pSelect ) return 0;     /* FROM is not a subquery or view */
  1603   1601     pTab = pSrc->a[0].pTab;
  1604         -  if( NEVER(pTab==0) ) return 0;
         1602  +  assert( pTab!=0 );
  1605   1603     assert( pTab->pSelect==0 );            /* FROM clause is not a view */
  1606   1604     if( IsVirtual(pTab) ) return 0;        /* FROM clause not a virtual table */
  1607   1605     pEList = p->pEList;
  1608   1606     if( pEList->nExpr!=1 ) return 0;       /* One column in the result set */
  1609   1607     pRes = pEList->a[0].pExpr;
  1610   1608     if( pRes->op!=TK_COLUMN ) return 0;    /* Result is a column */
  1611         -  if( pRes->iTable!=pSrc->a[0].iCursor ) return 0;  /* Not a correlated subq */
  1612         -  return 1;
         1609  +  assert( pRes->iTable==pSrc->a[0].iCursor );  /* Not a correlated subquery */
         1610  +  return p;
  1613   1611   }
  1614   1612   #endif /* SQLITE_OMIT_SUBQUERY */
  1615   1613   
  1616   1614   /*
  1617   1615   ** Code an OP_Once instruction and allocate space for its flag. Return the 
  1618   1616   ** address of the new instruction.
  1619   1617   */
................................................................................
  1737   1735     assert( pX->op==TK_IN );
  1738   1736     mustBeUnique = (inFlags & IN_INDEX_LOOP)!=0;
  1739   1737   
  1740   1738     /* Check to see if an existing table or index can be used to
  1741   1739     ** satisfy the query.  This is preferable to generating a new 
  1742   1740     ** ephemeral table.
  1743   1741     */
  1744         -  p = (ExprHasProperty(pX, EP_xIsSelect) ? pX->x.pSelect : 0);
  1745         -  if( pParse->nErr==0 && isCandidateForInOpt(p) ){
         1742  +  if( pParse->nErr==0 && (p = isCandidateForInOpt(pX))!=0 ){
  1746   1743       sqlite3 *db = pParse->db;              /* Database connection */
  1747   1744       Table *pTab;                           /* Table <table>. */
  1748   1745       Expr *pExpr;                           /* Expression <column> */
  1749   1746       i16 iCol;                              /* Index of column <column> */
  1750   1747       i16 iDb;                               /* Database idx for pTab */
  1751   1748   
  1752         -    assert( p );                        /* Because of isCandidateForInOpt(p) */
  1753   1749       assert( p->pEList!=0 );             /* Because of isCandidateForInOpt(p) */
  1754   1750       assert( p->pEList->a[0].pExpr!=0 ); /* Because of isCandidateForInOpt(p) */
  1755   1751       assert( p->pSrc!=0 );               /* Because of isCandidateForInOpt(p) */
  1756   1752       pTab = p->pSrc->a[0].pTab;
  1757   1753       pExpr = p->pEList->a[0].pExpr;
  1758   1754       iCol = (i16)pExpr->iColumn;
  1759   1755