Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix an issue with the OmitNoopJoin optimization and add test cases that are specifically for that optimization. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
5480d124b74c9adaacc4fa9cb8156086 |
| User & Date: | drh 2013-06-28 13:43:33 |
Context
|
2013-06-28
| ||
| 17:29 | Add a bit to the SQLITE_TESTCTRL_OPTIMIZATIONS option for sqlite3_file_control() that will disable the use of SQLITE_STAT3 information in the query planner. (check-in: 60c19b86 user: drh tags: trunk) | |
| 13:43 | Fix an issue with the OmitNoopJoin optimization and add test cases that are specifically for that optimization. (check-in: 5480d124 user: drh tags: trunk) | |
| 01:24 | Refactor the Vdbe.noIO field as Vdbe.bIsReader. The meaning is inverted. (check-in: 59f98c5c user: drh tags: trunk) | |
Changes
Changes to src/test1.c.
5955
5956
5957
5958
5959
5960
5961
5962
5963
5964
5965
5966
5967
5968
5969
5970
5971
5972
5973
5974
5975
5976
5977
....
5984
5985
5986
5987
5988
5989
5990
5991
5992
5993
5994
5995
5996
5997
5998
|
const char *zOpt;
int onoff;
int mask = 0;
static const struct {
const char *zOptName;
int mask;
} aOpt[] = {
{ "all", SQLITE_AllOpts },
{ "query-flattener", SQLITE_QueryFlattener },
{ "column-cache", SQLITE_ColumnCache },
{ "groupby-order", SQLITE_GroupByOrder },
{ "factor-constants", SQLITE_FactorOutConst },
{ "real-as-int", SQLITE_IdxRealAsInt },
{ "distinct-opt", SQLITE_DistinctOpt },
{ "cover-idx-scan", SQLITE_CoverIdxScan },
{ "order-by-idx-join",SQLITE_OrderByIdxJoin },
};
if( objc!=4 ){
Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
return TCL_ERROR;
}
if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
................................................................................
}
}
if( onoff ) mask = ~mask;
if( i>=sizeof(aOpt)/sizeof(aOpt[0]) ){
Tcl_AppendResult(interp, "unknown optimization - should be one of:",
(char*)0);
for(i=0; i<sizeof(aOpt)/sizeof(aOpt[0]); i++){
Tcl_AppendResult(interp, " ", aOpt[i].zOptName);
}
return TCL_ERROR;
}
sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, db, mask);
return TCL_OK;
}
|
|
>
|
|
|
|
|
|
|
|
>
>
>
|
|
5955
5956
5957
5958
5959
5960
5961
5962
5963
5964
5965
5966
5967
5968
5969
5970
5971
5972
5973
5974
5975
5976
5977
5978
5979
5980
5981
....
5988
5989
5990
5991
5992
5993
5994
5995
5996
5997
5998
5999
6000
6001
6002
|
const char *zOpt;
int onoff;
int mask = 0;
static const struct {
const char *zOptName;
int mask;
} aOpt[] = {
{ "all", SQLITE_AllOpts },
{ "none", 0 },
{ "query-flattener", SQLITE_QueryFlattener },
{ "column-cache", SQLITE_ColumnCache },
{ "groupby-order", SQLITE_GroupByOrder },
{ "factor-constants", SQLITE_FactorOutConst },
{ "real-as-int", SQLITE_IdxRealAsInt },
{ "distinct-opt", SQLITE_DistinctOpt },
{ "cover-idx-scan", SQLITE_CoverIdxScan },
{ "order-by-idx-join", SQLITE_OrderByIdxJoin },
{ "transitive", SQLITE_Transitive },
{ "subquery-coroutine", SQLITE_SubqCoroutine },
{ "omit-noop-join", SQLITE_OmitNoopJoin },
};
if( objc!=4 ){
Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
return TCL_ERROR;
}
if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
................................................................................
}
}
if( onoff ) mask = ~mask;
if( i>=sizeof(aOpt)/sizeof(aOpt[0]) ){
Tcl_AppendResult(interp, "unknown optimization - should be one of:",
(char*)0);
for(i=0; i<sizeof(aOpt)/sizeof(aOpt[0]); i++){
Tcl_AppendResult(interp, " ", aOpt[i].zOptName, (char*)0);
}
return TCL_ERROR;
}
sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, db, mask);
return TCL_OK;
}
|
Changes to src/where.c.
5792 5793 5794 5795 5796 5797 5798 5799 5800 5801 5802 5803 5804 5805 5806 5807 5808 5809 5810 5811 5812 5813 |
if( pWInfo->nLevel>=2
&& pResultSet!=0
&& OptimizationEnabled(db, SQLITE_OmitNoopJoin)
){
Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
while( pWInfo->nLevel>=2 ){
pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
&& (pLoop->wsFlags & WHERE_ONEROW)==0
){
break;
}
if( (tabUsed & pLoop->maskSelf)!=0 ) break;
WHERETRACE(0xffff, ("-> drop loop %c not used\n", pLoop->cId));
pWInfo->nLevel--;
nTabList--;
}
}
WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;
|
> > > > > > > > > > |
5792 5793 5794 5795 5796 5797 5798 5799 5800 5801 5802 5803 5804 5805 5806 5807 5808 5809 5810 5811 5812 5813 5814 5815 5816 5817 5818 5819 5820 5821 5822 5823 |
if( pWInfo->nLevel>=2
&& pResultSet!=0
&& OptimizationEnabled(db, SQLITE_OmitNoopJoin)
){
Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
while( pWInfo->nLevel>=2 ){
WhereTerm *pTerm, *pEnd;
pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
&& (pLoop->wsFlags & WHERE_ONEROW)==0
){
break;
}
if( (tabUsed & pLoop->maskSelf)!=0 ) break;
pEnd = sWLB.pWC->a + sWLB.pWC->nTerm;
for(pTerm=sWLB.pWC->a; pTerm<pEnd; pTerm++){
if( (pTerm->prereqAll & pLoop->maskSelf)!=0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
){
break;
}
}
if( pTerm<pEnd ) break;
WHERETRACE(0xffff, ("-> drop loop %c not used\n", pLoop->cId));
pWInfo->nLevel--;
nTabList--;
}
}
WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;
|
Changes to test/where3.test.
431 432 433 434 435 436 437 438 439 440 |
do_test where3-6.$cnt.8 {
set sql "SELECT * FROM t6w NATURAL JOIN t6x NATURAL JOIN t6y"
append sql " JOIN t6z USING(a) "
append sql $::predicate
db eval $sql
} {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > |
431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 |
do_test where3-6.$cnt.8 {
set sql "SELECT * FROM t6w NATURAL JOIN t6x NATURAL JOIN t6y"
append sql " JOIN t6z USING(a) "
append sql $::predicate
db eval $sql
} {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
}
do_execsql_test where3-7-setup {
CREATE TABLE t71(x1 INTEGER PRIMARY KEY, y1);
CREATE TABLE t72(x2 INTEGER PRIMARY KEY, y2);
CREATE TABLE t73(x3, y3);
CREATE TABLE t74(x4, y4);
INSERT INTO t71 VALUES(123,234);
INSERT INTO t72 VALUES(234,345);
INSERT INTO t73 VALUES(123,234);
INSERT INTO t74 VALUES(234,345);
INSERT INTO t74 VALUES(234,678);
} {}
foreach disabled_opt {none omit-noop-join all} {
optimization_control db all 1
optimization_control db $disabled_opt 0
do_execsql_test where3-7.$disabled_opt.1 {
SELECT x1 FROM t71 LEFT JOIN t72 ON x2=y1;
} {123}
do_execsql_test where3-7.$disabled_opt.2 {
SELECT x1 FROM t71 LEFT JOIN t72 ON x2=y1 WHERE y2 IS NULL;
} {}
do_execsql_test where3-7.$disabled_opt.3 {
SELECT x1 FROM t71 LEFT JOIN t72 ON x2=y1 WHERE y2 IS NOT NULL;
} {123}
do_execsql_test where3-7.$disabled_opt.4 {
SELECT x1 FROM t71 LEFT JOIN t72 ON x2=y1 AND y2 IS NULL;
} {123}
do_execsql_test where3-7.$disabled_opt.5 {
SELECT x1 FROM t71 LEFT JOIN t72 ON x2=y1 AND y2 IS NOT NULL;
} {123}
do_execsql_test where3-7.$disabled_opt.6 {
SELECT x3 FROM t73 LEFT JOIN t72 ON x2=y3;
} {123}
do_execsql_test where3-7.$disabled_opt.7 {
SELECT DISTINCT x3 FROM t73 LEFT JOIN t72 ON x2=y3;
} {123}
do_execsql_test where3-7.$disabled_opt.8 {
SELECT x3 FROM t73 LEFT JOIN t74 ON x4=y3;
} {123 123}
do_execsql_test where3-7.$disabled_opt.9 {
SELECT DISTINCT x3 FROM t73 LEFT JOIN t74 ON x4=y3;
} {123}
}
finish_test
|