SQLite Forum

Possible bug: Unexpected result using correlated aggregate subquery
Login
> sqlite> CREATE TABLE x AS SELECT 1 a;
> sqlite> CREATE TABLE y AS SELECT 1 b UNION ALL SELECT 1;

Note that the table "y" has no column "a", only column "b"

sqlite> SELECT (SELECT group_concat(a) FROM y) unexpected, -- expected 1,1
...
   ...> FROM x;

The subquery (SELECT group_concat(a) FROM y) here returns 2 rows of 1 column, where the value of that one column is '1' for both rows, because "a" is reffering to the column in x, and not to anything in y. Since this subquery is being used to provide a single column value, only the first of those rows is being used, and the rest discarded.

To get your expected '1,1' you would either have to make it something like
sqlite> select (select group_concat(a) from (select a from y)), ... from x;

if you did indeed mean column "a", or it you mentioned the wrong column and really wanted "b", then

sqlite> select (select group_concat(b) from y), ... from x;