SQLite Forum

Possible bug: Unexpected result using correlated aggregate subquery
Thank you Richard for validating the behaviour against PG. I'm satisfied that this relegate from "possible bug" to "not a bug" under the WWPGD philosophy. I have now also verified that this is the behaviour in MySQL.

Here is another variation which I find even less intuitive:

SELECT (SELECT group_concat(a) || '-' || group_concat(b) FROM y) z
  FROM x;
│   z   │
│ 1-1,1 │

But again seems consistent (at least with MySQL).


For context, the goal was to replicate a value as a comma separated list using the out-of-the-box CLI.

The failed attempt looked like this:

SELECT (SELECT group_concat(a) FROM generate_series(0,10))
  FROM x;

And some successful alternatives:

SELECT (SELECT group_concat(a+(value*0)) FROM generate_series(0,10))
  FROM x;

SELECT group_concat(a)
  FROM x
  JOIN generate_series(0,10)
 GROUP BY x.oid;