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
