Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not allow automatic indices for the RHS of IN expressions which are not correlated subqueries. Ticket [8011086c85c6c404014c9] |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
267492d3a7eff7b7b24ed6bd6d6ff551 |
| User & Date: | drh 2010-07-28 02:53:37 |
References
|
2010-08-04
| ||
| 12:13 | Backport fix [267492d3a7eff7b] for the performance regression caused by automatic indexing and reported by ticket [8011086c85c6c4040]. check-in: 3f367fe0 user: drh tags: branch-3.7.0 | |
Context
|
2010-07-28
| ||
| 15:49 | Add documentation for the SQLITE_STATUS_MALLOC_COUNT parameter to sqlite3_status(). check-in: dbfbdb60 user: drh tags: trunk | |
| 02:53 | Do not allow automatic indices for the RHS of IN expressions which are not correlated subqueries. Ticket [8011086c85c6c404014c9] check-in: 267492d3 user: drh tags: trunk | |
|
2010-07-27
| ||
| 16:42 | Update the makefiles to prefer TCL version 8.5 instead of 8.4. check-in: 8118de2a user: drh tags: trunk | |
Changes
Changes to src/expr.c.
1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 |
}
}
if( eType==0 ){
/* Could not found an existing table or index to use as the RHS b-tree.
** We will have to generate an ephemeral table to do the job.
*/
int rMayHaveNull = 0;
eType = IN_INDEX_EPH;
if( prNotFound ){
*prNotFound = rMayHaveNull = ++pParse->nMem;
}else if( pX->pLeft->iColumn<0 && !ExprHasAnyProperty(pX, EP_xIsSelect) ){
eType = IN_INDEX_ROWID;
}
sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
}else{
pX->iTable = iTab;
}
return eType;
}
#endif
|
> > > > | | | > > |
1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 |
}
}
if( eType==0 ){
/* Could not found an existing table or index to use as the RHS b-tree.
** We will have to generate an ephemeral table to do the job.
*/
double savedNQueryLoop = pParse->nQueryLoop;
int rMayHaveNull = 0;
eType = IN_INDEX_EPH;
if( prNotFound ){
*prNotFound = rMayHaveNull = ++pParse->nMem;
}else{
testcase( pParse->nQueryLoop>(double)1 );
pParse->nQueryLoop = (double)1;
if( pX->pLeft->iColumn<0 && !ExprHasAnyProperty(pX, EP_xIsSelect) ){
eType = IN_INDEX_ROWID;
}
}
sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
pParse->nQueryLoop = savedNQueryLoop;
}else{
pX->iTable = iTab;
}
return eType;
}
#endif
|
Changes to test/autoindex1.test.
131 132 133 134 135 136 137 138 139 |
JOIN t4 AS x6 ON x6.a=x5.b
JOIN t4 AS x7 ON x7.a=x6.b
JOIN t4 AS x8 ON x8.a=x7.b
JOIN t4 AS x9 ON x9.a=x8.b
JOIN t4 AS x10 ON x10.a=x9.b;
}
} {4087}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 |
JOIN t4 AS x6 ON x6.a=x5.b
JOIN t4 AS x7 ON x7.a=x6.b
JOIN t4 AS x8 ON x8.a=x7.b
JOIN t4 AS x9 ON x9.a=x8.b
JOIN t4 AS x10 ON x10.a=x9.b;
}
} {4087}
# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
# Make sure automatic indices are not created for the RHS of an IN expression
# that is not a correlated subquery.
#
do_test autoindex1-500 {
db eval {
CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
EXPLAIN QUERY PLAN
SELECT b FROM t501
WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
}
} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}
do_test autoindex1-501 {
db eval {
EXPLAIN QUERY PLAN
SELECT b FROM t501
WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
}
} {0 0 {TABLE t501} 0 0 {TABLE t502 WITH AUTOMATIC INDEX}}
do_test autoindex1-502 {
db eval {
EXPLAIN QUERY PLAN
SELECT b FROM t501
WHERE t501.a=123
AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
}
} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}
finish_test
|