/ Check-in [fe152f8b]
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:Improved comments explaining the operation of the findTerm() utility routine in where.c. Increase the maximum number of levels of transitivity from 4 to 11.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | transitive-constraints
Files: files | file ages | folders
SHA1: fe152f8b048c9a18f99fa6096ff0e68dd630c318
User & Date: drh 2013-01-17 00:08:42
Context
2013-01-17
15:05
Make more aggressive use of transitivity in optimizing queries. Add a test case. check-in: d9676284 user: drh tags: transitive-constraints
00:08
Improved comments explaining the operation of the findTerm() utility routine in where.c. Increase the maximum number of levels of transitivity from 4 to 11. check-in: fe152f8b user: drh tags: transitive-constraints
2013-01-16
17:08
Enhance the query planner to exploit transitivity of join constraints in a multi-way join. check-in: 13171eb5 user: drh tags: transitive-constraints
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

626
627
628
629
630
631
632


















633
634
635
636
637
638
639
640
641
642
643
644
645
646

647
648
649
650
651
652
653
654
655
656
657
....
1183
1184
1185
1186
1187
1188
1189
1190


1191
1192
1193
1194
1195
1196
1197
1198
1199


1200
1201
1202
1203
1204
1205
1206
}

/*
** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
** where X is a reference to the iColumn of table iCur and <op> is one of
** the WO_xx operator codes specified by the op parameter.
** Return a pointer to the term.  Return 0 if not found.


















*/
static WhereTerm *findTerm(
  WhereClause *pWC,     /* The WHERE clause to be searched */
  int iCur,             /* Cursor number of LHS */
  int iColumn,          /* Column number of LHS */
  Bitmask notReady,     /* RHS must not overlap with this mask */
  u32 op,               /* Mask of WO_xx values describing operator */
  Index *pIdx           /* Must be compatible with this index, if not NULL */
){
  WhereTerm *pTerm;
  WhereTerm *pResult = 0;
  WhereClause *pWCOrig = pWC;
  int j, k;
  int aEquiv[8];

  int nEquiv = 2;
  int iEquiv = 2;
  Expr *pX;
  Parse *pParse;

  assert( iCur>=0 );
  aEquiv[0] = iCur;
  aEquiv[1] = iColumn;
  for(;;){
    for(pWC=pWCOrig; pWC; pWC=pWC->pOuter){
      for(pTerm=pWC->a, k=pWC->nTerm; k; k--, pTerm++){
................................................................................
** Check to see if pExpr is an expression of the form A==B where both
** A and B are columns with the same affinity and collating sequence.
** If A and B are equivalent, return true.
*/
static int isEquivalenceExpr(Parse *pParse, Expr *pExpr){
  const CollSeq *pCLeft, *pCRight;
  if( pExpr->op!=TK_EQ ) return 0;
  if( ExprHasProperty(pExpr, EP_FromJoin) ) return 0;


  assert( sqlite3ExprSkipCollate(pExpr->pRight)->op==TK_COLUMN );
  assert( sqlite3ExprSkipCollate(pExpr->pLeft)->op==TK_COLUMN );
  if( sqlite3ExprAffinity(pExpr->pLeft)!=sqlite3ExprAffinity(pExpr->pRight) ){
    return 0;
  }
  pCLeft = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
  if( pCLeft ){
    pCRight = sqlite3ExprCollSeq(pParse, pExpr->pRight);
    if( pCRight && pCRight!=pCLeft ) return 0;


  }
  return 1;
}

/*
** The input to this routine is an WhereTerm structure with only the
** "pExpr" field filled in.  The job of this routine is to analyze the







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









|
|
|
|
|
>
|
<
|
|







 







|
>
>








|
>
>







626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666

667
668
669
670
671
672
673
674
675
....
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
}

/*
** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
** where X is a reference to the iColumn of table iCur and <op> is one of
** the WO_xx operator codes specified by the op parameter.
** Return a pointer to the term.  Return 0 if not found.
**
** The term returned might by Y=<expr> if there is another constraint in
** the WHERE clause that specifies that X=Y.  Any such constraints will be
** identified by the WO_EQUIV bit in the pTerm->eOperator field.  The
** aEquiv[] array holds X and all its equivalents, with each SQL variable
** taking up two slots in aEquiv[].  The first slot is for the cursor number
** and the second is for the column number.  There are 22 slots in aEquiv[]
** so that means we can look for X plus up to 10 other equivalent values.
** Hence a search for X will return <expr> if X=A1 and A1=A2 and A2=A3
** and ... and A9=A10 and A10=<expr>.
**
** If there are multiple terms in the WHERE clause of the form "X <op> <expr>"
** then try for the one with no dependencies on <expr> - in other words where
** <expr> is a constant expression of some kind.  Only return entries of
** the form "X <op> Y" where Y is a column in another table if no terms of
** the form "X <op> <const-expr>" exist.  Other than this priority, if there
** are two or more terms that match, then the choice of which term to return
** is arbitrary.
*/
static WhereTerm *findTerm(
  WhereClause *pWC,     /* The WHERE clause to be searched */
  int iCur,             /* Cursor number of LHS */
  int iColumn,          /* Column number of LHS */
  Bitmask notReady,     /* RHS must not overlap with this mask */
  u32 op,               /* Mask of WO_xx values describing operator */
  Index *pIdx           /* Must be compatible with this index, if not NULL */
){
  WhereTerm *pTerm;            /* Term being examined as possible result */
  WhereTerm *pResult = 0;      /* The answer to return */
  WhereClause *pWCOrig = pWC;  /* Original pWC value */
  int j, k;                    /* Loop counters */
  Expr *pX;                /* Pointer to an expression */
  Parse *pParse;           /* Parsing context */
  int nEquiv = 2;          /* Number of entires in aEquiv[] */

  int iEquiv = 2;          /* Number of entries of aEquiv[] processed so far */
  int aEquiv[22];          /* iCur,iColumn and up to 10 other equivalents */

  assert( iCur>=0 );
  aEquiv[0] = iCur;
  aEquiv[1] = iColumn;
  for(;;){
    for(pWC=pWCOrig; pWC; pWC=pWC->pOuter){
      for(pTerm=pWC->a, k=pWC->nTerm; k; k--, pTerm++){
................................................................................
** Check to see if pExpr is an expression of the form A==B where both
** A and B are columns with the same affinity and collating sequence.
** If A and B are equivalent, return true.
*/
static int isEquivalenceExpr(Parse *pParse, Expr *pExpr){
  const CollSeq *pCLeft, *pCRight;
  if( pExpr->op!=TK_EQ ) return 0;
  if( ExprHasProperty(pExpr, EP_FromJoin) ){
    return 0;
  }
  assert( sqlite3ExprSkipCollate(pExpr->pRight)->op==TK_COLUMN );
  assert( sqlite3ExprSkipCollate(pExpr->pLeft)->op==TK_COLUMN );
  if( sqlite3ExprAffinity(pExpr->pLeft)!=sqlite3ExprAffinity(pExpr->pRight) ){
    return 0;
  }
  pCLeft = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
  if( pCLeft ){
    pCRight = sqlite3ExprCollSeq(pParse, pExpr->pRight);
    if( pCRight && pCRight!=pCLeft ){
      return 0;
    }
  }
  return 1;
}

/*
** The input to this routine is an WhereTerm structure with only the
** "pExpr" field filled in.  The job of this routine is to analyze the