Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not use indexed expressions to replace the expression being indexed on a RIGHT JOIN as that leads to problems, as shown by dbsqlfuzz a644e70d7683a7ca59c71861a153c1dccf8850b9. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
f113eebdbe68246fd38d640b0cdf0fd7 |
| User & Date: | drh 2022-12-20 01:48:43.300 |
References
|
2022-12-20
| ||
| 14:02 | Improvement to the dbsqlfuzz fix of [f113eebdbe68246f]. (check-in: 79d62956f2 user: drh tags: trunk) | |
Context
|
2022-12-20
| ||
| 14:02 | Improvement to the dbsqlfuzz fix of [f113eebdbe68246f]. (check-in: 79d62956f2 user: drh tags: trunk) | |
| 01:48 | Do not use indexed expressions to replace the expression being indexed on a RIGHT JOIN as that leads to problems, as shown by dbsqlfuzz a644e70d7683a7ca59c71861a153c1dccf8850b9. (check-in: f113eebdbe user: drh tags: trunk) | |
|
2022-12-19
| ||
| 20:14 | Fix a faulty assert() in fts5. (check-in: 569018170b user: dan tags: trunk) | |
Changes
Changes to src/expr.c.
| ︙ | ︙ | |||
6380 6381 6382 6383 6384 6385 6386 6387 6388 6389 6390 6391 6392 6393 |
}
}
if( pCol->iSorterColumn<0 ){
pCol->iSorterColumn = pAggInfo->nSortingColumn++;
}
fix_up_expr:
ExprSetVVAProperty(pExpr, EP_NoReduce);
pExpr->pAggInfo = pAggInfo;
if( pExpr->op==TK_COLUMN ){
pExpr->op = TK_AGG_COLUMN;
}
pExpr->iAgg = (i16)k;
}
| > | 6380 6381 6382 6383 6384 6385 6386 6387 6388 6389 6390 6391 6392 6393 6394 |
}
}
if( pCol->iSorterColumn<0 ){
pCol->iSorterColumn = pAggInfo->nSortingColumn++;
}
fix_up_expr:
ExprSetVVAProperty(pExpr, EP_NoReduce);
assert( pExpr->pAggInfo==0 || pExpr->pAggInfo==pAggInfo );
pExpr->pAggInfo = pAggInfo;
if( pExpr->op==TK_COLUMN ){
pExpr->op = TK_AGG_COLUMN;
}
pExpr->iAgg = (i16)k;
}
|
| ︙ | ︙ | |||
6415 6416 6417 6418 6419 6420 6421 6422 6423 6424 6425 6426 6427 6428 |
for(pIEpr=pParse->pIdxEpr; pIEpr; pIEpr=pIEpr->pIENext){
int iDataCur = pIEpr->iDataCur;
if( iDataCur<0 ) continue;
if( sqlite3ExprCompare(0, pExpr, pIEpr->pExpr, iDataCur)==0 ) break;
}
if( pIEpr==0 ) break;
if( NEVER(!ExprUseYTab(pExpr)) ) break;
/* If we reach this point, it means that expression pExpr can be
** translated into a reference to an index column as described by
** pIEpr.
*/
memset(&tmp, 0, sizeof(tmp));
tmp.op = TK_AGG_COLUMN;
| > | 6416 6417 6418 6419 6420 6421 6422 6423 6424 6425 6426 6427 6428 6429 6430 |
for(pIEpr=pParse->pIdxEpr; pIEpr; pIEpr=pIEpr->pIENext){
int iDataCur = pIEpr->iDataCur;
if( iDataCur<0 ) continue;
if( sqlite3ExprCompare(0, pExpr, pIEpr->pExpr, iDataCur)==0 ) break;
}
if( pIEpr==0 ) break;
if( NEVER(!ExprUseYTab(pExpr)) ) break;
assert( pExpr->pAggInfo==0 );
/* If we reach this point, it means that expression pExpr can be
** translated into a reference to an index column as described by
** pIEpr.
*/
memset(&tmp, 0, sizeof(tmp));
tmp.op = TK_AGG_COLUMN;
|
| ︙ | ︙ |
Changes to src/select.c.
| ︙ | ︙ | |||
7681 7682 7683 7684 7685 7686 7687 7688 7689 7690 7691 7692 7693 7694 |
(void(*)(sqlite3*,void*))agginfoFree, pAggInfo);
testcase( pParse->earlyCleanup );
}
if( db->mallocFailed ){
goto select_end;
}
pAggInfo->selId = p->selId;
memset(&sNC, 0, sizeof(sNC));
sNC.pParse = pParse;
sNC.pSrcList = pTabList;
sNC.uNC.pAggInfo = pAggInfo;
VVA_ONLY( sNC.ncFlags = NC_UAggInfo; )
pAggInfo->nSortingColumn = pGroupBy ? pGroupBy->nExpr : 0;
pAggInfo->pGroupBy = pGroupBy;
| > > > | 7681 7682 7683 7684 7685 7686 7687 7688 7689 7690 7691 7692 7693 7694 7695 7696 7697 |
(void(*)(sqlite3*,void*))agginfoFree, pAggInfo);
testcase( pParse->earlyCleanup );
}
if( db->mallocFailed ){
goto select_end;
}
pAggInfo->selId = p->selId;
#ifdef SQLITE_DEBUG
pAggInfo->pSelect = p;
#endif
memset(&sNC, 0, sizeof(sNC));
sNC.pParse = pParse;
sNC.pSrcList = pTabList;
sNC.uNC.pAggInfo = pAggInfo;
VVA_ONLY( sNC.ncFlags = NC_UAggInfo; )
pAggInfo->nSortingColumn = pGroupBy ? pGroupBy->nExpr : 0;
pAggInfo->pGroupBy = pGroupBy;
|
| ︙ | ︙ |
Changes to src/sqliteInt.h.
| ︙ | ︙ | |||
2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 |
Expr *pFExpr; /* Expression encoding the function */
FuncDef *pFunc; /* The aggregate function implementation */
int iDistinct; /* Ephemeral table used to enforce DISTINCT */
int iDistAddr; /* Address of OP_OpenEphemeral */
} *aFunc;
int nFunc; /* Number of entries in aFunc[] */
u32 selId; /* Select to which this AggInfo belongs */
};
/*
** Macros to compute aCol[] and aFunc[] register numbers.
**
** These macros should not be used prior to the call to
** assignAggregateRegisters() that computes the value of pAggInfo->iFirstReg.
| > > > | 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 |
Expr *pFExpr; /* Expression encoding the function */
FuncDef *pFunc; /* The aggregate function implementation */
int iDistinct; /* Ephemeral table used to enforce DISTINCT */
int iDistAddr; /* Address of OP_OpenEphemeral */
} *aFunc;
int nFunc; /* Number of entries in aFunc[] */
u32 selId; /* Select to which this AggInfo belongs */
#ifdef SQLITE_DEBUG
Select *pSelect; /* SELECT statement that this AggInfo supports */
#endif
};
/*
** Macros to compute aCol[] and aFunc[] register numbers.
**
** These macros should not be used prior to the call to
** assignAggregateRegisters() that computes the value of pAggInfo->iFirstReg.
|
| ︙ | ︙ |
Changes to src/where.c.
| ︙ | ︙ | |||
6236 6237 6238 6239 6240 6241 6242 |
op = OP_OpenWrite;
pWInfo->aiCurOnePass[1] = iIndexCur;
}else if( iAuxArg && (wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 ){
iIndexCur = iAuxArg;
op = OP_ReopenIdx;
}else{
iIndexCur = pParse->nTab++;
| > | > > | 6236 6237 6238 6239 6240 6241 6242 6243 6244 6245 6246 6247 6248 6249 6250 6251 6252 6253 |
op = OP_OpenWrite;
pWInfo->aiCurOnePass[1] = iIndexCur;
}else if( iAuxArg && (wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 ){
iIndexCur = iAuxArg;
op = OP_ReopenIdx;
}else{
iIndexCur = pParse->nTab++;
if( pIx->bHasExpr
&& OptimizationEnabled(db, SQLITE_IndexedExpr)
&& (pTabItem->fg.jointype & (JT_LTORJ|JT_RIGHT))==0
){
whereAddIndexedExpr(pParse, pIx, iIndexCur, pTabItem);
}
}
pLevel->iIdxCur = iIndexCur;
assert( pIx!=0 );
assert( pIx->pSchema==pTab->pSchema );
assert( iIndexCur>=0 );
|
| ︙ | ︙ |
Changes to test/tkt-99378177930f87bd.test.
| ︙ | ︙ | |||
144 145 146 147 148 149 150 |
SUM(c) AS t3,
SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
FROM t1
WHERE d BETWEEN 0 and 10
GROUP BY a;
} {~/Function/}
| | | > > > > > > | 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 |
SUM(c) AS t3,
SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
FROM t1
WHERE d BETWEEN 0 and 10
GROUP BY a;
} {~/Function/}
# 2022-12-20 dbsqlfuzz a644e70d7683a7ca59c71861a153c1dccf8850b9
#
do_execsql_test tkt-99378-300 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a);
CREATE INDEX i1 ON t1(a,a+a,a<a,a=a);
SELECT * FROM (SELECT * FROM t1) NATURAL JOIN t1 WHERE a=1 OR ((SELECT avg((SELECT * FROM (SELECT * FROM (SELECT * FROM t1 NATURAL JOIN t1 WHERE 1||a OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)) WHERE a=1 OR ((SELECT sum((SELECT 1 FROM t1 NATURAL RIGHT JOIN t1 WHERE a=a))) AND a<=10)) WHERE a=1 OR ((SELECT sum(a)OVER(ORDER BY a)))))) AND a>10);
} {}
finish_test
|