SQLite

Check-in [2c55c3c295]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2c55c3c2950cafdc256ab540f60dc4609b9c354b
User & Date: drh 2016-03-09 15:34:51.959
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: 79338b991b user: dan tags: trunk)
15:34
Slight simplification to the query optimizer logic associated with IN (SELECT). (check-in: 2c55c3c295 user: drh tags: trunk)
15:14
Add another test case for bug [5e3c8867]. (check-in: d91e57e49f user: dan tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/expr.c.
1564
1565
1566
1567
1568
1569
1570
1571
1572


1573
1574

1575
1576
1577


1578
1579
1580
1581
1582
1583


1584
1585

1586
1587
1588



1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604

1605
1606
1607
1608
1609
1610
1611
1612


1613
1614
1615
1616
1617
1618
1619
1564
1565
1566
1567
1568
1569
1570


1571
1572


1573



1574
1575



1576
1577

1578
1579
1580
1581
1582
1583


1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601

1602
1603
1604
1605
1606
1607
1608


1609
1610
1611
1612
1613
1614
1615
1616
1617







-
-
+
+
-
-
+
-
-
-
+
+
-
-
-


-
+
+


+

-
-
+
+
+















-
+






-
-
+
+







  if( sqlite3StrICmp(z, "_ROWID_")==0 ) return 1;
  if( sqlite3StrICmp(z, "ROWID")==0 ) return 1;
  if( sqlite3StrICmp(z, "OID")==0 ) return 1;
  return 0;
}

/*
** Return true if we are able to the IN operator optimization on a
** query of the form
** pX is the RHS of an IN operator.  If pX is a SELECT statement 
** that can be simplified to a direct table access, then return
**
**       x IN (SELECT ...)
** a pointer to the SELECT statement.  If pX is not a SELECT statement,
**
** Where the SELECT... clause is as specified by the parameter to this
** routine.
** or if the SELECT statement needs to be manifested into a transient
** table, then return NULL.
**
** The Select object passed in has already been preprocessed and no
** errors have been found.
*/
#ifndef SQLITE_OMIT_SUBQUERY
static int isCandidateForInOpt(Select *p){
static Select *isCandidateForInOpt(Expr *pX){
  Select *p;
  SrcList *pSrc;
  ExprList *pEList;
  Expr *pRes;
  Table *pTab;
  Expr *pRes;                            /* Result expression */
  if( p==0 ) return 0;                   /* right-hand side of IN is SELECT */
  if( !ExprHasProperty(pX, EP_xIsSelect) ) return 0;  /* Not a subquery */
  if( ExprHasProperty(pX, EP_VarSelect)  ) return 0;  /* Correlated subq */
  p = pX->x.pSelect;
  if( p->pPrior ) return 0;              /* Not a compound SELECT */
  if( p->selFlags & (SF_Distinct|SF_Aggregate) ){
    testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct );
    testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate );
    return 0; /* No DISTINCT keyword and no aggregate functions */
  }
  assert( p->pGroupBy==0 );              /* Has no GROUP BY clause */
  if( p->pLimit ) return 0;              /* Has no LIMIT clause */
  assert( p->pOffset==0 );               /* No LIMIT means no OFFSET */
  if( p->pWhere ) return 0;              /* Has no WHERE clause */
  pSrc = p->pSrc;
  assert( pSrc!=0 );
  if( pSrc->nSrc!=1 ) return 0;          /* Single term in FROM clause */
  if( pSrc->a[0].pSelect ) return 0;     /* FROM is not a subquery or view */
  pTab = pSrc->a[0].pTab;
  if( NEVER(pTab==0) ) return 0;
  assert( pTab!=0 );
  assert( pTab->pSelect==0 );            /* FROM clause is not a view */
  if( IsVirtual(pTab) ) return 0;        /* FROM clause not a virtual table */
  pEList = p->pEList;
  if( pEList->nExpr!=1 ) return 0;       /* One column in the result set */
  pRes = pEList->a[0].pExpr;
  if( pRes->op!=TK_COLUMN ) return 0;    /* Result is a column */
  if( pRes->iTable!=pSrc->a[0].iCursor ) return 0;  /* Not a correlated subq */
  return 1;
  assert( pRes->iTable==pSrc->a[0].iCursor );  /* Not a correlated subquery */
  return p;
}
#endif /* SQLITE_OMIT_SUBQUERY */

/*
** Code an OP_Once instruction and allocate space for its flag. Return the 
** address of the new instruction.
*/
1737
1738
1739
1740
1741
1742
1743
1744
1745

1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1735
1736
1737
1738
1739
1740
1741


1742
1743
1744
1745
1746
1747
1748

1749
1750
1751
1752
1753
1754
1755







-
-
+






-







  assert( pX->op==TK_IN );
  mustBeUnique = (inFlags & IN_INDEX_LOOP)!=0;

  /* Check to see if an existing table or index can be used to
  ** satisfy the query.  This is preferable to generating a new 
  ** ephemeral table.
  */
  p = (ExprHasProperty(pX, EP_xIsSelect) ? pX->x.pSelect : 0);
  if( pParse->nErr==0 && isCandidateForInOpt(p) ){
  if( pParse->nErr==0 && (p = isCandidateForInOpt(pX))!=0 ){
    sqlite3 *db = pParse->db;              /* Database connection */
    Table *pTab;                           /* Table <table>. */
    Expr *pExpr;                           /* Expression <column> */
    i16 iCol;                              /* Index of column <column> */
    i16 iDb;                               /* Database idx for pTab */

    assert( p );                        /* Because of isCandidateForInOpt(p) */
    assert( p->pEList!=0 );             /* Because of isCandidateForInOpt(p) */
    assert( p->pEList->a[0].pExpr!=0 ); /* Because of isCandidateForInOpt(p) */
    assert( p->pSrc!=0 );               /* Because of isCandidateForInOpt(p) */
    pTab = p->pSrc->a[0].pTab;
    pExpr = p->pEList->a[0].pExpr;
    iCol = (i16)pExpr->iColumn;