SQLite: Check-in [4066a34da7]

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

Overview

Comment:	Do not allow a term in the WHERE clause of the query to qualify a partial index on the right table of a LEFT JOIN. Ticket [7f39060a24b47353]
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA3-256:	4066a34da7bcdcece6c438c27f3a11bc49b8c8373b7e1603f30f6225e2bc800a
User & Date:	drh 2019-11-30 19:29:19

Context

2019-12-03
02:51		Avoid computing a zero offset of a null pointer, which though this is technically harmless, is upsetting to pedantic run-time checkers. (check-in: 3ce804e99b user: drh tags: trunk)
2019-11-30
19:29		Do not allow a term in the WHERE clause of the query to qualify a partial index on the right table of a LEFT JOIN. Ticket [7f39060a24b47353] (check-in: 4066a34da7 user: drh tags: trunk)
2019-11-29
13:01		Add comments in the parse.sql file to help demonstrate how rules are encoded. (check-in: 2c4f714892 user: drh tags: trunk)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

  }
  return 0;
}

/* Check to see if a partial index with pPartIndexWhere can be used
** in the current query.  Return true if it can be and false if not.
*/
static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){





  int i;
  WhereTerm *pTerm;
  Parse *pParse = pWC->pWInfo->pParse;
  while( pWhere->op==TK_AND ){
    if( !whereUsablePartialIndex(iTab,pWC,pWhere->pLeft) ) return 0;
    pWhere = pWhere->pRight;
  }
  if( pParse->db->flags & SQLITE_EnableQPSG ) pParse = 0;
  for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    Expr *pExpr;
    pExpr = pTerm->pExpr;
    if( (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)

     && sqlite3ExprImpliesExpr(pParse, pExpr, pWhere, iTab) 
    ){
      return 1;
    }
  }
  return 0;
}
................................................................................
#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */

  /* Loop over all indices. If there was an INDEXED BY clause, then only 
  ** consider index pProbe.  */
  for(; rc==SQLITE_OK && pProbe; 
      pProbe=(pSrc->pIBIndex ? 0 : pProbe->pNext), iSortIdx++
  ){

    if( pProbe->pPartIdxWhere!=0
     && !whereUsablePartialIndex(pSrc->iCursor, pWC, pProbe->pPartIdxWhere) ){


      testcase( pNew->iTab!=pSrc->iCursor );  /* See ticket [98d973b8f5] */
      continue;  /* Partial index inappropriate for this query */
    }
    if( pProbe->bNoQuery ) continue;
    rSize = pProbe->aiRowLogEst[0];
    pNew->u.btree.nEq = 0;
    pNew->u.btree.nBtm = 0;








|
>
>
>
>
>




|







>







 







>

|
>
>

  }
  return 0;
}

/* Check to see if a partial index with pPartIndexWhere can be used
** in the current query.  Return true if it can be and false if not.
*/
static int whereUsablePartialIndex(
  int iTab,             /* The table for which we want an index */
  int isLeft,           /* True if iTab is the right table of a LEFT JOIN */
  WhereClause *pWC,     /* The WHERE clause of the query */
  Expr *pWhere          /* The WHERE clause from the partial index */
){
  int i;
  WhereTerm *pTerm;
  Parse *pParse = pWC->pWInfo->pParse;
  while( pWhere->op==TK_AND ){
    if( !whereUsablePartialIndex(iTab,isLeft,pWC,pWhere->pLeft) ) return 0;
    pWhere = pWhere->pRight;
  }
  if( pParse->db->flags & SQLITE_EnableQPSG ) pParse = 0;
  for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    Expr *pExpr;
    pExpr = pTerm->pExpr;
    if( (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
     && (isLeft==0 || ExprHasProperty(pExpr, EP_FromJoin))
     && sqlite3ExprImpliesExpr(pParse, pExpr, pWhere, iTab) 
    ){
      return 1;
    }
  }
  return 0;
}
................................................................................
#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */

  /* Loop over all indices. If there was an INDEXED BY clause, then only 
  ** consider index pProbe.  */
  for(; rc==SQLITE_OK && pProbe; 
      pProbe=(pSrc->pIBIndex ? 0 : pProbe->pNext), iSortIdx++
  ){
    int isLeft = (pSrc->fg.jointype & JT_OUTER)!=0;
    if( pProbe->pPartIdxWhere!=0
     && !whereUsablePartialIndex(pSrc->iCursor, isLeft, pWC,
                                 pProbe->pPartIdxWhere)
    ){
      testcase( pNew->iTab!=pSrc->iCursor );  /* See ticket [98d973b8f5] */
      continue;  /* Partial index inappropriate for this query */
    }
    if( pProbe->bNoQuery ) continue;
    rSize = pProbe->aiRowLogEst[0];
    pNew->u.btree.nEq = 0;
    pNew->u.btree.nBtm = 0;

Changes to test/join.test.

  INSERT INTO t0(c0) VALUES (0);
  SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
} {}
do_execsql_test join-20.2 {
  CREATE INDEX t1x ON t1(0) WHERE NULL IN (c1);
  SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
} {}























finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

  INSERT INTO t0(c0) VALUES (0);
  SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
} {}
do_execsql_test join-20.2 {
  CREATE INDEX t1x ON t1(0) WHERE NULL IN (c1);
  SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
} {}

# 2019-11-30 ticket 7f39060a24b47353
# Do not allow a WHERE clause term to qualify a partial index on the
# right table of a LEFT JOIN.
#
do_execsql_test join-21.10 {
  DROP TABLE t0;
  DROP TABLE t1;
  CREATE TABLE t0(aa);
  CREATE TABLE t1(bb);
  INSERT INTO t0(aa) VALUES (1);
  INSERT INTO t1(bb) VALUES (1);
  SELECT 11, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
  SELECT 12, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
  SELECT 13, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
  SELECT 14, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
  CREATE INDEX i0 ON t0(aa) WHERE aa ISNULL;
  SELECT 21, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
  SELECT 22, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
  SELECT 23, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
  SELECT 24, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
} {13 1 {} 14 1 {} 23 1 {} 24 1 {}}

finish_test