SQLite Forum

Possible bug: Unexpected result using correlated aggregate subquery
Login

Possible bug: Unexpected result using correlated aggregate subquery

(1) By jake on 2021-08-31 08:12:41 [link] [source]

The following examples demonstrate a possible bug in some cases when using a correlated aggregate subquery:

SQLite version 3.37.0 2021-08-30 17:02:48
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE x AS SELECT 1 a;
sqlite> CREATE TABLE y AS SELECT 1 b UNION ALL SELECT 1;
sqlite>
sqlite> .mode box
sqlite>
sqlite> SELECT (SELECT group_concat(a) FROM y) unexpected, -- expected 1,1
   ...>        group_concat((SELECT a FROM y)) expected,
   ...>        (SELECT group_concat(b) FROM y) expected,
   ...>        (SELECT group_concat(a+b) FROM y) expected
   ...>   FROM x;
┌────────────┬──────────┬──────────┬──────────┐
│ unexpected │ expected │ expected │ expected │
├────────────┼──────────┼──────────┼──────────┤
│ 1          │ 1        │ 1,1      │ 2,2      │
└────────────┴──────────┴──────────┴──────────┘
sqlite>
sqlite> INSERT INTO x VALUES (1);
sqlite>
sqlite> SELECT (SELECT group_concat(a) FROM y) unexpected -- expected 2 rows
   ...>   FROM x;
┌────────────┐
│ unexpected │
├────────────┤
│ 1,1        │
└────────────┘
sqlite>
sqlite> SELECT (SELECT group_concat(b) FROM y) expected
   ...>   FROM x;
┌──────────┐
│ expected │
├──────────┤
│ 1,1      │
│ 1,1      │
└──────────┘
sqlite>
sqlite> SELECT (SELECT group_concat(a+b) FROM y) expected
   ...>   FROM x;
┌──────────┐
│ expected │
├──────────┤
│ 2,2      │
│ 2,2      │
└──────────┘

(2) By Richard Hipp (drh) on 2021-08-31 11:03:33 in reply to 1 [source]

SQLite computes the same answer as PostgreSQL here.

To be clear, I had to modify the code slightly to conform to PG syntax:

  • Add a LIMIT 1 to the subquery. The LIMIT 1 is implied by SQLite, but must be explicit in PG (also in MySQL and SQL Server).

  • Change group_concat() into string_agg().

I end up with this:

CREATE TABLE t1(a varchar);
CREATE TABLE t2(b varchar);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2),(3);
SELECT (SELECT string_agg(a,',') FROM t2 LIMIT 1) FROM t1;

To which PG gives an answer of just "1", same as SQLite.

(3) By J.M. Aranda (JMAranda) on 2021-08-31 11:57:44 in reply to 2 [link] [source]

¡ Que grande eres Maestro !

(4) By David Raymond (dvdraymond) on 2021-08-31 14:21:34 in reply to 1 [link] [source]

> 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;

(5) By David Raymond (dvdraymond) on 2021-08-31 14:54:35 in reply to 4 [link] [source]

I mean you have to admit that it does seem a little weird that an aggregate function in the sub-query makes the outer query an aggregate. That is a little unexpected.

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x (a);
sqlite> create table y (b);
sqlite> insert into x values (1), (2);
sqlite> insert into y values (7), (8), (9);
sqlite> select (select group_concat(y.b) from y) from x; --returns 2 rows
7,8,9
7,8,9
sqlite> select (select group_concat(5) from y) from x; --returns 2 rows
5,5,5
5,5,5
sqlite> select (select group_concat(x.a) from y) from x; --now only returns 1 row
1,2

sqlite> select (select count(*) from (select group_concat(y.b) from y)) from x;
1
1
sqlite> select (select count(*) from (select group_concat(5) from y)) from x;
1
1
sqlite> select (select count(*) from (select group_concat(x.a) from y)) from x;
3
sqlite>

(6) By jake on 2021-08-31 15:27:07 in reply to 5 [link] [source]

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;