Re: [sqlite] GROUP_CONCAT with DISTINCT bug
Hello, On 2018-01-03 01:44, Cezary H. Noweta wrote: MySQL has a separator specified by a distinct clause. I'm sorry -- I meant ``distinct'' == ``separate/different'' (a clause named ``SEPARATOR''). Not to be confused with ``DISTINCT'' clause in SQLite's ``group_concat(DISTINCT...)''. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
Scott. I almost forgot about this example. Consider group_concat's
orthogonal function group_replace.
I adapted this from SQLite replace and group_concat. The author disclaims
all rights to the following code:
---
struct StrRepl {
const unsigned char* zStr;
u32 nStr;
u8 replError; /* STRREPL_NOMEM or STRREPL_TOOBIG */
};
typedef struct StrRepl StrRepl;
#define STRREPL_NOMEM 1
#define STRREPL_TOOBIG 2
/*
** Aggregate group_replace(A,B,C) result string is derived from A by
replacing
** every exact match occurrence of B with C. A is presumed constant over
the group
** while B and C may vary at each step. Collating sequences are not used.
*/
static void groupReplaceStep(
sqlite3_context *context,
int argc,
sqlite3_value **argv
) {
const unsigned char *zStr; /* The input string A */
const unsigned char *zPattern; /* The pattern string B */
const unsigned char *zRepl; /* The replacement string C */
unsigned char *zOut; /* The output */
int nStr; /* Size of zStr */
int nPattern; /* Size of zPattern */
int nRepl; /* Size of zRep */
i64 nOut; /* Maximum size of zOut */
int loopLimit; /* Last zStr[] that might match zPattern[] */
int i, j; /* Loop counters */
assert(argc == 3);
UNUSED_PARAMETER(argc);
zStr = sqlite3_value_text(argv[0]);
if (zStr == 0) return;
nStr = sqlite3_value_bytes(argv[0]);
assert(zStr == sqlite3_value_text(argv[0])); /* No encoding change */
zPattern = sqlite3_value_text(argv[1]);
if (zPattern == 0) {
assert(sqlite3_value_type(argv[1]) == SQLITE_NULL
|| sqlite3_context_db_handle(context)->mallocFailed);
return;
}
if (zPattern[0] == 0) {
assert(sqlite3_value_type(argv[1]) != SQLITE_NULL);
sqlite3_result_value(context, argv[0]);
return;
}
nPattern = sqlite3_value_bytes(argv[1]);
assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change */
zRepl = sqlite3_value_text(argv[2]);
if (zRepl == 0) return;
nRepl = sqlite3_value_bytes(argv[2]);
assert(zRepl == sqlite3_value_text(argv[2]));
StrRepl* pStrRepl = (StrRepl*) sqlite3_aggregate_context(context, sizeof
(StrRepl));
if (pStrRepl) {
if (pStrRepl->replError) return;
if (!pStrRepl->zStr) {
if (sqlite3_value_type(argv[0]) == SQLITE_NULL
|| sqlite3_value_type(argv[1]) == SQLITE_NULL) return;
pStrRepl->nStr = sqlite3_value_bytes(argv[0]);
pStrRepl->zStr = contextMalloc(context,pStrRepl->nStr);
memcpy((void*)pStrRepl->zStr,sqlite3_value_text(argv[0]),pStrRepl->nStr);
/* No encoding change */
}
zPattern = sqlite3_value_text(argv[1]);
nPattern = sqlite3_value_bytes(argv[1]);
assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change
*/
if (0 == pStrRepl->nStr || 0 == nPattern) return;
zRepl = sqlite3_value_text(argv[2]);
nRepl = sqlite3_value_bytes(argv[2]);
if (0 == zRepl) return;
nOut = pStrRepl->nStr + 1;
assert(nOut < SQLITE_MAX_LENGTH);
zOut = contextMalloc(context, (i64) nOut);
if (0 == zOut) return;
loopLimit = pStrRepl->nStr - nPattern;
for (i = j = 0; i <= loopLimit; i++) {
if (pStrRepl->zStr[i] != zPattern[0] || memcmp(&(pStrRepl->zStr)[i],
zPattern, nPattern)) {
zOut[j++] = pStrRepl->zStr[i];
} else {
u8 *zOld;
sqlite3 *db = sqlite3_context_db_handle(context);
nOut += nRepl - nPattern;
testcase(nOut - 1 == db->aLimit[SQLITE_LIMIT_LENGTH]);
testcase(nOut - 2 == db->aLimit[SQLITE_LIMIT_LENGTH]);
if (nOut - 1 > db->aLimit[SQLITE_LIMIT_LENGTH]) {
pStrRepl->replError = STRREPL_TOOBIG;
sqlite3_free(zOut);
return;
}
zOld = zOut;
zOut = sqlite3_realloc64(zOut, (int) nOut);
if (zOut == 0) {
pStrRepl->replError = STRREPL_NOMEM;
sqlite3_free(zOld);
return;
}
memcpy(&zOut[j], zRepl, nRepl);
j += nRepl;
i += nPattern - 1;
}
}
assert(j + pStrRepl->nStr - i + 1 == nOut);
memcpy(&zOut[j], &pStrRepl->zStr[i], pStrRepl->nStr - i);
j += pStrRepl->nStr - i;
assert(j <= nOut);
zOut[j] = 0;
void* pFree = (void*)pStrRepl->zStr;
pStrRepl->zStr = zOut;
pStrRepl->nStr = nOut;
sqlite3_free(pFree);
}
}
static void groupReplaceFinalize(sqlite3_context *context) {
const char **pzVal;
StrRepl* pStrRepl = sqlite3_aggregate_context(context, 0);
if (pStrRepl) {
if (pStrRepl->replError == STRREPL_TOOBIG) {
sqlite3_result_error_toobig(context);
} else if (pStrRepl->replError == STRREPL_NOMEM) {
sqlite3_result_error_nomem(context);
} else {
sqlite3_result_text(context, pStrRepl->zStr, -1, sqlite3_free);
}
}
}
On Tue, Jan 2, 2018 at 4:57 PM, Scott Robison
wrote:
> On Tue, Jan 2, 2018 at 5:46 PM, petern
> wrote:
> > Hi Scott.
> >
> >>Are there other aggregate functions that take multiple arguments?
> >
> > Absolutely. I've got a few in my code which deseriali
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
On Tue, Jan 2, 2018 at 5:46 PM, petern wrote: > 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. Thanks for the info. In doing some quick searches, I found multiple descriptions of "generic SQL" aggregates that gave a syntax of "aggregate([DISTINCT|ALL] expression)", which led me to assume that maybe the standard only allows that syntax with a single expression, not an expression list. I say maybe because the examples I found were clearly not the standard. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
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
wrote:
> On Tue, Jan 2, 2018 at 4:15 PM, petern
> 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
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
Hello,
On 2018-01-03 00:02, Tony Papadimitriou wrote:
MySQL does not seem to have a problem with it.
MySQL has a separator specified by a distinct clause. In SQLite it is
specified by a second expression, which, in a canonical and intuitive
point of view, is a constant string. However it can vary from record to
record and can be used in many fancy and/or useful ways:
sqlite> WITH RECURSIVE numbers(n,p) AS (VALUES(random()/10,0)
UNION ALL SELECT random()/10,n FROM numbers LIMIT 10) SELECT
group_concat(n,substr('<=>',3*(n
8566622672>2879787174<=7821300466>-9054357747<=3166199899>-4120363042<=8151009951>-7018229290<=4454709919<=8212308797
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
On Tue, Jan 2, 2018 at 4:15 PM, petern 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
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
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.
Peter
On Tue, Jan 2, 2018 at 2:54 PM, Tony Papadimitriou wrote:
> Even simpler, then...
> select group_concat(distinct 1,',');
>
> -Original Message- From: petern
> Simpler one line test case also parses incorrectly:
>
> WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c) FROM t;
> "group_concat(DISTINCT c)"
> 1
>
> WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c,',') FROM t;
> Error: DISTINCT aggregates must have exactly one argument
>
>
> On Tue, Jan 2, 2018 at 12:36 PM, Tony Papadimitriou wrote:
>
> create table t(s);
>> insert into t values ('A'),('A'),('B');
>>
>> select group_concat(s,', ') from t group by null; -- OK
>> select group_concat(distinct s) from t group by null; -- OK
>> select group_concat(distinct s,', ') from t group by null; -- ERROR
>>
>> -- The moment the optional delimiter is given along with DISTINCT you get
>> this error:
>> -- Error: near line 6: DISTINCT aggregates must have exactly one argument
>>
>> -- Thank you.
>> ___
>> 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
> ___
> 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
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
MySQL does not seem to have a problem with it.
-Original Message-
From: Scott Robison
On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou wrote:
create table t(s);
insert into t values ('A'),('A'),('B');
select group_concat(s,', ') from t group by null; -- OK
select group_concat(distinct s) from t group by null; -- OK
select group_concat(distinct s,', ') from t group by null; -- ERROR
-- The moment the optional delimiter is given along with DISTINCT you get
this error:
-- Error: near line 6: DISTINCT aggregates must have exactly one argument
A limitation of the SQL syntax. I suspect this might work for your
example use case: select group_concat(s, ',') from (select distinct s
as s from t) group by null;
Tested and confirmed here.
___
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
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
Even simpler, then...
select group_concat(distinct 1,',');
-Original Message-
From: petern
Simpler one line test case also parses incorrectly:
WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c) FROM t;
"group_concat(DISTINCT c)"
1
WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c,',') FROM t;
Error: DISTINCT aggregates must have exactly one argument
On Tue, Jan 2, 2018 at 12:36 PM, Tony Papadimitriou wrote:
create table t(s);
insert into t values ('A'),('A'),('B');
select group_concat(s,', ') from t group by null; -- OK
select group_concat(distinct s) from t group by null; -- OK
select group_concat(distinct s,', ') from t group by null; -- ERROR
-- The moment the optional delimiter is given along with DISTINCT you get
this error:
-- Error: near line 6: DISTINCT aggregates must have exactly one argument
-- Thank you.
___
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
Hello,
On 2018-01-02 22:39, Scott Robison wrote:
On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou wrote:
create table t(s);
insert into t values ('A'),('A'),('B');
select group_concat(s,', ') from t group by null; -- OK
select group_concat(distinct s) from t group by null; -- OK
select group_concat(distinct s,', ') from t group by null; -- ERROR
-- The moment the optional delimiter is given along with DISTINCT you get this
error:
-- Error: near line 6: DISTINCT aggregates must have exactly one argument
A limitation of the SQL syntax.
Quite justly, not all the time params are obvious -- group functions are
not group functions with 1 column and n one-bind-time parameters:
sqlite> CREATE TABLE a(a,sep);
sqlite> INSERT INTO a VALUES('Hello', ','),('world', ';'),('shmorld', '
AND ');
sqlite> SELECT group_concat(a,sep) FROM a GROUP BY NULL;
Hello;world AND shmorld
Hopefully, SELECT FROM SELECT DISTINCT mentioned previously by Scott,
resolves the problem in an easy & painless way.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
Simpler one line test case also parses incorrectly:
WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c) FROM t;
"group_concat(DISTINCT c)"
1
WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c,',') FROM t;
Error: DISTINCT aggregates must have exactly one argument
On Tue, Jan 2, 2018 at 12:36 PM, Tony Papadimitriou wrote:
> create table t(s);
> insert into t values ('A'),('A'),('B');
>
> select group_concat(s,', ') from t group by null; -- OK
> select group_concat(distinct s) from t group by null; -- OK
> select group_concat(distinct s,', ') from t group by null; -- ERROR
>
> -- The moment the optional delimiter is given along with DISTINCT you get
> this error:
> -- Error: near line 6: DISTINCT aggregates must have exactly one argument
>
> -- Thank you.
> ___
> 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
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou wrote:
> create table t(s);
> insert into t values ('A'),('A'),('B');
>
> select group_concat(s,', ') from t group by null; -- OK
> select group_concat(distinct s) from t group by null; -- OK
> select group_concat(distinct s,', ') from t group by null; -- ERROR
>
> -- The moment the optional delimiter is given along with DISTINCT you get
> this error:
> -- Error: near line 6: DISTINCT aggregates must have exactly one argument
A limitation of the SQL syntax. I suspect this might work for your
example use case: select group_concat(s, ',') from (select distinct s
as s from t) group by null;
Tested and confirmed here.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] GROUP_CONCAT with DISTINCT bug
create table t(s);
insert into t values ('A'),('A'),('B');
select group_concat(s,', ') from t group by null; -- OK
select group_concat(distinct s) from t group by null; -- OK
select group_concat(distinct s,', ') from t group by null; -- ERROR
-- The moment the optional delimiter is given along with DISTINCT you get this
error:
-- Error: near line 6: DISTINCT aggregates must have exactly one argument
-- Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
