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 |
| Timelines: | family | ancestors | descendants | both | transitive-constraints |
| Files: | files | file ages | folders |
| SHA1: |
8c886c43ff0bd5953d289534791d2c6b |
| User & Date: | drh 2015-05-16 20:51:25.495 |
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
Changes to src/where.c.
| ︙ | ︙ | |||
1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 |
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.
**
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 |
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.
**
|
| ︙ | ︙ | |||
1276 1277 1278 1279 1280 1281 1282 |
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;
| < < < < < < < < < < < < < < | < < | | < | 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 |
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);
|
| ︙ | ︙ |