/ Check-in [a46a247f]
Login

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

Overview
Comment:Fix the transitive constraint processing to only allow transitivity if the operands of the == or IS operator have compatible affinities.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | transitive-constraints
Files: files | file ages | folders
SHA1: a46a247fbcfe6e63b12cef31353835295a650c9b
User & Date: drh 2015-05-16 19:17:17
Context
2015-05-16
20:51
Further restrictions on the use of the transitive property in WHERE clauses. check-in: 8c886c43 user: drh tags: transitive-constraints
19:17
Fix the transitive constraint processing to only allow transitivity if the operands of the == or IS operator have compatible affinities. check-in: a46a247f user: drh tags: transitive-constraints
18:31
Fix a typo in a comment. No changes to code. check-in: ee4b7425 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  1272   1272             sqlite3ExprDelete(db, pDup);
  1273   1273             return;
  1274   1274           }
  1275   1275           idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC);
  1276   1276           if( idxNew==0 ) return;
  1277   1277           pNew = &pWC->a[idxNew];
  1278   1278           markTermAsChild(pWC, idxNew, idxTerm);
         1279  +        if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
  1279   1280           pTerm = &pWC->a[idxTerm];
  1280   1281           pTerm->wtFlags |= TERM_COPIED;
         1282  +
         1283  +        /* Expressions of the form "A==B" or "A IS B" might be candidates
         1284  +        ** for propagating constraints via the transitive property.  In other
         1285  +        ** words:  "A==B AND B==$xyz" implies "A==$xyz".  If this term
         1286  +        ** qualifies, mark it with WO_EQUIV.  Necessary preconditions:
         1287  +        **   1.  The term is not in the ON clause of a LEFT JOIN
         1288  +        **   2.  The affinities of A and B must be compatible
         1289  +        **   3.  The SQLITE_Transitive optimization must be enabled
         1290  +        */
  1281   1291           if( (op==TK_EQ || op==TK_IS)
  1282   1292            && !ExprHasProperty(pExpr, EP_FromJoin)
  1283   1293            && OptimizationEnabled(db, SQLITE_Transitive)
  1284   1294           ){
  1285         -          pTerm->eOperator |= WO_EQUIV;
  1286         -          eExtraOp = WO_EQUIV;
         1295  +          char aff1 = sqlite3ExprAffinity(pDup->pLeft);
         1296  +          char aff2 = sqlite3ExprAffinity(pDup->pRight);
         1297  +          if( aff1==aff2
         1298  +           || (sqlite3IsNumericAffinity(aff1) && sqlite3IsNumericAffinity(aff2))
         1299  +          ){
         1300  +            pTerm->eOperator |= WO_EQUIV;
         1301  +            eExtraOp = WO_EQUIV;
         1302  +          }
  1287   1303           }
  1288         -        if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
  1289   1304         }else{
  1290   1305           pDup = pExpr;
  1291   1306           pNew = pTerm;
  1292   1307         }
  1293   1308         exprCommute(pParse, pDup);
  1294   1309         pLeft = sqlite3ExprSkipCollate(pDup->pLeft);
  1295   1310         pNew->leftCursor = pLeft->iTable;