Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with queries containing a min() or max() function for which the FILTER clause excludes all rows. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
b1d7e104e034655fe41bc55c562d91e8 |
| User & Date: | dan 2019-09-20 20:52:16 |
Context
|
2019-09-20
| ||
| 21:12 | Simplify the fix in the previous commit. (check-in: 5ef64b0f user: dan tags: trunk) | |
| 20:52 | Fix a problem with queries containing a min() or max() function for which the FILTER clause excludes all rows. (check-in: b1d7e104 user: dan tags: trunk) | |
|
2019-09-19
| ||
| 13:51 | Fix an assert() in fts3 that could fail when accessing a corrupt database. (check-in: 601ce953 user: dan tags: trunk) | |
Changes
Changes to src/select.c.
5339 5340 5341 5342 5343 5344 5345 5346 5347 5348 5349 5350 5351 5352 5353 5354 5355 5356 5357 5358 5359 5360 5361 5362 5363 5364 5365 .... 5402 5403 5404 5405 5406 5407 5408 5409 5410 5411 5412 5413 5414 5415 .... 6577 6578 6579 6580 6581 6582 6583 6584 6585 6586 6587 6588 6589 6590 6591 6592 6593 6594 6595 6596 6597 |
** of setting and clearing regAcc.
*/
static void updateAccumulator(Parse *pParse, int regAcc, AggInfo *pAggInfo){
Vdbe *v = pParse->pVdbe;
int i;
int regHit = 0;
int addrHitTest = 0;
struct AggInfo_func *pF;
struct AggInfo_col *pC;
pAggInfo->directMode = 1;
for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
int nArg;
int addrNext = 0;
int regAgg;
ExprList *pList = pF->pExpr->x.pList;
assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) );
assert( !IsWindowFunc(pF->pExpr) );
if( ExprHasProperty(pF->pExpr, EP_WinFunc) ){
Expr *pFilter = pF->pExpr->y.pWin->pFilter;
addrNext = sqlite3VdbeMakeLabel(pParse);
sqlite3ExprIfFalse(pParse, pFilter, addrNext, SQLITE_JUMPIFNULL);
}
if( pList ){
nArg = pList->nExpr;
regAgg = sqlite3GetTempRange(pParse, nArg);
sqlite3ExprCodeExprList(pParse, pList, regAgg, 0, SQLITE_ECEL_DUP);
................................................................................
}
if( regHit ){
addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); VdbeCoverage(v);
}
for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
sqlite3ExprCode(pParse, pC->pExpr, pC->iMem);
}
pAggInfo->directMode = 0;
if( addrHitTest ){
sqlite3VdbeJumpHere(v, addrHitTest);
}
}
/*
................................................................................
sqlite3VdbeAddOp1(v, OP_Close, iCsr);
explainSimpleCount(pParse, pTab, pBest);
}else
#endif /* SQLITE_OMIT_BTREECOUNT */
{
int regAcc = 0; /* "populate accumulators" flag */
/* If there are accumulator registers but no min() or max() functions,
** allocate register regAcc. Register regAcc will contain 0 the first
** time the inner loop runs, and 1 thereafter. The code generated
** by updateAccumulator() only updates the accumulator registers if
** regAcc contains 0. */
if( sAggInfo.nAccumulator ){
for(i=0; i<sAggInfo.nFunc; i++){
if( sAggInfo.aFunc[i].pFunc->funcFlags&SQLITE_FUNC_NEEDCOLL ) break;
}
if( i==sAggInfo.nFunc ){
regAcc = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, 0, regAcc);
}
}
|
> > > > > > > > > > > > > > > > > > > | | | | | > > > > > |
5339 5340 5341 5342 5343 5344 5345 5346 5347 5348 5349 5350 5351 5352 5353 5354 5355 5356 5357 5358 5359 5360 5361 5362 5363 5364 5365 5366 5367 5368 5369 5370 5371 5372 5373 5374 5375 5376 5377 .... 5414 5415 5416 5417 5418 5419 5420 5421 5422 5423 5424 5425 5426 5427 5428 5429 5430 5431 5432 5433 5434 .... 6596 6597 6598 6599 6600 6601 6602 6603 6604 6605 6606 6607 6608 6609 6610 6611 6612 6613 6614 6615 6616 6617 6618 6619 6620 6621 |
** of setting and clearing regAcc.
*/
static void updateAccumulator(Parse *pParse, int regAcc, AggInfo *pAggInfo){
Vdbe *v = pParse->pVdbe;
int i;
int regHit = 0;
int addrHitTest = 0;
int bFilterMinMax = 0;
struct AggInfo_func *pF;
struct AggInfo_col *pC;
pAggInfo->directMode = 1;
for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
int nArg;
int addrNext = 0;
int regAgg;
ExprList *pList = pF->pExpr->x.pList;
assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) );
assert( !IsWindowFunc(pF->pExpr) );
if( ExprHasProperty(pF->pExpr, EP_WinFunc) ){
Expr *pFilter = pF->pExpr->y.pWin->pFilter;
if( pAggInfo->nAccumulator
&& (pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL)
){
bFilterMinMax = 1;
if( regHit==0 ) regHit = ++pParse->nMem;
/* If this is the first row of the group (regAcc==0), clear the
** "magnet" register regHit so that the accumulator registers
** are populated even if the FILTER clause causes control to
** skip over the invocation of min() or max() altogether */
sqlite3VdbeAddOp2(v, OP_Copy, regAcc, regHit);
}
addrNext = sqlite3VdbeMakeLabel(pParse);
sqlite3ExprIfFalse(pParse, pFilter, addrNext, SQLITE_JUMPIFNULL);
}
if( pList ){
nArg = pList->nExpr;
regAgg = sqlite3GetTempRange(pParse, nArg);
sqlite3ExprCodeExprList(pParse, pList, regAgg, 0, SQLITE_ECEL_DUP);
................................................................................
}
if( regHit ){
addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); VdbeCoverage(v);
}
for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
sqlite3ExprCode(pParse, pC->pExpr, pC->iMem);
}
if( bFilterMinMax ){
/* If there is a min() or max() with a FILTER clause, then ensure that
** the "magnet" register is set to indicate "do not attract" after
** loading column values into the accumulator registers */
sqlite3VdbeAddOp2(v, OP_Integer, 1, regHit);
}
pAggInfo->directMode = 0;
if( addrHitTest ){
sqlite3VdbeJumpHere(v, addrHitTest);
}
}
/*
................................................................................
sqlite3VdbeAddOp1(v, OP_Close, iCsr);
explainSimpleCount(pParse, pTab, pBest);
}else
#endif /* SQLITE_OMIT_BTREECOUNT */
{
int regAcc = 0; /* "populate accumulators" flag */
/* If there are accumulator registers but no min() or max() functions
** without FILTER clauses, allocate register regAcc. Register regAcc
** will contain 0 the first time the inner loop runs, and 1 thereafter.
** The code generated by updateAccumulator() uses this to ensure
** that the accumulator registers are (a) updated only once if
** there are no min() or max functions or (b) always updated for the
** first row visited by the aggregate, so that they are updated at
** least once even if the FILTER clause means the min() or max()
** function visits zero rows. */
if( sAggInfo.nAccumulator ){
for(i=0; i<sAggInfo.nFunc; i++){
if( ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_WinFunc) ) continue;
if( sAggInfo.aFunc[i].pFunc->funcFlags&SQLITE_FUNC_NEEDCOLL ) break;
}
if( i==sAggInfo.nFunc ){
regAcc = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, 0, regAcc);
}
}
|
Changes to test/filter1.test.
96 97 98 99 100 101 102 103 104 |
do_catchsql_test 2.2 {
SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
} {1 {misuse of window function max()}}
do_catchsql_test 2.3 {
SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
} {1 {misuse of aggregate function count()}}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 |
do_catchsql_test 2.2 {
SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
} {1 {misuse of window function max()}}
do_catchsql_test 2.3 {
SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
} {1 {misuse of aggregate function count()}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
CREATE TABLE t1(a,b);
INSERT INTO t1 VALUES(1, 1);
}
do_execsql_test 3.1 {
SELECT b, max(a) FILTER (WHERE b='x') FROM t1;
} {1 {}}
do_execsql_test 3.2 {
CREATE TABLE t2(a, b, c);
INSERT INTO t2 VALUES(1, 2, 3);
INSERT INTO t2 VALUES(1, 3, 4);
INSERT INTO t2 VALUES(2, 5, 6);
INSERT INTO t2 VALUES(2, 7, 8);
}
do_execsql_test 3.3 {
SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a;
} {1 3 {} 2 6 {}}
do_execsql_test 3.4 {
DELETE FROM t2;
INSERT INTO t2 VALUES(1, 5, 'x');
INSERT INTO t2 VALUES(1, 2, 3);
INSERT INTO t2 VALUES(1, 4, 'x');
INSERT INTO t2 VALUES(2, 5, 6);
INSERT INTO t2 VALUES(2, 7, 8);
}
do_execsql_test 3.5 {
SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a;
} {1 x 5 2 6 {}}
finish_test
|