Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Progress toward getting DISTINCT to use a seek to advance to the next distinct row, when driven by an appropriate index. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | skip-ahead-distinct |
Files: | files | file ages | folders |
SHA1: |
9e14aa14cff7ebf7a5e93ce7377838da |
User & Date: | drh 2016-04-15 01:55:49.683 |
Context
2016-04-15
| ||
13:24 | Fixed crash bugs. Still sometimes gets the wrong answers. (check-in: 2afcb2c752 user: drh tags: skip-ahead-distinct) | |
01:55 | Progress toward getting DISTINCT to use a seek to advance to the next distinct row, when driven by an appropriate index. (check-in: 9e14aa14cf user: drh tags: skip-ahead-distinct) | |
2016-04-14
| ||
19:29 | Work toward improving analysis and code generation for DISTINCT and aggregate queries. (Leaf check-in: 129083bd5e user: drh tags: better-distinct-agg) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
742 743 744 745 746 747 748 749 750 751 752 753 754 755 | /* If the DISTINCT keyword was present on the SELECT statement ** and this row has been seen before, then do not make this row ** part of the result. */ if( hasDistinct ){ switch( pDistinct->eTnctType ){ case WHERE_DISTINCT_ORDERED: { VdbeOp *pOp; /* No longer required OpenEphemeral instr. */ int iJump; /* Jump destination */ int regPrev; /* Previous row content */ /* Allocate space for the previous row */ regPrev = pParse->nMem+1; pParse->nMem += nResultCol; | > | 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 | /* If the DISTINCT keyword was present on the SELECT statement ** and this row has been seen before, then do not make this row ** part of the result. */ if( hasDistinct ){ switch( pDistinct->eTnctType ){ case WHERE_DISTINCT_ORDERED: { #if 0 VdbeOp *pOp; /* No longer required OpenEphemeral instr. */ int iJump; /* Jump destination */ int regPrev; /* Previous row content */ /* Allocate space for the previous row */ regPrev = pParse->nMem+1; pParse->nMem += nResultCol; |
︙ | ︙ | |||
778 779 780 781 782 783 784 785 786 787 788 789 790 791 | } sqlite3VdbeChangeP4(v, -1, (const char *)pColl, P4_COLLSEQ); sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); } assert( sqlite3VdbeCurrentAddr(v)==iJump || pParse->db->mallocFailed ); sqlite3VdbeAddOp3(v, OP_Copy, regResult, regPrev, nResultCol-1); break; } case WHERE_DISTINCT_UNIQUE: { sqlite3VdbeChangeToNoop(v, pDistinct->addrTnct); break; } | > | 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 | } sqlite3VdbeChangeP4(v, -1, (const char *)pColl, P4_COLLSEQ); sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); } assert( sqlite3VdbeCurrentAddr(v)==iJump || pParse->db->mallocFailed ); sqlite3VdbeAddOp3(v, OP_Copy, regResult, regPrev, nResultCol-1); break; #endif } case WHERE_DISTINCT_UNIQUE: { sqlite3VdbeChangeToNoop(v, pDistinct->addrTnct); break; } |
︙ | ︙ |
Changes to src/shell.c.
︙ | ︙ | |||
1729 1730 1731 1732 1733 1734 1735 | if( str_in_array(zOp, azNext) ){ for(i=p2op; i<iOp; i++) p->aiIndent[i] += 2; } if( str_in_array(zOp, azGoto) && p2op<p->nIndent && (abYield[p2op] || sqlite3_column_int(pSql, 2)) ){ | | | 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 | if( str_in_array(zOp, azNext) ){ for(i=p2op; i<iOp; i++) p->aiIndent[i] += 2; } if( str_in_array(zOp, azGoto) && p2op<p->nIndent && (abYield[p2op] || sqlite3_column_int(pSql, 2)) ){ for(i=p2op; i<iOp; i++) p->aiIndent[i] += 2; } } p->iIndent = 0; sqlite3_free(abYield); sqlite3_reset(pSql); } |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
3871 3872 3873 3874 3875 3876 3877 3878 3879 3880 3881 3882 3883 3884 | /* Load the lowest cost path into pWInfo */ for(iLoop=0; iLoop<nLoop; iLoop++){ WhereLevel *pLevel = pWInfo->a + iLoop; pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop]; pLevel->iFrom = pWLoop->iTab; pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor; } if( (pWInfo->wctrlFlags & WHERE_WANT_DISTINCT)!=0 && (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0 && pWInfo->eDistinct==WHERE_DISTINCT_NOOP && nRowEst ){ Bitmask notUsed; int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pDistinctSet, pFrom, | > | 3871 3872 3873 3874 3875 3876 3877 3878 3879 3880 3881 3882 3883 3884 3885 | /* Load the lowest cost path into pWInfo */ for(iLoop=0; iLoop<nLoop; iLoop++){ WhereLevel *pLevel = pWInfo->a + iLoop; pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop]; pLevel->iFrom = pWLoop->iTab; pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor; } assert( pWInfo->pDistinctSet || (pWInfo->wctrlFlags&WHERE_WANT_DISTINCT)==0 ); if( (pWInfo->wctrlFlags & WHERE_WANT_DISTINCT)!=0 && (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0 && pWInfo->eDistinct==WHERE_DISTINCT_NOOP && nRowEst ){ Bitmask notUsed; int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pDistinctSet, pFrom, |
︙ | ︙ | |||
4484 4485 4486 4487 4488 4489 4490 4491 4492 4493 4494 4495 4496 4497 | assert( iIndexCur>=0 ); if( op ){ sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIx); if( (pLoop->wsFlags & WHERE_CONSTRAINT)!=0 && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0 && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 ){ sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */ } VdbeComment((v, "%s", pIx->zName)); #ifdef SQLITE_ENABLE_COLUMN_USED_MASK { u64 colUsed = 0; | > | 4485 4486 4487 4488 4489 4490 4491 4492 4493 4494 4495 4496 4497 4498 4499 | assert( iIndexCur>=0 ); if( op ){ sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIx); if( (pLoop->wsFlags & WHERE_CONSTRAINT)!=0 && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0 && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 && pWInfo->eDistinct!=WHERE_DISTINCT_ORDERED ){ sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */ } VdbeComment((v, "%s", pIx->zName)); #ifdef SQLITE_ENABLE_COLUMN_USED_MASK { u64 colUsed = 0; |
︙ | ︙ | |||
4567 4568 4569 4570 4571 4572 4573 4574 4575 4576 4577 4578 4579 4580 | WhereLoop *pLoop; SrcList *pTabList = pWInfo->pTabList; sqlite3 *db = pParse->db; /* Generate loop termination code. */ VdbeModuleComment((v, "End WHERE-core")); sqlite3ExprCacheClear(pParse); for(i=pWInfo->nLevel-1; i>=0; i--){ int addr; pLevel = &pWInfo->a[i]; pLoop = pLevel->pWLoop; sqlite3VdbeResolveLabel(v, pLevel->addrCont); if( pLevel->op!=OP_Noop ){ | > > > > > > > > > > > > > > > > > > > > > | | | | | | > | 4569 4570 4571 4572 4573 4574 4575 4576 4577 4578 4579 4580 4581 4582 4583 4584 4585 4586 4587 4588 4589 4590 4591 4592 4593 4594 4595 4596 4597 4598 4599 4600 4601 4602 4603 4604 4605 4606 4607 4608 4609 4610 4611 4612 4613 4614 4615 4616 4617 | WhereLoop *pLoop; SrcList *pTabList = pWInfo->pTabList; sqlite3 *db = pParse->db; /* Generate loop termination code. */ VdbeModuleComment((v, "End WHERE-core")); assert( pWInfo->eDistinct!=WHERE_DISTINCT_ORDERED || pWInfo->pDistinctSet!=0); sqlite3ExprCacheClear(pParse); for(i=pWInfo->nLevel-1; i>=0; i--){ int addr; pLevel = &pWInfo->a[i]; pLoop = pLevel->pWLoop; sqlite3VdbeResolveLabel(v, pLevel->addrCont); if( pLevel->op!=OP_Noop ){ if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED ){ int j, k, op; int r1 = pParse->nMem+1; int n = 0; ExprList *pX = pWInfo->pDistinctSet; for(j=0; j<pX->nExpr; j++){ Expr *pE = sqlite3ExprSkipCollate(pX->a[j].pExpr); if( pE->op==TK_COLUMN && pE->iTable==pLevel->iTabCur ) n++; } for(j=0; j<n; j++){ sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j); } pParse->nMem += n; op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT; k = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n); VdbeCoverageIf(v, op==OP_SeekLT); VdbeCoverageIf(v, op==OP_SeekGT); sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2); sqlite3VdbeJumpHere(v, k); }else{ sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3); sqlite3VdbeChangeP5(v, pLevel->p5); VdbeCoverage(v); VdbeCoverageIf(v, pLevel->op==OP_Next); VdbeCoverageIf(v, pLevel->op==OP_Prev); VdbeCoverageIf(v, pLevel->op==OP_VNext); } } if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){ struct InLoop *pIn; int j; sqlite3VdbeResolveLabel(v, pLevel->addrNxt); for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){ sqlite3VdbeJumpHere(v, pIn->addrInTop+1); |
︙ | ︙ |