Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not attempt to read values from indexes-on-expressions if the index is on the RHS of a LEFT JOIN. This won't work if the index cursor points at a null-row. Fix for [7fa80496]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
b8ef967ab1bebf2846c06c4f7200d6fa |
| User & Date: | dan 2018-04-24 14:05:14 |
Context
|
2018-04-24
| ||
| 14:18 | Add a hyperlink to the ticket on the code comment for the fix to ticket [7fa8049685b50b5aeb0c2]. (check-in: 45247c7f user: drh tags: trunk) | |
| 14:05 | Do not attempt to read values from indexes-on-expressions if the index is on the RHS of a LEFT JOIN. This won't work if the index cursor points at a null-row. Fix for [7fa80496]. (check-in: b8ef967a user: dan tags: trunk) | |
| 13:07 | Enhance the CLI to render EXPLAIN QUERY PLAN using an ASCII-art graph. This works with ".eqp" modes and when the query begins with exactly "EXPLAIN QUERY PLAN". To see the original output format, add extra space characters in between words of the initial "EXPLAIN QUERY PLAN". (check-in: f53716ee user: drh tags: trunk) | |
Changes
Changes to src/wherecode.c.
1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 |
sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont,
iRowidReg, pPk->nKeyCol); VdbeCoverage(v);
}
/* If pIdx is an index on one or more expressions, then look through
** all the expressions in pWInfo and try to transform matching expressions
** into reference to index columns.
*/
whereIndexExprTrans(pIdx, iCur, iIdxCur, pWInfo);
/* Record the instruction used to terminate the loop. */
if( pLoop->wsFlags & WHERE_ONEROW ){
pLevel->op = OP_Noop;
}else if( bRev ){
pLevel->op = OP_Prev;
}else{
|
> > > > > > > | | |
1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 |
sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont,
iRowidReg, pPk->nKeyCol); VdbeCoverage(v);
}
/* If pIdx is an index on one or more expressions, then look through
** all the expressions in pWInfo and try to transform matching expressions
** into reference to index columns.
**
** Do not do this for the RHS of a LEFT JOIN. This is because the
** expression may be evaluated after OP_NullRow has been executed on
** the cursor. In this case it is important to do the full evaluation,
** as the result of the expression may not be NULL, even if all table
** column values are.
*/
if( pLevel->iLeftJoin==0 ){
whereIndexExprTrans(pIdx, iCur, iIdxCur, pWInfo);
}
/* Record the instruction used to terminate the loop. */
if( pLoop->wsFlags & WHERE_ONEROW ){
pLevel->op = OP_Noop;
}else if( bRev ){
pLevel->op = OP_Prev;
}else{
|
Changes to test/join5.test.
207 208 209 210 211 212 213 214 215 |
do_execsql_test 5.4 {
SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x
} 1
do_execsql_test 5.5 {
SELECT * FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x
} {0 0 1 {}}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 |
do_execsql_test 5.4 {
SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x
} 1
do_execsql_test 5.5 {
SELECT * FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x
} {0 0 1 {}}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 6.1 {
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2(y INTEGER PRIMARY KEY,a,b);
INSERT INTO t2 VALUES(1,2,3);
CREATE INDEX t2a ON t2(a);
CREATE INDEX t2b ON t2(b);
}
do_execsql_test 6.2 {
SELECT * FROM t1 LEFT JOIN t2 ON a=2 OR b=3 WHERE y IS NULL;
} {}
do_execsql_test 6.3.1 {
CREATE TABLE t3(x);
INSERT INTO t3 VALUES(1);
CREATE TABLE t4(y, z);
SELECT ifnull(z, '!!!') FROM t3 LEFT JOIN t4 ON (x=y);
} {!!!}
do_execsql_test 6.3.2 {
CREATE INDEX t4i ON t4(y, ifnull(z, '!!!'));
SELECT ifnull(z, '!!!') FROM t3 LEFT JOIN t4 ON (x=y);
} {!!!}
finish_test
|