This is a SQL limitation. mysql> select sum(1); +--------+ | sum(1) | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)
mysql> select sum(sum(1)); ERROR 1111 (HY000): Invalid use of group function On Thu, Feb 10, 2022 at 12:39 PM Gavin Ray <ray.gavi...@gmail.com> wrote: > Went to test this query out and found that it can't be performed: > > SELECT > JSON_OBJECT( > KEY 'users' > VALUE JSON_ARRAYAGG( > JSON_OBJECT( > KEY 'name' VALUE "users"."name", > KEY 'todos' VALUE JSON_ARRAYAGG( > JSON_OBJECT( > KEY 'description' VALUE "todos"."description" > ) > ) > ) > ) > ) > FROM > "users" > LEFT OUTER JOIN > "todos" ON "users"."id" = "todos"."user_id"; > > Checking the source, seems this is a blanket policy, not a > datasource-specific thing. > From a functional perspective, it doesn't feel like it's much different > from JOINs > But I don't understand relational theory or DB functionality in the least, > so I'm not fit to judge. > > Just curious why Calcite doesn't allow this >
