Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | In the query optimizer, when converting BETWEEN and LIKE/GLOB expressions into simpler forms for processing, be sure to transfer the LEFT JOIN markings. Fix for ticket [bc878246eafe0f52c]. Cherrypick of [caab361ebe]. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | branch-3.8.0 |
| Files: | files | file ages | folders |
| SHA1: |
cb667449d0ff64cba4c951325d381863 |
| User & Date: | drh 2013-08-29 13:21:52.036 |
Context
|
2013-08-29
| ||
| 13:23 | Fix an off-by-one error that causes a quoted empty string at the end of a CRNL-terminated line of CSV input to be misread by the shell. Cherrypick of [b5617e4fdadc4c]. (check-in: 43aa7d2353 user: drh tags: branch-3.8.0) | |
| 13:21 | In the query optimizer, when converting BETWEEN and LIKE/GLOB expressions into simpler forms for processing, be sure to transfer the LEFT JOIN markings. Fix for ticket [bc878246eafe0f52c]. Cherrypick of [caab361ebe]. (check-in: cb667449d0 user: drh tags: branch-3.8.0) | |
| 13:15 | Cherrypick of [c1152bdcbb] and fix for [9f2eb3abac]: Have the whereShortCut() planner ignore indexes with more than four columns. (check-in: c3f75941e5 user: drh tags: branch-3.8.0) | |
|
2013-08-28
| ||
| 16:27 | In the query optimizer, when converting BETWEEN and LIKE/GLOB expressions into simpler forms for processing, be sure to transfer the LEFT JOIN markings. Fix for ticket [bc878246eafe0f52c]. (check-in: caab361ebe user: drh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 |
#endif /* SQLITE_OMIT_VIRTUALTABLE */
/*
** If the pBase expression originated in the ON or USING clause of
** a join, then transfer the appropriate markings over to derived.
*/
static void transferJoinMarkings(Expr *pDerived, Expr *pBase){
pDerived->flags |= pBase->flags & EP_FromJoin;
pDerived->iRightJoinTable = pBase->iRightJoinTable;
}
#if !defined(SQLITE_OMIT_OR_OPTIMIZATION) && !defined(SQLITE_OMIT_SUBQUERY)
/*
** Analyze a term that consists of two or more OR-connected
** subterms. So in:
**
** ... WHERE (a=5) AND (b=7 OR c=9 OR d=13) AND (d=13)
| > > | 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 |
#endif /* SQLITE_OMIT_VIRTUALTABLE */
/*
** If the pBase expression originated in the ON or USING clause of
** a join, then transfer the appropriate markings over to derived.
*/
static void transferJoinMarkings(Expr *pDerived, Expr *pBase){
if( pDerived ){
pDerived->flags |= pBase->flags & EP_FromJoin;
pDerived->iRightJoinTable = pBase->iRightJoinTable;
}
}
#if !defined(SQLITE_OMIT_OR_OPTIMIZATION) && !defined(SQLITE_OMIT_SUBQUERY)
/*
** Analyze a term that consists of two or more OR-connected
** subterms. So in:
**
** ... WHERE (a=5) AND (b=7 OR c=9 OR d=13) AND (d=13)
|
| ︙ | ︙ | |||
1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 |
assert( pList->nExpr==2 );
for(i=0; i<2; i++){
Expr *pNewExpr;
int idxNew;
pNewExpr = sqlite3PExpr(pParse, ops[i],
sqlite3ExprDup(db, pExpr->pLeft, 0),
sqlite3ExprDup(db, pList->a[i].pExpr, 0), 0);
idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew==0 );
exprAnalyze(pSrc, pWC, idxNew);
pTerm = &pWC->a[idxTerm];
pWC->a[idxNew].iParent = idxTerm;
}
pTerm->nChild = 2;
| > | 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 |
assert( pList->nExpr==2 );
for(i=0; i<2; i++){
Expr *pNewExpr;
int idxNew;
pNewExpr = sqlite3PExpr(pParse, ops[i],
sqlite3ExprDup(db, pExpr->pLeft, 0),
sqlite3ExprDup(db, pList->a[i].pExpr, 0), 0);
transferJoinMarkings(pNewExpr, pExpr);
idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew==0 );
exprAnalyze(pSrc, pWC, idxNew);
pTerm = &pWC->a[idxTerm];
pWC->a[idxNew].iParent = idxTerm;
}
pTerm->nChild = 2;
|
| ︙ | ︙ | |||
1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 |
}
sCollSeqName.z = noCase ? "NOCASE" : "BINARY";
sCollSeqName.n = 6;
pNewExpr1 = sqlite3ExprDup(db, pLeft, 0);
pNewExpr1 = sqlite3PExpr(pParse, TK_GE,
sqlite3ExprAddCollateToken(pParse,pNewExpr1,&sCollSeqName),
pStr1, 0);
idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew1==0 );
exprAnalyze(pSrc, pWC, idxNew1);
pNewExpr2 = sqlite3ExprDup(db, pLeft, 0);
pNewExpr2 = sqlite3PExpr(pParse, TK_LT,
sqlite3ExprAddCollateToken(pParse,pNewExpr2,&sCollSeqName),
pStr2, 0);
idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew2==0 );
exprAnalyze(pSrc, pWC, idxNew2);
pTerm = &pWC->a[idxTerm];
if( isComplete ){
pWC->a[idxNew1].iParent = idxTerm;
pWC->a[idxNew2].iParent = idxTerm;
| > > | 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 |
}
sCollSeqName.z = noCase ? "NOCASE" : "BINARY";
sCollSeqName.n = 6;
pNewExpr1 = sqlite3ExprDup(db, pLeft, 0);
pNewExpr1 = sqlite3PExpr(pParse, TK_GE,
sqlite3ExprAddCollateToken(pParse,pNewExpr1,&sCollSeqName),
pStr1, 0);
transferJoinMarkings(pNewExpr1, pExpr);
idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew1==0 );
exprAnalyze(pSrc, pWC, idxNew1);
pNewExpr2 = sqlite3ExprDup(db, pLeft, 0);
pNewExpr2 = sqlite3PExpr(pParse, TK_LT,
sqlite3ExprAddCollateToken(pParse,pNewExpr2,&sCollSeqName),
pStr2, 0);
transferJoinMarkings(pNewExpr2, pExpr);
idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew2==0 );
exprAnalyze(pSrc, pWC, idxNew2);
pTerm = &pWC->a[idxTerm];
if( isComplete ){
pWC->a[idxNew1].iParent = idxTerm;
pWC->a[idxNew2].iParent = idxTerm;
|
| ︙ | ︙ |
Changes to test/where9.test.
| ︙ | ︙ | |||
946 947 948 949 950 951 952 953 954 |
SELECT 5 FROM x9 WHERE y IS NULL;
SELECT 6 FROM t91 LEFT JOIN t92 ON a=2 OR b=3 WHERE y IS NULL;
SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL;
SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL;
SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL;
}
} {1 2 3 4 8 9}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > | 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 |
SELECT 5 FROM x9 WHERE y IS NULL;
SELECT 6 FROM t91 LEFT JOIN t92 ON a=2 OR b=3 WHERE y IS NULL;
SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL;
SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL;
SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL;
}
} {1 2 3 4 8 9}
# Fix for ticket [bc878246eafe0f52c519e29049b2fe4a99491b27]
# Incorrect result when OR is used in a join to the right of a LEFT JOIN
#
do_test where9-10.1 {
db eval {
CREATE TABLE t101 (id INTEGER PRIMARY KEY);
INSERT INTO t101 VALUES (1);
SELECT * FROM t101 AS t0
LEFT JOIN t101 AS t1 ON t1.id BETWEEN 10 AND 20
JOIN t101 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
}
} {1 {} 1}
do_test where9-10.2 {
db eval {
CREATE TABLE t102 (id TEXT UNIQUE NOT NULL);
INSERT INTO t102 VALUES ('1');
SELECT * FROM t102 AS t0
LEFT JOIN t102 AS t1 ON t1.id GLOB 'abc%'
JOIN t102 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
}
} {1 {} 1}
finish_test
|