Sorry, I realized this might be a time investment to try to debug/reproduce So I found a website that let me create the schema, rows, and query on various DB's
Here is an example on Oracle and MySQL that you can view/modify in your browser: ORACLE: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=2eed69c44fa63adf9830213163ba73d0 MYSQL: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f94c7957eae4f5ebe4c879c17fbe64ea On Wed, Feb 23, 2022 at 2:59 PM Gavin Ray <ray.gavi...@gmail.com> wrote: > I think this actually is a bug in Calcite's parser or it's interpretation > I tested on H2, Oracle, and MySQL, the below is valid on those DB's but > fails on Calcite's parser: > > ================= > H2/Oracle > ================= > SELECT > "houses"."id" AS "id", > "houses"."address" AS "address", > ( > SELECT > JSON_ARRAYAGG(JSON_OBJECT( > KEY 'id' VALUE "users"."id", > KEY 'name' VALUE "users"."name", > KEY 'todos' VALUE ( > SELECT > JSON_ARRAYAGG(JSON_OBJECT( > KEY 'id' VALUE "todos"."id", > KEY 'description' VALUE "todos"."description" > )) > FROM > "todos" > WHERE > "todos"."user_id" = "users"."id" > ) > )) > FROM > "users" > WHERE > "users"."house_id" = "houses"."id" > ) "users" > FROM > "houses" > GROUP BY > "houses"."id", > "houses"."address" > > ================= > MySQL > ================= > SELECT > `houses`.`id` AS `id`, > `houses`.`address` AS `address`, > ( > SELECT > JSON_ARRAYAGG(JSON_OBJECT( > 'id', `users`.`id`, > 'name', `users`.`name`, > 'todos', ( > SELECT > JSON_ARRAYAGG(JSON_OBJECT( > 'id', `todos`.`id`, > 'description', `todos`.`description` > )) > FROM > `todos` > WHERE > `todos`.`user_id` = `users`.`id` > ) > )) > FROM > `users` > WHERE > `users`.`house_id` = `houses`.`id` > ) `users` > FROM > `houses` > GROUP BY > `houses`.`id`, > `houses`.`address` > > On Wed, Feb 23, 2022 at 12:52 PM Gavin Ray <ray.gavi...@gmail.com> wrote: > >> That's a valid point, let me see check what some common dialects will >> accept for this sort of thing >> >> On Wed, Feb 23, 2022 at 12:36 PM Julian Hyde <jhyde.apa...@gmail.com> >> wrote: >> >>> It’s a parser error. That usually means that the user has made a >>> mistake. >>> >>> Try your SQL on another DB with JSON support before you declare this a >>> Calcite bug. >>> >>> Julian >>> >>> > On Feb 23, 2022, at 09:22, Gavin Ray <ray.gavi...@gmail.com> wrote: >>> > >>> > No dice still unfortunately =/ >>> > >>> > If it's any easier, I put a self-contained single class reproduction on >>> > Github: >>> > >>> https://github.com/GavinRay97/calcite-nested-json-subquery-bug/blob/main/src/test/java/com/example/AppTest.java >>> > >>> > Is it worth filing a JIRA ticket over you think? >>> > >>> > >>> >> On Wed, Feb 23, 2022 at 3:45 AM Julian Hyde <jhyde.apa...@gmail.com> >>> wrote: >>> >> >>> >> Try ‘value ((‘ in place of ‘value (‘. >>> >> >>> >> Julian >>> >> >>> >>>> On Feb 21, 2022, at 9:33 AM, Gavin Ray <ray.gavi...@gmail.com> >>> wrote: >>> >>> >>> >>> I hadn't thought about the fact that ORM's probably have to solve >>> this >>> >>> problem as well >>> >>> That is a great suggestion, I will try to investigate some of the >>> popular >>> >>> ORM codebases and see if there are any tricks they are using. >>> >>> >>> >>> I seem to maybe be getting a tiny bit closer by using subqueries like >>> >>> Julian suggested instead of operator calls >>> >>> But if I may ask what is probably a very stupid question: >>> >>> >>> >>> What might the error message >>> >>> "parse failed: Query expression encountered in illegal context >>> >>> (state=,code=0)" >>> >>> >>> >>> Mean in the below query? >>> >>> >>> >>> The reason why I am confused is because the query runs if I remove >>> the >>> >>> innermost subquery ("todos") >>> >>> But the innermost subquery is a direct copy-paste of the subquery >>> above >>> >> it, >>> >>> so I know it MUST be valid >>> >>> >>> >>> As usual, thank you so much for your help/guidance Stamatis. >>> >>> >>> >>> select >>> >>> "g0"."id" "id", >>> >>> "g0"."address" "address", >>> >>> ( >>> >>> select json_arrayagg(json_object( >>> >>> key 'id' value "g1"."id", >>> >>> key 'todos' value ( >>> >>> select json_arrayagg(json_object( >>> >>> key 'id' value "g2"."id", >>> >>> key 'description' value "g2"."description", >>> >>> )) >>> >>> from ( >>> >>> select * from "todos" >>> >>> where "g1"."id" = "user_id" >>> >>> order by "id" >>> >>> ) "g2" >>> >>> ) >>> >>> )) >>> >>> from ( >>> >>> select * from "users" >>> >>> where "g0"."id" = "house_id" >>> >>> order by "id" >>> >>> ) "g1" >>> >>> ) "users" >>> >>> from "houses" "g0" >>> >>> order by "g0"."id" >>> >>> >>> >>>> On Mon, Feb 21, 2022 at 8:07 AM Stamatis Zampetakis < >>> zabe...@gmail.com> >>> >>>> wrote: >>> >>>> >>> >>>> Hi Gavin, >>> >>>> >>> >>>> A few more comments in case they help to get you a bit further on >>> your >>> >>>> work. >>> >>>> >>> >>>> The need to return the result as a single object is a common >>> problem in >>> >>>> object relational mapping (ORM) frameworks/APIS (JPA, Datanucleus, >>> >>>> Hibernate, etc.). Apart from the suggestions so far maybe you could >>> look >>> >>>> into these frameworks as well for more inspiration. >>> >>>> >>> >>>> Moreover your approach of decomposing the query into individual >>> parts is >>> >>>> commonly known as the N+1 problem [1]. >>> >>>> >>> >>>> Lastly, keep in mind that you can introduce custom UDF, UDAF >>> functions >>> >> if >>> >>>> you need more flexibility on reconstructing the final result. >>> >>>> >>> >>>> Best, >>> >>>> Stamatis >>> >>>> >>> >>>> [1] >>> >>>> >>> >>>> >>> >> >>> https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping >>> >>>> >>> >>>>> On Sun, Feb 13, 2022 at 3:59 AM Gavin Ray <ray.gavi...@gmail.com> >>> >> wrote: >>> >>>>> >>> >>>>> Ah wait nevermind, got excited and spoke too soon. Looking at it >>> more >>> >>>>> closely, that data isn't correct. >>> >>>>> At least it's in somewhat the right shape, ha! >>> >>>>> >>> >>>>>> On Sat, Feb 12, 2022 at 9:57 PM Gavin Ray <ray.gavi...@gmail.com> >>> >> wrote: >>> >>>>> >>> >>>>>> After ~5 hours, I think I may have made some progress =) >>> >>>>>> >>> >>>>>> I have this, which currently works. The problem is that the nested >>> >>>>> columns >>> >>>>>> don't have names on them. >>> >>>>>> Since I need to return a nested "Map<String, Object>", I have to >>> >> figure >>> >>>>>> out how to convert this query into a form that gives column names. >>> >>>>>> >>> >>>>>> But this is still great progress I think! >>> >>>>>> >>> >>>>>> SELECT >>> >>>>>> "todos".*, >>> >>>>>> ARRAY( >>> >>>>>> SELECT >>> >>>>>> "users".*, >>> >>>>>> ARRAY( >>> >>>>>> SELECT >>> >>>>>> "todos".* >>> >>>>>> FROM >>> >>>>>> "todos" >>> >>>>>> ) AS "todos" >>> >>>>>> FROM >>> >>>>>> "users" >>> >>>>>> ) AS "users" >>> >>>>>> FROM >>> >>>>>> "todos" >>> >>>>>> WHERE >>> >>>>>> "user_id" IN ( >>> >>>>>> SELECT >>> >>>>>> "user_id" >>> >>>>>> FROM >>> >>>>>> "users" >>> >>>>>> WHERE >>> >>>>>> "house_id" IN ( >>> >>>>>> SELECT >>> >>>>>> "id" >>> >>>>>> FROM >>> >>>>>> "houses" >>> >>>>>> ) >>> >>>>>> ); >>> >>>>>> >>> >>>>>> >>> >>>>>> >>> >>>>>> >>> >>>>> >>> >>>> >>> >> >>> +----+---------+------------------------+------------------------------------------------------------------------------+ >>> >>>>>> | id | user_id | description | >>> >>>>>> | >>> >>>>>> >>> >>>>>> >>> >>>>> >>> >>>> >>> >> >>> +----+---------+------------------------+------------------------------------------------------------------------------+ >>> >>>>>> | 1 | 1 | Take out the trash | [{1, John, 1, [{1, 1, >>> Take >>> >>>> out >>> >>>>>> the trash}, {2, 1, Watch my favorite show}, { | >>> >>>>>> | 2 | 1 | Watch my favorite show | [{1, John, 1, [{1, 1, >>> Take >>> >>>> out >>> >>>>>> the trash}, {2, 1, Watch my favorite show}, { | >>> >>>>>> | 3 | 1 | Charge my phone | [{1, John, 1, [{1, 1, >>> Take >>> >>>> out >>> >>>>>> the trash}, {2, 1, Watch my favorite show}, { | >>> >>>>>> | 4 | 2 | Cook dinner | [{1, John, 1, [{1, 1, >>> Take >>> >>>> out >>> >>>>>> the trash}, {2, 1, Watch my favorite show}, { | >>> >>>>>> | 5 | 2 | Read a book | [{1, John, 1, [{1, 1, >>> Take >>> >>>> out >>> >>>>>> the trash}, {2, 1, Watch my favorite show}, { | >>> >>>>>> | 6 | 2 | Organize office | [{1, John, 1, [{1, 1, >>> Take >>> >>>> out >>> >>>>>> the trash}, {2, 1, Watch my favorite show}, { | >>> >>>>>> | 7 | 3 | Walk the dog | [{1, John, 1, [{1, 1, >>> Take >>> >>>> out >>> >>>>>> the trash}, {2, 1, Watch my favorite show}, { | >>> >>>>>> | 8 | 3 | Feed the cat | [{1, John, 1, [{1, 1, >>> Take >>> >>>> out >>> >>>>>> the trash}, {2, 1, Watch my favorite show}, { | >>> >>>>>> >>> >>>>>> >>> >>>>> >>> >>>> >>> >> >>> +----+---------+------------------------+------------------------------------------------------------------------------+ >>> >>>>>> >>> >>>>>> On Sat, Feb 12, 2022 at 4:13 PM Gavin Ray <ray.gavi...@gmail.com> >>> >>>> wrote: >>> >>>>>> >>> >>>>>>> Nevermind, this is a standard term not something >>> Calcite-specific it >>> >>>>>>> seems! >>> >>>>>>> >>> >>>>>>> https://en.wikipedia.org/wiki/Correlated_subquery >>> >>>>>>> >>> >>>>>>> On Sat, Feb 12, 2022 at 3:46 PM Gavin Ray <ray.gavi...@gmail.com >>> > >>> >>>>> wrote: >>> >>>>>>> >>> >>>>>>>> Forgive my ignorance/lack of experience >>> >>>>>>>> >>> >>>>>>>> I am somewhat familiar with the ARRAY() function, but not sure I >>> >> know >>> >>>>>>>> the term "correlated" >>> >>>>>>>> Searching the Calcite codebase for uses of "correlated" + >>> "query", I >>> >>>>>>>> found: >>> >>>>>>>> >>> >>>>>>>> >>> >>>>>>>> >>> >>>>> >>> >>>> >>> >> >>> https://github.com/apache/calcite/blob/1d4f1b394bfdba03c5538017e12ab2431b137ca9/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java#L1603-L1612 >>> >>>>>>>> >>> >>>>>>>> @Test void testCorrelatedSubQueryInJoin() { >>> >>>>>>>> final String sql = "select *\n" >>> >>>>>>>> + "from emp as e\n" >>> >>>>>>>> + "join dept as d using (deptno)\n" >>> >>>>>>>> + "where d.name = (\n" >>> >>>>>>>> + " select max(name)\n" >>> >>>>>>>> + " from dept as d2\n" >>> >>>>>>>> + " where d2.deptno = d.deptno)"; >>> >>>>>>>> sql(sql).withExpand(false).ok(); >>> >>>>>>>> } >>> >>>>>>>> >>> >>>>>>>> But I also see this, which says it is "uncorrelated" but seems >>> very >>> >>>>>>>> similar? >>> >>>>>>>> >>> >>>>>>>> @Test void testInUncorrelatedSubQuery() { >>> >>>>>>>> final String sql = "select empno from emp where deptno in" >>> >>>>>>>> + " (select deptno from dept)"; >>> >>>>>>>> sql(sql).ok(); >>> >>>>>>>> } >>> >>>>>>>> >>> >>>>>>>> I wouldn't blame you for not answering such a basic question -- >>> but >>> >>>>> what >>> >>>>>>>> exactly does "correlation" mean here? >>> >>>>>>>> >>> >>>>>>>> Thanks, as usual Julian >>> >>>>>>>> >>> >>>>>>>> >>> >>>>>>>> On Sat, Feb 12, 2022 at 3:08 PM Julian Hyde < >>> jhyde.apa...@gmail.com >>> >>> >>> >>>>>>>> wrote: >>> >>>>>>>> >>> >>>>>>>>> Correlated ARRAY sub-query? >>> >>>>>>>>> >>> >>>>>>>>>> On Feb 12, 2022, at 10:40 AM, Gavin Ray < >>> ray.gavi...@gmail.com> >>> >>>>>>>>> wrote: >>> >>>>>>>>>> >>> >>>>>>>>>> Apologies for the delay in replying >>> >>>>>>>>>> >>> >>>>>>>>>> This makes things clear and seems obvious now that you point >>> it >>> >>>> out. >>> >>>>>>>>>> Thank you, Justin and Julian =) >>> >>>>>>>>>> >>> >>>>>>>>>> Let me ask another question (if I may) that I am struggling to >>> >>>>> phrase >>> >>>>>>>>>> easily. >>> >>>>>>>>>> >>> >>>>>>>>>> So with GraphQL, you might have a query like: >>> >>>>>>>>>> - "Get houses" >>> >>>>>>>>>> - "For each house get the user that lives in the house >>> >>>>>>>>>> - "And for each user get their list of todos" >>> >>>>>>>>>> >>> >>>>>>>>>> The result has to come back such that it's a single object for >>> >>>> each >>> >>>>>>>>> row >>> >>>>>>>>>> ============== >>> >>>>>>>>>> { >>> >>>>>>>>>> houses: [{ >>> >>>>>>>>>> address: "123 Main Street", >>> >>>>>>>>>> users: [{ >>> >>>>>>>>>> name: "Joe", >>> >>>>>>>>>> todos: [{ >>> >>>>>>>>>> description: "Take out trash" >>> >>>>>>>>>> }] >>> >>>>>>>>>> }] >>> >>>>>>>>>> } >>> >>>>>>>>>> >>> >>>>>>>>>> From a SQL perspective, the logical equivalent would be >>> something >>> >>>>>>>>> like: >>> >>>>>>>>>> ============== >>> >>>>>>>>>> SELECT >>> >>>>>>>>>> house.address, >>> >>>>>>>>>> (somehow nest users + double-nest todos under user) >>> >>>>>>>>>> FROM >>> >>>>>>>>>> house >>> >>>>>>>>>> JOIN >>> >>>>>>>>>> user ON user.house_id = house.id >>> >>>>>>>>>> todos ON todos.user_id = user.id >>> >>>>>>>>>> WHERE >>> >>>>>>>>>> house.id = 1 >>> >>>>>>>>>> >>> >>>>>>>>>> I'm not familiar enough with SQL to have figured out a way to >>> make >>> >>>>>>>>> this >>> >>>>>>>>>> kind of >>> >>>>>>>>>> query using operators that are supported across most of the >>> DB's >>> >>>>>>>>> Calcite has >>> >>>>>>>>>> adapters for. >>> >>>>>>>>>> >>> >>>>>>>>>> Currently what I have done instead, on a tip from >>> Gopalakrishna >>> >>>>> Holla >>> >>>>>>>>> from >>> >>>>>>>>>> LinkedIn Coral team who has built GraphQL-on-Calcite, was to >>> break >>> >>>>> up >>> >>>>>>>>> the >>> >>>>>>>>>> query >>> >>>>>>>>>> into individual parts and then do the join in-memory: >>> >>>>>>>>>> >>> >>>>>>>>>> SELECT ... FROM users; >>> >>>>>>>>>> SELECT ... FROM todos WHERE todos.user_id IN (ResultSet from >>> prev >>> >>>>>>>>> response); >>> >>>>>>>>>> >>> >>>>>>>>>> However, the way I am doing this seems like it's probably very >>> >>>>>>>>> inefficient. >>> >>>>>>>>>> Because I do a series of nested loops to add the key to each >>> >>>> object >>> >>>>>>>>> in the >>> >>>>>>>>>> parent ResultSet row: >>> >>>>>>>>>> >>> >>>>>>>>>> >>> >>>>>>>>> >>> >>>>> >>> >>>> >>> >> >>> https://github.com/GavinRay97/GraphQLCalcite/blob/648e0ac4f6810a3c360d13a03e6597c33406de4b/src/main/kotlin/TableDataFetcher.kt#L135-L153 >>> >>>>>>>>>> >>> >>>>>>>>>> Is there some better way of doing this? >>> >>>>>>>>>> I would be eternally grateful for any advice. >>> >>>>>>>>>> >>> >>>>>>>>>> >>> >>>>>>>>>> On Thu, Feb 10, 2022 at 3:27 PM Julian Hyde < >>> >>>> jhyde.apa...@gmail.com >>> >>>>>> >>> >>>>>>>>> wrote: >>> >>>>>>>>>> >>> >>>>>>>>>>> Yes, if you want to do multiple layers of aggregation, use >>> CTEs >>> >>>>>>>>> (WITH) or >>> >>>>>>>>>>> nested sub-queries. For example, the following is I believe >>> valid >>> >>>>>>>>> standard >>> >>>>>>>>>>> SQL, and actually computes something useful: >>> >>>>>>>>>>> >>> >>>>>>>>>>> WITH q1 AS >>> >>>>>>>>>>> (SELECT deptno, job, AVG(sal) AS avg_sal >>> >>>>>>>>>>> FROM emp >>> >>>>>>>>>>> GROUP BY deptno, job) >>> >>>>>>>>>>> WITH q2 AS >>> >>>>>>>>>>> (SELECT deptno, AVG(avg_sal) AS avg_avg_sal >>> >>>>>>>>>>> FROM q1 >>> >>>>>>>>>>> GROUP BY deptno) >>> >>>>>>>>>>> SELECT AVG(avg_avg_sal) >>> >>>>>>>>>>> FROM q2 >>> >>>>>>>>>>> GROUP BY () >>> >>>>>>>>>>> >>> >>>>>>>>>>> (You can omit the “GROUP BY ()” line, but I think it makes >>> things >>> >>>>>>>>> clearer.) >>> >>>>>>>>>>> >>> >>>>>>>>>>> Julian >>> >>>>>>>>>>> >>> >>>>>>>>>>> >>> >>>>>>>>>>> >>> >>>>>>>>>>>> On Feb 10, 2022, at 12:17 PM, Justin Swanhart < >>> >>>>> greenl...@gmail.com> >>> >>>>>>>>>>> wrote: >>> >>>>>>>>>>>> >>> >>>>>>>>>>>> I wish you could unsend emails :) Answering my own >>> question, >>> >>>> no, >>> >>>>>>>>> because >>> >>>>>>>>>>>> that would return three rows with the average :D >>> >>>>>>>>>>>> >>> >>>>>>>>>>>> On Thu, Feb 10, 2022 at 3:16 PM Justin Swanhart < >>> >>>>>>>>> greenl...@gmail.com> >>> >>>>>>>>>>> wrote: >>> >>>>>>>>>>>> >>> >>>>>>>>>>>>> Just out of curiosity, is the second level aggregation >>> using >>> >>>> AVG >>> >>>>>>>>> in a >>> >>>>>>>>>>>>> window context? It the frame is the whole table and it >>> >>>>> aggregates >>> >>>>>>>>> over >>> >>>>>>>>>>> it? >>> >>>>>>>>>>>>> >>> >>>>>>>>>>>>> On Thu, Feb 10, 2022 at 3:12 PM Justin Swanhart < >>> >>>>>>>>> greenl...@gmail.com> >>> >>>>>>>>>>>>> wrote: >>> >>>>>>>>>>>>> >>> >>>>>>>>>>>>>> That is really neat about Oracle. >>> >>>>>>>>>>>>>> >>> >>>>>>>>>>>>>> The alternative in general is to use a subquery: >>> >>>>>>>>>>>>>> SELECT avg(avg(sal)) FROM emp GROUP BY deptno; >>> >>>>>>>>>>>>>> becomes >>> >>>>>>>>>>>>>> select avg(the_avg) >>> >>>>>>>>>>>>>> from (select avg(sal) from emp group b deptno) an_alias; >>> >>>>>>>>>>>>>> >>> >>>>>>>>>>>>>> or >>> >>>>>>>>>>>>>> >>> >>>>>>>>>>>>>> with the_cte as (select avg(sal) x from emp group by >>> deptno) >>> >>>>>>>>>>>>>> select avg(x) from the_cte; >>> >>>>>>>>>>>>>> >>> >>>>>>>>>>>>>> On Thu, Feb 10, 2022 at 3:03 PM Julian Hyde < >>> >>>>>>>>> jhyde.apa...@gmail.com> >>> >>>>>>>>>>>>>> wrote: >>> >>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> Some databases, e.g. Oracle, allow TWO levels of nesting: >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> SELECT avg(sal) FROM emp GROUP BY deptno; >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> AVG(SAL) >>> >>>>>>>>>>>>>>> ======== >>> >>>>>>>>>>>>>>> 1,566.67 >>> >>>>>>>>>>>>>>> 2,175.00 >>> >>>>>>>>>>>>>>> 2,916.65 >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> SELECT avg(avg(sal)) FROM emp GROUP BY deptno; >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> AVG(SUM(SAL)) >>> >>>>>>>>>>>>>>> ============= >>> >>>>>>>>>>>>>>> 9,675 >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> The first level aggregates by department (returning 3 >>> >>>> records), >>> >>>>>>>>> and >>> >>>>>>>>>>> the >>> >>>>>>>>>>>>>>> second level computes the grand total (returning 1 >>> record). >>> >>>> But >>> >>>>>>>>> that >>> >>>>>>>>>>> is an >>> >>>>>>>>>>>>>>> exceptional case. >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> Generally, any expression in the SELECT or HAVING clause >>> of >>> >>>> an >>> >>>>>>>>>>> aggregate >>> >>>>>>>>>>>>>>> query is either ‘before’ or ‘after’ aggregation. Consider >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> SELECT t.x + 1 AS a, 2 + SUM(t.y + 3) AS b >>> >>>>>>>>>>>>>>> FROM t >>> >>>>>>>>>>>>>>> GROUP BY t.x >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> The expressions “t.y” and “t.y + 3” occur before >>> aggregation; >>> >>>>>>>>> “t.x”, >>> >>>>>>>>>>>>>>> “t.x + 1”, “SUM(t.y + 3)” and “2 + SUM(t.y + 3)” occur >>> after >>> >>>>>>>>>>> aggregation. >>> >>>>>>>>>>>>>>> SQL semantics rely heavily on this stratification. >>> Allowing >>> >>>> an >>> >>>>>>>>> extra >>> >>>>>>>>>>> level >>> >>>>>>>>>>>>>>> of aggregation would mess it all up. >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> Julian >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>>> On Feb 10, 2022, at 9:45 AM, Justin Swanhart < >>> >>>>>>>>> greenl...@gmail.com> >>> >>>>>>>>>>>>>>> wrote: >>> >>>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>>> 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 >>> >>>>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>>>>>> >>> >>>>>>>>>>> >>> >>>>>>>>>>> >>> >>>>>>>>> >>> >>>>>>>>> >>> >>>>> >>> >>>> >>> >> >>> >>
