Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch exp-agg-opt Excluding Merge-Ins
This is equivalent to a diff from 8a0b730d0e to dce2dfbe15
2018-06-05
| ||
23:21 | Avoid unnecessary loads of columns in an aggregate query that are not within an aggregate function and that are not part of the GROUP BY clause. (check-in: e15e100660 user: drh tags: trunk) | |
20:04 | Calculate non-aggregate expressions in the SELECT list of an aggregate query that does not use min() or max() once per group, instead of once per row visited. (Closed-Leaf check-in: dce2dfbe15 user: dan tags: exp-agg-opt) | |
13:54 | Merge trunk changes. (check-in: 047295c588 user: drh tags: multikey-opt-idea) | |
13:43 | Update and correct the documentation on the OP_OpenRead, OP_OpenWrite, and OP_ReopenIdx opcodes. No code changes other than the addition of an assert(). (check-in: 8a0b730d0e user: drh tags: trunk) | |
2018-06-04
| ||
19:24 | Version 3.24.0 (check-in: c7ee083322 user: drh tags: trunk, release, version-3.24.0) | |
Changes to src/select.c.
︙ | ︙ | |||
5095 5096 5097 5098 5099 5100 5101 5102 5103 5104 5105 5106 | for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){ ExprList *pList = pF->pExpr->x.pList; 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. */ | > > > > > > | | 5095 5096 5097 5098 5099 5100 5101 5102 5103 5104 5105 5106 5107 5108 5109 5110 5111 5112 5113 5114 5115 5116 5117 5118 5119 5120 | for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){ ExprList *pList = pF->pExpr->x.pList; 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, int regAcc, AggInfo *pAggInfo){ Vdbe *v = pParse->pVdbe; int i; int regHit = 0; int addrHitTest = 0; struct AggInfo_func *pF; struct AggInfo_col *pC; |
︙ | ︙ | |||
5164 5165 5166 5167 5168 5169 5170 5171 5172 5173 5174 5175 5176 5177 | ** to pC->iMem. But by the time the value is used, the original register ** may have been used, invalidating the underlying buffer holding the ** 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 ){ addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); VdbeCoverage(v); } sqlite3ExprCacheClear(pParse); for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){ sqlite3ExprCode(pParse, pC->pExpr, pC->iMem); } | > > > | 5170 5171 5172 5173 5174 5175 5176 5177 5178 5179 5180 5181 5182 5183 5184 5185 5186 | ** to pC->iMem. But by the time the value is used, the original register ** may have been used, invalidating the underlying buffer holding the ** 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; i<pAggInfo->nAccumulator; i++, pC++){ sqlite3ExprCode(pParse, pC->pExpr, pC->iMem); } |
︙ | ︙ | |||
6017 6018 6019 6020 6021 6022 6023 | addrReset = sqlite3VdbeMakeLabel(v); iAMem = pParse->nMem + 1; pParse->nMem += pGroupBy->nExpr; iBMem = pParse->nMem + 1; pParse->nMem += pGroupBy->nExpr; sqlite3VdbeAddOp2(v, OP_Integer, 0, iAbortFlag); VdbeComment((v, "clear abort flag")); | < < | 6026 6027 6028 6029 6030 6031 6032 6033 6034 6035 6036 6037 6038 6039 | addrReset = sqlite3VdbeMakeLabel(v); iAMem = pParse->nMem + 1; pParse->nMem += pGroupBy->nExpr; iBMem = pParse->nMem + 1; pParse->nMem += pGroupBy->nExpr; sqlite3VdbeAddOp2(v, OP_Integer, 0, iAbortFlag); VdbeComment((v, "clear abort flag")); 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 ** in the right order to begin with. */ |
︙ | ︙ | |||
6151 6152 6153 6154 6155 6156 6157 | sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); VdbeComment((v, "reset accumulator")); /* Update the aggregate accumulators based on the content of ** the current row */ sqlite3VdbeJumpHere(v, addr1); | | | 6158 6159 6160 6161 6162 6163 6164 6165 6166 6167 6168 6169 6170 6171 6172 | sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); VdbeComment((v, "reset accumulator")); /* Update the aggregate accumulators based on the content of ** the current row */ sqlite3VdbeJumpHere(v, addr1); updateAccumulator(pParse, iUseFlag, &sAggInfo); sqlite3VdbeAddOp2(v, OP_Integer, 1, iUseFlag); VdbeComment((v, "indicate data in accumulator")); /* End of the loop */ if( groupBySort ){ sqlite3VdbeAddOp2(v, OP_SorterNext, sAggInfo.sortingIdx, addrTopOfLoop); |
︙ | ︙ | |||
6203 6204 6205 6206 6207 6208 6209 6210 6211 6212 6213 6214 6215 6216 | sqlite3VdbeAddOp1(v, OP_Return, regOutputRow); VdbeComment((v, "end groupby result generator")); /* Generate a subroutine that will reset the group-by accumulator */ sqlite3VdbeResolveLabel(v, addrReset); resetAccumulator(pParse, &sAggInfo); sqlite3VdbeAddOp1(v, OP_Return, regReset); } /* endif pGroupBy. Begin aggregate queries without GROUP BY: */ else { #ifndef SQLITE_OMIT_BTREECOUNT Table *pTab; if( (pTab = isSimpleCount(p, &sAggInfo))!=0 ){ | > > | 6210 6211 6212 6213 6214 6215 6216 6217 6218 6219 6220 6221 6222 6223 6224 6225 | sqlite3VdbeAddOp1(v, OP_Return, regOutputRow); VdbeComment((v, "end groupby result generator")); /* 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 Table *pTab; if( (pTab = isSimpleCount(p, &sAggInfo))!=0 ){ |
︙ | ︙ | |||
6268 6269 6270 6271 6272 6273 6274 6275 6276 6277 6278 6279 6280 6281 | } sqlite3VdbeAddOp2(v, OP_Count, iCsr, sAggInfo.aFunc[0].iMem); sqlite3VdbeAddOp1(v, OP_Close, iCsr); explainSimpleCount(pParse, pTab, pBest); }else #endif /* SQLITE_OMIT_BTREECOUNT */ { /* 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 ); resetAccumulator(pParse, &sAggInfo); | > > > > > > > > > > > > > > > > > | 6277 6278 6279 6280 6281 6282 6283 6284 6285 6286 6287 6288 6289 6290 6291 6292 6293 6294 6295 6296 6297 6298 6299 6300 6301 6302 6303 6304 6305 6306 6307 | } sqlite3VdbeAddOp2(v, OP_Count, iCsr, sAggInfo.aFunc[0].iMem); 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); } } /* 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 ); resetAccumulator(pParse, &sAggInfo); |
︙ | ︙ | |||
6289 6290 6291 6292 6293 6294 6295 | SELECTTRACE(1,pParse,p,("WhereBegin\n")); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMaxOrderBy, 0, minMaxFlag, 0); if( pWInfo==0 ){ goto select_end; } | | > | 6315 6316 6317 6318 6319 6320 6321 6322 6323 6324 6325 6326 6327 6328 6329 6330 | SELECTTRACE(1,pParse,p,("WhereBegin\n")); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMaxOrderBy, 0, minMaxFlag, 0); if( pWInfo==0 ){ goto select_end; } 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"))); } sqlite3WhereEnd(pWInfo); finalizeAggFunctions(pParse, &sAggInfo); |
︙ | ︙ |
Changes to test/aggnested.test.
︙ | ︙ | |||
61 62 63 64 65 66 67 | NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1)); REPLACE INTO t2 VALUES(1,88,888,8888); 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; } | | | 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1)); REPLACE INTO t2 VALUES(1,88,888,8888); 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 1 11 111 1111} db2 close ##################### Test cases for ticket [bfbf38e5e9956ac69f] ############ # # This first test case is the original problem report: do_test aggnested-3.0 { db eval { |
︙ | ︙ |
Changes to test/e_select.test.
︙ | ︙ | |||
797 798 799 800 801 802 803 | do_select_tests e_select-4.1 { 1 "SELECT * FROM z1 LIMIT 1" {51.65 -59.58 belfries} 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} | | | 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 | do_select_tests e_select-4.1 { 1 "SELECT * FROM z1 LIMIT 1" {51.65 -59.58 belfries} 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 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 } 10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" { |
︙ | ︙ | |||
935 936 937 938 939 940 941 | CREATE TABLE a2(one PRIMARY KEY, three); INSERT INTO a2 VALUES(1, 1); 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 { | | | | | | | | 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 | CREATE TABLE a2(one PRIMARY KEY, three); INSERT INTO a2 VALUES(1, 1); 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" {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" {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. # do_select_tests e_select-4.7 { |
︙ | ︙ | |||
1124 1125 1126 1127 1128 1129 1130 | 1.2 "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y} 1.3 "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x} 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} | | | 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 | 1.2 "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y} 1.3 "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x} 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 j>6" {5 10} } # EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then # evaluated once for each group of rows. # # EVIDENCE-OF: R-53735-47017 If the expression is an aggregate # expression, it is evaluated across all rows in the group. |
︙ | ︙ | |||
1150 1151 1152 1153 1154 1155 1156 | # arbitrarily chosen row from within the group. # # 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 { | | | | | | | 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 | # arbitrarily chosen row from within the group. # # 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" {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 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. # # EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the # DISTINCT keyword, the number of rows returned by an aggregate query |
︙ | ︙ |
Changes to test/select5.test.
︙ | ︙ | |||
150 151 152 153 154 155 156 | SELECT a, b FROM t2 GROUP BY a, b; } } {1 2 1 4 6 4} do_test select5-5.5 { execsql { SELECT a, b FROM t2 GROUP BY a; } | | | 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 | SELECT a, b FROM t2 GROUP BY a, b; } } {1 2 1 4 6 4} do_test select5-5.5 { execsql { SELECT a, b FROM t2 GROUP BY a; } } {1 2 6 4} # Test rendering of columns for the GROUP BY clause. # do_test select5-5.11 { execsql { SELECT max(c), b*a, b, a FROM t2 GROUP BY b*a, b, a } |
︙ | ︙ |