Good news, everyone! I've taught SQLite to feel love! No, wait, that's not it. I spotted the thread 'how to use group_concat uniquely' today, and felt inspired, so I wrote a quick patch.
The patch allows a multi-argument aggregate function to be passed DISTINCT, under the restriction that every other argument must be constant. This allows for, among other things, the following syntax: sqlite> select group_concat(distinct type, ';') from sqlite_master; table;index But you still can't do it if there is more than one column referenced: sqlite> select group_concat(distinct type, tbl_name) from sqlite_master; Error: DISTINCT aggregates must have at most one non-constant argument I know how I would implement that, but that's way more effort than I'm willing to expend right now. Here's a quick diff. If people (in particular, DRH) think it's worth it, I'll write up the test cases and submit a more formal patch. Index: src/select.c =================================================================== --- src/select.c +++ src/select.c @@ -3487,10 +3487,74 @@ if( pParse->nErr || db->mallocFailed ) return; sqlite3SelectAddTypeInfo(pParse, p); } /* +** Set up and validate an aggregate being called with a DISTINCT argument. +** +** Verifies that one of the following is true: +** +** * The aggregate takes exactly one argument. +** * The aggregate is being passed at most one non-constant argument. +** +** If neither of these is satisfied, the return value is zero. +** Otherwise, the return value is nonzero, and the following are performed: +** +** * pFunc->iDistinctArg is set to the index of the argument that is +** DISTINCT. +** * keyExprList is filled out to refer to only the distinct column. +** Note that all pointers are aliased to the original expression list; +** no freeing must be done. +** +*/ + +static int setupDistinctAggregate(Parse *pParse, struct AggInfo_func *pFunc, ExprList *keyExprList){ + int i; + ExprList *pEL = pFunc->pExpr->x.pList; + struct ExprList_item *pELi; + pFunc->iDistinctArg = -1; + if( pEL==0 ){ + return 0; + } + if( pEL->nExpr==1 ){ + /* If there is only one argument to the aggregate, that argument must be the + ** DISTINCT one. + */ + + pFunc->iDistinctArg = 0; + }else{ + /* Okay, there is more than one argument to this aggregate. + ** Require that at most one is non-constant. + */ + for(i=0, pELi=pEL->a; i<pEL->nExpr; i++, pELi++){ + if( !sqlite3ExprIsConstantOrFunction(pELi->pExpr) ) + { + if( pFunc->iDistinctArg>=0 ){ + return 0; + } + pFunc->iDistinctArg = i; + } + } + + if( pFunc->iDistinctArg<0 ){ + /* All of the arguments passed to this function are constant. + ** Arbitrarily choose the first argument for the DISTINCTness. + ** TODO: somehow degrade this to a non-DISTINCT for this case. + */ + pFunc->iDistinctArg = 0; + } + } + + keyExprList->nExpr = 1; + keyExprList->nAlloc = 0; + keyExprList->iECursor = pEL->iECursor; + keyExprList->a = &pEL->a[pFunc->iDistinctArg]; + + return 1; +} + +/* ** Reset the aggregate accumulator. ** ** The aggregate accumulator is a set of memory cells that hold ** intermediate results while calculating an aggregate. This ** routine simply stores NULLs in all of those memory cells. @@ -3507,17 +3571,18 @@ } for(pFunc=pAggInfo->aFunc, i=0; i<pAggInfo->nFunc; i++, pFunc++){ sqlite3VdbeAddOp2(v, OP_Null, 0, pFunc->iMem); if( pFunc->iDistinct>=0 ){ Expr *pE = pFunc->pExpr; + ExprList keyEL; assert( !ExprHasProperty(pE, EP_xIsSelect) ); - if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){ - sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one " - "argument"); + if( !setupDistinctAggregate(pParse, pFunc, &keyEL) ){ + sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have at most one " + "non-constant argument"); pFunc->iDistinct = -1; }else{ - KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList); + KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, &keyEL); sqlite3VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0, (char*)pKeyInfo, P4_KEYINFO_HANDOFF); } } } @@ -3565,12 +3630,12 @@ nArg = 0; regAgg = 0; } if( pF->iDistinct>=0 ){ addrNext = sqlite3VdbeMakeLabel(v); - assert( nArg==1 ); - codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg); + //assert( nArg==1 ); + codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg + pF->iDistinctArg); } if( pF->pFunc->flags & SQLITE_FUNC_NEEDCOLL ){ CollSeq *pColl = 0; struct ExprList_item *pItem; int j; Index: src/sqliteInt.h =================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1530,10 +1530,11 @@ struct AggInfo_func { /* For each aggregate function */ Expr *pExpr; /* Expression encoding the function */ FuncDef *pFunc; /* The aggregate function implementation */ int iMem; /* Memory location that acts as accumulator */ int iDistinct; /* Ephemeral table used to enforce DISTINCT */ + int iDistinctArg; /* Index of argument that is DISTINCT */ } *aFunc; int nFunc; /* Number of entries in aFunc[] */ int nFuncAlloc; /* Number of slots allocated for aFunc[] */ }; -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users