/ Check-in [8c886c43ff]
Login

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

Overview
Comment:Further restrictions on the use of the transitive property in WHERE clauses.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | transitive-constraints
Files: files | file ages | folders
SHA1: 8c886c43ff0bd5953d289534791d2c6b9dc25445
User & Date: drh 2015-05-16 20:51:25
Context
2015-05-18
11:34
Refinements to the determination of when an A==B term is an equivalence. Add test cases. check-in: 6bfaf525ca user: drh tags: transitive-constraints
2015-05-16
20:51
Further restrictions on the use of the transitive property in WHERE clauses. check-in: 8c886c43ff 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: a46a247fbc user: drh tags: transitive-constraints
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  1177   1177           sqlite3ExprListDelete(db, pList);
  1178   1178         }
  1179   1179         pTerm->eOperator = WO_NOOP;  /* case 1 trumps case 3 */
  1180   1180       }
  1181   1181     }
  1182   1182   }
  1183   1183   #endif /* !SQLITE_OMIT_OR_OPTIMIZATION && !SQLITE_OMIT_SUBQUERY */
         1184  +
         1185  +/*
         1186  +** We already know that pExpr is a binary operator where both operands are
         1187  +** column references.  This routine checks to see if pExpr is an equivalence
         1188  +** relation:
         1189  +**   1.  The SQLITE_Transitive optimization must be enabled
         1190  +**   2.  Must be either an == or an IS operator
         1191  +**   3.  Not taken from the ON clause of a LEFT JOIN
         1192  +**   4.  The affinities of A and B must be compatible
         1193  +**   5.  Use the same collating sequence if not numeric affinity
         1194  +** If this routine returns TRUE, that means that the RHS can be substituted
         1195  +** for the LHS anyplace else in the WHERE clause where the LHS column occurs.
         1196  +** This is an optimization.  No harm comes from returning 0.  But if 1 is
         1197  +** returned when it should not be, then incorrect answers might result.
         1198  +*/
         1199  +static int isEquivalence(Parse *pParse, Expr *pExpr){
         1200  +  char aff1, aff2;
         1201  +  CollSeq *pColl1, *pColl2;
         1202  +  const char *zColl1, *zColl2;
         1203  +  if( !OptimizationEnabled(pParse->db, SQLITE_Transitive) ) return 0;
         1204  +  if( pExpr->op!=TK_EQ && pExpr->op!=TK_IS ) return 0;
         1205  +  if( ExprHasProperty(pExpr, EP_FromJoin) ) return 0;
         1206  +  aff1 = sqlite3ExprAffinity(pExpr->pLeft);
         1207  +  aff2 = sqlite3ExprAffinity(pExpr->pRight);
         1208  +  if( aff1!=aff2
         1209  +   && (!sqlite3IsNumericAffinity(aff1) || !sqlite3IsNumericAffinity(aff2))
         1210  +  ){
         1211  +    return 0;
         1212  +  }
         1213  +  pColl1 = sqlite3ExprCollSeq(pParse, pExpr->pRight);
         1214  +  zColl1 = pColl1 ? pColl1->zName : "BINARY";
         1215  +  pColl2 = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
         1216  +  zColl2 = pColl2 ? pColl2->zName : "BINARY";
         1217  +  return sqlite3StrICmp(zColl1, zColl2)==0;
         1218  +}
  1184   1219   
  1185   1220   /*
  1186   1221   ** The input to this routine is an WhereTerm structure with only the
  1187   1222   ** "pExpr" field filled in.  The job of this routine is to analyze the
  1188   1223   ** subexpression and populate all the other fields of the WhereTerm
  1189   1224   ** structure.
  1190   1225   **
................................................................................
  1276   1311           if( idxNew==0 ) return;
  1277   1312           pNew = &pWC->a[idxNew];
  1278   1313           markTermAsChild(pWC, idxNew, idxTerm);
  1279   1314           if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
  1280   1315           pTerm = &pWC->a[idxTerm];
  1281   1316           pTerm->wtFlags |= TERM_COPIED;
  1282   1317   
  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         -        */
  1291         -        if( (op==TK_EQ || op==TK_IS)
  1292         -         && !ExprHasProperty(pExpr, EP_FromJoin)
  1293         -         && OptimizationEnabled(db, SQLITE_Transitive)
  1294         -        ){
  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         -          }
         1318  +        if( isEquivalence(pParse, pDup) ){
         1319  +          pTerm->eOperator |= WO_EQUIV;
         1320  +          eExtraOp = WO_EQUIV;
  1303   1321           }
  1304   1322         }else{
  1305   1323           pDup = pExpr;
  1306   1324           pNew = pTerm;
  1307   1325         }
  1308   1326         exprCommute(pParse, pDup);
  1309   1327         pLeft = sqlite3ExprSkipCollate(pDup->pLeft);