Hi Scott.
>Are there other aggregate functions that take multiple arguments?
Absolutely. I've got a few in my code which deserialize table rows into
runtime objects. Fortunately, the DISTINCT filter makes no sense in that
use case, so I didn't bump into this issue myself.
If you're looking for a genuine hypothetical DISTINCT filter aggregate
case, consider an aggregate which computes statistics about matrices. In
some mode, such a aggregate could legitimately be invoked with a DISTINCT
filter. Obviously the nested query workaround fixes that case too.
Given the nested workaround is always possible, short circuit optimizing
the SELECT syntax makes sense but it would be nice to know if that's the
only rationale.
There is also the sensible expectation of group_concat() to have rows
supplied in controlled order, by nested SELECT if needed. The expectation
of a controlling nested SELECT is definitely already there.
Peter
On Tue, Jan 2, 2018 at 4:12 PM, Scott Robison <sc...@casaderobison.com>
wrote:
> On Tue, Jan 2, 2018 at 4:15 PM, petern <peter.nichvolo...@gmail.com>
> wrote:
> > Hi Tony. Good. Yes, simpler test case is always better when posting
> > possible bugs.
> >
> > Unfortunately, as Cezary points out, this error is by design (from
> > select.c):
> >
> > if( pFunc->iDistinct>=0 ){
> > Expr *pE = pFunc->pExpr;
> > assert( !ExprHasProperty(pE, EP_xIsSelect) );
> > if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
> > sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly
> one "
> > "argument");
> > pFunc->iDistinct = -1;
> > }else{
> >
> > It would be interesting to understand the harm avoided by disallowing
> > DISTINCT scope of all the aggregate parameters. Probably slower, but
> what
> > else? Usually, there is a comment in the source but not for this one.
>
> I'm not the guy who wrote it or decided how it should work, but it
> seems to me that "group_concat(distinct x,y)" would not work
> intuitively if y is a variable, though (somewhat surprisingly to me)
> it works:
>
> sqlite> create table t(x,y);
> sqlite> insert into t values (1,','),(1,'+'),(2,'.'),(2,'-');
> sqlite> select group_concat(x,y) from t;
> 1+1.2-2
> sqlite> select group_concat(distinct x,y) from t;
> Error: DISTINCT aggregates must have exactly one argument
> sqlite> select group_concat(x,y) from (select distinct x, y from t);
> 1+1.2-2
>
> Are there other aggregate functions that take multiple arguments? I
> can't find any examples online of aggregates that take more than one,
> which seems like the normal way something like this would be done.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
