Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -5097,16 +5097,22 @@ assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) ); sqlite3VdbeAddOp2(v, OP_AggFinal, pF->iMem, pList ? pList->nExpr : 0); sqlite3VdbeAppendP4(v, pF->pFunc, P4_FUNCDEF); } } + /* ** Update the accumulator memory cells for an aggregate based on ** the current cursor position. +** +** If regAcc is non-zero and there are no min() or max() aggregates +** in pAggInfo, then only populate the pAggInfo->nAccumulator accumulator +** registers i register regAcc contains 0. The caller will take care +** of setting and clearing regAcc. */ -static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){ +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; @@ -5166,10 +5172,13 @@ ** text or blob value. See ticket [883034dcb5]. ** ** Another solution would be to change the OP_SCopy used to copy cached ** values to an OP_Copy. */ + if( regHit==0 && pAggInfo->nAccumulator ){ + regHit = regAcc; + } if( regHit ){ addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); VdbeCoverage(v); } sqlite3ExprCacheClear(pParse); for(i=0, pC=pAggInfo->aCol; inAccumulator; i++, pC++){ @@ -6019,12 +6028,10 @@ pParse->nMem += pGroupBy->nExpr; iBMem = pParse->nMem + 1; pParse->nMem += pGroupBy->nExpr; sqlite3VdbeAddOp2(v, OP_Integer, 0, iAbortFlag); VdbeComment((v, "clear abort flag")); - sqlite3VdbeAddOp2(v, OP_Integer, 0, iUseFlag); - VdbeComment((v, "indicate accumulator empty")); sqlite3VdbeAddOp3(v, OP_Null, 0, iAMem, iAMem+pGroupBy->nExpr-1); /* Begin a loop that will extract all source rows in GROUP BY order. ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information @@ -6153,11 +6160,11 @@ /* Update the aggregate accumulators based on the content of ** the current row */ sqlite3VdbeJumpHere(v, addr1); - updateAccumulator(pParse, &sAggInfo); + updateAccumulator(pParse, iUseFlag, &sAggInfo); sqlite3VdbeAddOp2(v, OP_Integer, 1, iUseFlag); VdbeComment((v, "indicate data in accumulator")); /* End of the loop */ @@ -6205,10 +6212,12 @@ /* Generate a subroutine that will reset the group-by accumulator */ sqlite3VdbeResolveLabel(v, addrReset); resetAccumulator(pParse, &sAggInfo); + sqlite3VdbeAddOp2(v, OP_Integer, 0, iUseFlag); + VdbeComment((v, "indicate accumulator empty")); sqlite3VdbeAddOp1(v, OP_Return, regReset); } /* endif pGroupBy. Begin aggregate queries without GROUP BY: */ else { #ifndef SQLITE_OMIT_BTREECOUNT @@ -6270,10 +6279,27 @@ 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; ifuncFlags&SQLITE_FUNC_NEEDCOLL ) break; + } + if( i==sAggInfo.nFunc ){ + regAcc = ++pParse->nMem; + sqlite3VdbeAddOp2(v, OP_Integer, 0, regAcc); + } + } + /* This case runs if the aggregate has no GROUP BY clause. The ** processing is much simpler since there is only a single row ** of output. */ assert( p->pGroupBy==0 ); @@ -6291,11 +6317,12 @@ pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMaxOrderBy, 0, minMaxFlag, 0); if( pWInfo==0 ){ goto select_end; } - updateAccumulator(pParse, &sAggInfo); + updateAccumulator(pParse, regAcc, &sAggInfo); + if( regAcc ) sqlite3VdbeAddOp2(v, OP_Integer, 1, regAcc); if( sqlite3WhereIsOrdered(pWInfo)>0 ){ sqlite3VdbeGoto(v, sqlite3WhereBreakLabel(pWInfo)); VdbeComment((v, "%s() by index", (minMaxFlag==WHERE_ORDERBY_MIN?"min":"max"))); } Index: test/aggnested.test ================================================================== --- test/aggnested.test +++ test/aggnested.test @@ -63,11 +63,11 @@ REPLACE INTO t2 VALUES(2,99,999,9999); SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2), t1.* FROM t1; } -} {A,B,B 3 33 333 3333} +} {A,B,B 1 11 111 1111} db2 close ##################### Test cases for ticket [bfbf38e5e9956ac69f] ############ # # This first test case is the original problem report: Index: test/e_select.test ================================================================== --- test/e_select.test +++ test/e_select.test @@ -799,11 +799,11 @@ 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} - 6 "SELECT count(*), * FROM z1" {6 63 born -26} + 6 "SELECT count(*), * FROM z1" {6 51.65 -59.58 belfries} 7 "SELECT max(a), * FROM z1" {63 63 born -26} 8 "SELECT *, min(a) FROM z1" {-5 {} 75 -5} 9 "SELECT *,* FROM z1,z2 LIMIT 1" { 51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21 @@ -937,17 +937,17 @@ INSERT INTO a2 VALUES(3, 2); INSERT INTO a2 VALUES(6, 3); INSERT INTO a2 VALUES(10, 4); } {} do_select_tests e_select-4.6 { - 1 "SELECT one, two, count(*) FROM a1" {4 10 4} - 2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {2 3 2} + 1 "SELECT one, two, count(*) FROM a1" {1 1 4} + 2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {1 1 2} 3 "SELECT one, two, count(*) FROM a1 WHERE one>3" {4 10 1} - 4 "SELECT *, count(*) FROM a1 JOIN a2" {4 10 10 4 16} - 5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} - 6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} - 7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6} + 4 "SELECT *, count(*) FROM a1 JOIN a2" {1 1 1 1 16} + 5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {1 1 1 3} + 6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {1 1 1 3} + 7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 1 1} } # EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then # each non-aggregate expression is evaluated against a row consisting # entirely of NULL values. @@ -1126,11 +1126,11 @@ 1.4 "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4} 2.1 "SELECT up FROM c1 GROUP BY up HAVING down>10" {y} 2.2 "SELECT up FROM c1 GROUP BY up HAVING up='y'" {y} - 2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6" {9 36} + 2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING j>6" {5 10} } # EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then # evaluated once for each group of rows. # @@ -1152,16 +1152,16 @@ # EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate # expression in the result-set, then all such expressions are evaluated # for the same row. # do_select_tests e_select-4.15 { - 1 "SELECT i, j FROM c2 GROUP BY i%2" {8 28 9 36} - 2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28} - 3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} - 4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} + 1 "SELECT i, j FROM c2 GROUP BY i%2" {2 1 1 0} + 2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {2 1 1 0} + 3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {} + 4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {} 5 "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)" - {2 5 boron 2 2 helium 1 3 lithium} + {2 4 beryllium 2 1 hydrogen 1 3 lithium} } # EVIDENCE-OF: R-19334-12811 Each group of input dataset rows # contributes a single row to the set of result rows. # Index: test/select5.test ================================================================== --- test/select5.test +++ test/select5.test @@ -152,11 +152,11 @@ } {1 2 1 4 6 4} do_test select5-5.5 { execsql { SELECT a, b FROM t2 GROUP BY a; } -} {1 4 6 4} +} {1 2 6 4} # Test rendering of columns for the GROUP BY clause. # do_test select5-5.11 { execsql {