Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Use OP_SorterOpen instead of OP_OpenEphemeral to implement GROUP BY. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | merge-sort |
Files: | files | file ages | folders |
SHA1: |
ebf819aaa555bd79fddfc0a6f9827a25 |
User & Date: | drh 2011-09-01 16:01:27.777 |
Context
2011-09-02
| ||
10:31 | Instead of a temporary b-tree, use a linked-list and merge-sort to sort records in main memory in vdbesort.c. (check-in: 7769fb988d user: dan tags: merge-sort) | |
2011-09-01
| ||
16:01 | Use OP_SorterOpen instead of OP_OpenEphemeral to implement GROUP BY. (check-in: ebf819aaa5 user: drh tags: merge-sort) | |
15:32 | Experimental code-generator changes to utilize new opcodes for sorting. (check-in: bab2e560f6 user: drh tags: merge-sort) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
4029 4030 4031 4032 4033 4034 4035 4036 4037 4038 4039 4040 4041 4042 | int iBMem; /* First Mem address for previous GROUP BY */ int iUseFlag; /* Mem address holding flag indicating that at least ** one row of the input to the aggregator has been ** processed */ int iAbortFlag; /* Mem address which causes query abort if positive */ int groupBySort; /* Rows come from source in GROUP BY order */ int addrEnd; /* End of processing for this SELECT */ /* Remove any and all aliases between the result set and the ** GROUP BY clause. */ if( pGroupBy ){ int k; /* Loop counter */ struct ExprList_item *pItem; /* For looping over expression in a list */ | > > | 4029 4030 4031 4032 4033 4034 4035 4036 4037 4038 4039 4040 4041 4042 4043 4044 | int iBMem; /* First Mem address for previous GROUP BY */ int iUseFlag; /* Mem address holding flag indicating that at least ** one row of the input to the aggregator has been ** processed */ int iAbortFlag; /* Mem address which causes query abort if positive */ int groupBySort; /* Rows come from source in GROUP BY order */ int addrEnd; /* End of processing for this SELECT */ int sortPTab = 0; /* Pseudotable used to decode sorting results */ int sortOut = 0; /* Output register from the sorter */ /* Remove any and all aliases between the result set and the ** GROUP BY clause. */ if( pGroupBy ){ int k; /* Loop counter */ struct ExprList_item *pItem; /* For looping over expression in a list */ |
︙ | ︙ | |||
4090 4091 4092 4093 4094 4095 4096 | int addrTopOfLoop; /* Top of the input loop */ int addrSortingIdx; /* The OP_OpenEphemeral for the sorting index */ int addrReset; /* Subroutine for resetting the accumulator */ int regReset; /* Return address register for reset subroutine */ /* If there is a GROUP BY clause we might need a sorting index to ** implement it. Allocate that sorting index now. If it turns out | | | | 4092 4093 4094 4095 4096 4097 4098 4099 4100 4101 4102 4103 4104 4105 4106 4107 4108 4109 4110 4111 | int addrTopOfLoop; /* Top of the input loop */ int addrSortingIdx; /* The OP_OpenEphemeral for the sorting index */ int addrReset; /* Subroutine for resetting the accumulator */ int regReset; /* Return address register for reset subroutine */ /* If there is a GROUP BY clause we might need a sorting index to ** implement it. Allocate that sorting index now. If it turns out ** that we do not need it after all, the OP_SorterOpen instruction ** will be converted into a Noop. */ sAggInfo.sortingIdx = pParse->nTab++; pKeyInfo = keyInfoFromExprList(pParse, pGroupBy); addrSortingIdx = sqlite3VdbeAddOp4(v, OP_SorterOpen, sAggInfo.sortingIdx, sAggInfo.nSortingColumn, 0, (char*)pKeyInfo, P4_KEYINFO_HANDOFF); /* Initialize memory locations used by GROUP BY aggregate processing */ iUseFlag = ++pParse->nMem; iAbortFlag = ++pParse->nMem; |
︙ | ︙ | |||
4176 4177 4178 4179 4180 4181 4182 | sqlite3VdbeAddOp2(v, OP_SCopy, r2, r1); } j++; } } regRecord = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regRecord); | | > > > | > > > | > | 4178 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 4190 4191 4192 4193 4194 4195 4196 4197 4198 4199 4200 4201 4202 4203 4204 4205 4206 4207 4208 4209 4210 4211 4212 4213 4214 4215 4216 4217 4218 | sqlite3VdbeAddOp2(v, OP_SCopy, r2, r1); } j++; } } regRecord = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regRecord); sqlite3VdbeAddOp2(v, OP_SorterInsert, sAggInfo.sortingIdx, regRecord); sqlite3ReleaseTempReg(pParse, regRecord); sqlite3ReleaseTempRange(pParse, regBase, nCol); sqlite3WhereEnd(pWInfo); sortPTab = pParse->nTab++; sortOut = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp3(v, OP_OpenPseudo, sortPTab, sortOut, nCol); sqlite3VdbeAddOp2(v, OP_SorterSort, sAggInfo.sortingIdx, addrEnd); VdbeComment((v, "GROUP BY sort")); sAggInfo.useSortingIdx = 1; sqlite3ExprCacheClear(pParse); } /* Evaluate the current GROUP BY terms and store in b0, b1, b2... ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth) ** Then compare the current GROUP BY terms against the GROUP BY terms ** from the previous row currently stored in a0, a1, a2... */ addrTopOfLoop = sqlite3VdbeCurrentAddr(v); sqlite3ExprCacheClear(pParse); if( groupBySort ){ sqlite3VdbeAddOp2(v, OP_SorterData, sAggInfo.sortingIdx, sortOut); } for(j=0; j<pGroupBy->nExpr; j++){ if( groupBySort ){ sqlite3VdbeAddOp3(v, OP_Column, sortPTab, j, iBMem+j); if( j==0 ) sqlite3VdbeChangeP5(v, OPFLAG_CLEARCACHE); }else{ sAggInfo.directMode = 1; sqlite3ExprCode(pParse, pGroupBy->a[j].pExpr, iBMem+j); } } sqlite3VdbeAddOp4(v, OP_Compare, iAMem, iBMem, pGroupBy->nExpr, (char*)pKeyInfo, P4_KEYINFO); |
︙ | ︙ | |||
4234 4235 4236 4237 4238 4239 4240 | updateAccumulator(pParse, &sAggInfo); sqlite3VdbeAddOp2(v, OP_Integer, 1, iUseFlag); VdbeComment((v, "indicate data in accumulator")); /* End of the loop */ if( groupBySort ){ | | | 4243 4244 4245 4246 4247 4248 4249 4250 4251 4252 4253 4254 4255 4256 4257 | updateAccumulator(pParse, &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); }else{ sqlite3WhereEnd(pWInfo); sqlite3VdbeChangeToNoop(v, addrSortingIdx, 1); } /* Output the final row of result */ |
︙ | ︙ |
Changes to test/distinct.test.
︙ | ︙ | |||
41 42 43 44 45 46 47 | uplevel [list do_test $tn [list is_distinct_noop $sql] 0] } proc do_temptables_test {tn sql temptables} { uplevel [list do_test $tn [subst -novar { set ret "" db eval "EXPLAIN [set sql]" { | | | 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | uplevel [list do_test $tn [list is_distinct_noop $sql] 0] } proc do_temptables_test {tn sql temptables} { uplevel [list do_test $tn [subst -novar { set ret "" db eval "EXPLAIN [set sql]" { if {$opcode == "OpenEphemeral" || $opcode == "SorterOpen"} { if {$p5 != "10" && $p5!="00"} { error "p5 = $p5" } if {$p5 == "10"} { lappend ret hash } else { lappend ret btree } } |
︙ | ︙ |