Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch transitive-constraints Excluding Merge-Ins
This is equivalent to a diff from ee4b74250a to 204e567f68
2015-05-18
| ||
12:28 | Transitive constraints should only work if operands have compatible affinities and collating sequences. (check-in: 5df4056448 user: drh tags: trunk) | |
12:18 | Use an ALWAY() on conditionals in the transitive constraint logic that are always true as far as we know. (Closed-Leaf check-in: 204e567f68 user: drh tags: transitive-constraints) | |
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) | |
04:24 | Make a hard copy of the results of a subquery lest the result of the subquery be referenced after a change to the table that generated the subquery result. (check-in: 9c0d80907b user: drh tags: trunk) | |
2015-05-16
| ||
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) | |
18:31 | Fix a typo in a comment. No changes to code. (check-in: ee4b74250a user: drh tags: trunk) | |
03:41 | Improve the clean target in the MSVC makefile. (check-in: 2c1039d454 user: mistachkin tags: trunk) | |
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 1226 1227 1228 1229 1230 | 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 originating the ON clause of an OUTER JOIN ** 4. The affinities of A and B must be compatible ** 5a. Both operands use the same collating sequence OR ** 5b. The overall collating sequence is BINARY ** 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 termIsEquivalence(Parse *pParse, Expr *pExpr){ char aff1, aff2; CollSeq *pColl; 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; } pColl = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, pExpr->pRight); if( pColl==0 || sqlite3StrICmp(pColl->zName, "BINARY")==0 ) return 1; pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft); /* Since pLeft and pRight are both a column references, their collating ** sequence should always be defined. */ zColl1 = ALWAYS(pColl) ? pColl->zName : 0; pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight); zColl2 = ALWAYS(pColl) ? pColl->zName : 0; 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. ** |
︙ | ︙ | |||
1272 1273 1274 1275 1276 1277 1278 1279 1280 | sqlite3ExprDelete(db, pDup); return; } idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC); if( idxNew==0 ) return; pNew = &pWC->a[idxNew]; markTermAsChild(pWC, idxNew, idxTerm); pTerm = &pWC->a[idxTerm]; pTerm->wtFlags |= TERM_COPIED; | > | < | < < | 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 | sqlite3ExprDelete(db, pDup); return; } idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC); 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( termIsEquivalence(pParse, pDup) ){ pTerm->eOperator |= WO_EQUIV; eExtraOp = WO_EQUIV; } }else{ pDup = pExpr; pNew = pTerm; } exprCommute(pParse, pDup); pLeft = sqlite3ExprSkipCollate(pDup->pLeft); pNew->leftCursor = pLeft->iTable; |
︙ | ︙ |
Changes to test/transitive1.test.
︙ | ︙ | |||
292 293 294 295 296 297 298 299 300 | JOIN files ON files.idFile = episodeview.idFile JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow JOIN path ON path.idPath = tvshowlinkpath.idPath WHERE tvshowview.idShow = 1 GROUP BY episodeview.c12; } {1 /tmp/tvshows/The.Big.Bang.Theory/ {The Big Bang Theory} {Leonard Hofstadter and Sheldon Cooper are brilliant physicists, the kind of "beautiful minds" that understand how the universe works. But none of that genius helps them interact with people, especially women. All this begins to change when a free-spirited beauty named Penny moves in next door. Sheldon, Leonard's roommate, is quite content spending his nights playing Klingon Boggle with their socially dysfunctional friends, fellow CalTech scientists Howard Wolowitz and Raj Koothrappali. However, Leonard sees in Penny a whole new universe of possibilities... including love.} 2007-09-24 Comedy CBS TV-PG 3 1 0} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 | JOIN files ON files.idFile = episodeview.idFile JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow JOIN path ON path.idPath = tvshowlinkpath.idPath WHERE tvshowview.idShow = 1 GROUP BY episodeview.c12; } {1 /tmp/tvshows/The.Big.Bang.Theory/ {The Big Bang Theory} {Leonard Hofstadter and Sheldon Cooper are brilliant physicists, the kind of "beautiful minds" that understand how the universe works. But none of that genius helps them interact with people, especially women. All this begins to change when a free-spirited beauty named Penny moves in next door. Sheldon, Leonard's roommate, is quite content spending his nights playing Klingon Boggle with their socially dysfunctional friends, fellow CalTech scientists Howard Wolowitz and Raj Koothrappali. However, Leonard sees in Penny a whole new universe of possibilities... including love.} 2007-09-24 Comedy CBS TV-PG 3 1 0} ############################################################################## # 2015-05-18. Make sure transitive constraints are avoided when column # affinities and collating sequences get in the way. # db close forcedelete test.db sqlite3 db test.db do_execsql_test transitive1-500 { CREATE TABLE x(i INTEGER PRIMARY KEY, y TEXT); INSERT INTO x VALUES(10, '10'); SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND x.i=x.y; } {10 10} do_execsql_test transitive1-510 { CREATE TABLE t1(x TEXT); CREATE TABLE t2(y TEXT); INSERT INTO t1 VALUES('abc'); INSERT INTO t2 VALUES('ABC'); SELECT * FROM t1 CROSS JOIN t2 WHERE (x=y COLLATE nocase) AND y='ABC'; } {abc ABC} do_execsql_test transitive1-520 { CREATE TABLE t3(i INTEGER PRIMARY KEY, t TEXT); INSERT INTO t3 VALUES(10, '10'); SELECT * FROM t3 WHERE i=t AND t = '10 '; } {} do_execsql_test transitive1-530 { CREATE TABLE u1(x TEXT, y INTEGER, z TEXT); CREATE INDEX i1 ON u1(x); INSERT INTO u1 VALUES('00013', 13, '013'); SELECT * FROM u1 WHERE x=y AND y=z AND z='013'; } {00013 13 013} do_execsql_test transitive1-540 { CREATE TABLE b1(x, y); INSERT INTO b1 VALUES('abc', 'ABC'); CREATE INDEX b1x ON b1(x); SELECT * FROM b1 WHERE (x=y COLLATE nocase) AND y='ABC'; } {abc ABC} do_execsql_test transitive1-550 { CREATE TABLE c1(x, y COLLATE nocase, z); INSERT INTO c1 VALUES('ABC', 'ABC', 'abc'); SELECT * FROM c1 WHERE x=y AND y=z AND z='abc'; } {ABC ABC abc} do_execsql_test transitive1-560 { CREATE INDEX c1x ON c1(x); SELECT * FROM c1 WHERE x=y AND y=z AND z='abc'; } {ABC ABC abc} do_execsql_test transitive1-560eqp { EXPLAIN QUERY PLAN SELECT * FROM c1 WHERE x=y AND y=z AND z='abc'; } {/SCAN TABLE c1/} do_execsql_test transitive1-570 { SELECT * FROM c1 WHERE x=y AND z=y AND z='abc'; } {} do_execsql_test transitive1-570eqp { EXPLAIN QUERY PLAN SELECT * FROM c1 WHERE x=y AND z=y AND z='abc'; } {/SEARCH TABLE c1 USING INDEX c1x/} finish_test |