/ Check-in [8c886c43]
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: 6bfaf525 user: drh tags: transitive-constraints
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

1177
1178
1179
1180
1181
1182
1183



































1184
1185
1186
1187
1188
1189
1190
....
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
        sqlite3ExprListDelete(db, pList);
      }
      pTerm->eOperator = WO_NOOP;  /* case 1 trumps case 3 */
    }
  }
}
#endif /* !SQLITE_OMIT_OR_OPTIMIZATION && !SQLITE_OMIT_SUBQUERY */




































/*
** 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
** subexpression and populate all the other fields of the WhereTerm
** structure.
**
................................................................................
        if( idxNew==0 ) return;
        pNew = &pWC->a[idxNew];
        markTermAsChild(pWC, idxNew, idxTerm);
        if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
        pTerm = &pWC->a[idxTerm];
        pTerm->wtFlags |= TERM_COPIED;

        /* Expressions of the form "A==B" or "A IS B" might be candidates
        ** for propagating constraints via the transitive property.  In other
        ** words:  "A==B AND B==$xyz" implies "A==$xyz".  If this term
        ** qualifies, mark it with WO_EQUIV.  Necessary preconditions:
        **   1.  The term is not in the ON clause of a LEFT JOIN
        **   2.  The affinities of A and B must be compatible
        **   3.  The SQLITE_Transitive optimization must be enabled
        */
        if( (op==TK_EQ || op==TK_IS)
         && !ExprHasProperty(pExpr, EP_FromJoin)
         && OptimizationEnabled(db, SQLITE_Transitive)
        ){
          char aff1 = sqlite3ExprAffinity(pDup->pLeft);
          char aff2 = sqlite3ExprAffinity(pDup->pRight);
          if( aff1==aff2
           || (sqlite3IsNumericAffinity(aff1) && sqlite3IsNumericAffinity(aff2))
          ){
            pTerm->eOperator |= WO_EQUIV;
            eExtraOp = WO_EQUIV;
          }
        }
      }else{
        pDup = pExpr;
        pNew = pTerm;
      }
      exprCommute(pParse, pDup);
      pLeft = sqlite3ExprSkipCollate(pDup->pLeft);







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







 







|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
|
<







1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
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
....
1311
1312
1313
1314
1315
1316
1317
1318
















1319
1320

1321
1322
1323
1324
1325
1326
1327
        sqlite3ExprListDelete(db, pList);
      }
      pTerm->eOperator = WO_NOOP;  /* case 1 trumps case 3 */
    }
  }
}
#endif /* !SQLITE_OMIT_OR_OPTIMIZATION && !SQLITE_OMIT_SUBQUERY */

/*
** We already know that pExpr is a binary operator where both operands are
** column references.  This routine checks to see if pExpr is an equivalence
** relation:
**   1.  The SQLITE_Transitive optimization must be enabled
**   2.  Must be either an == or an IS operator
**   3.  Not taken from the ON clause of a LEFT JOIN
**   4.  The affinities of A and B must be compatible
**   5.  Use the same collating sequence if not numeric affinity
** If this routine returns TRUE, that means that the RHS can be substituted
** for the LHS anyplace else in the WHERE clause where the LHS column occurs.
** This is an optimization.  No harm comes from returning 0.  But if 1 is
** returned when it should not be, then incorrect answers might result.
*/
static int isEquivalence(Parse *pParse, Expr *pExpr){
  char aff1, aff2;
  CollSeq *pColl1, *pColl2;
  const char *zColl1, *zColl2;
  if( !OptimizationEnabled(pParse->db, SQLITE_Transitive) ) return 0;
  if( pExpr->op!=TK_EQ && pExpr->op!=TK_IS ) return 0;
  if( ExprHasProperty(pExpr, EP_FromJoin) ) return 0;
  aff1 = sqlite3ExprAffinity(pExpr->pLeft);
  aff2 = sqlite3ExprAffinity(pExpr->pRight);
  if( aff1!=aff2
   && (!sqlite3IsNumericAffinity(aff1) || !sqlite3IsNumericAffinity(aff2))
  ){
    return 0;
  }
  pColl1 = sqlite3ExprCollSeq(pParse, pExpr->pRight);
  zColl1 = pColl1 ? pColl1->zName : "BINARY";
  pColl2 = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
  zColl2 = pColl2 ? pColl2->zName : "BINARY";
  return sqlite3StrICmp(zColl1, zColl2)==0;
}

/*
** 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
** subexpression and populate all the other fields of the WhereTerm
** structure.
**
................................................................................
        if( idxNew==0 ) return;
        pNew = &pWC->a[idxNew];
        markTermAsChild(pWC, idxNew, idxTerm);
        if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
        pTerm = &pWC->a[idxTerm];
        pTerm->wtFlags |= TERM_COPIED;

        if( isEquivalence(pParse, pDup) ){
















          pTerm->eOperator |= WO_EQUIV;
          eExtraOp = WO_EQUIV;

        }
      }else{
        pDup = pExpr;
        pNew = pTerm;
      }
      exprCommute(pParse, pDup);
      pLeft = sqlite3ExprSkipCollate(pDup->pLeft);