Re: 回复：cannot be cast to class org.apache.calcite.runtime.FlatLists$ComparableList error
Hello,
per ReflectiveSchema javadoc [1]:
"Implementation of {@link org.apache.calcite.schema.Schema} that exposes
the public fields and methods in a Java object."
[1]
https://github.com/apache/calcite/blob/812e3e98eae518cf85cd1b6b7f055fb96784a423/core/src/main/java/org/apache/calcite/adapter/java/ReflectiveSchema.java#L71
On Sat, Feb 12, 2022 at 1:54 AM xiaobo wrote:
> Sorry for the HTML escape characters, I rewrite the problem as following:
>
> SQL select count(a.PA01AI01) from p.PRH_PA01 as a
> failed with :
> Caused by: java.sql.SQLException: Error while executing SQL "select
> count(a.PA01AI01) from p.PRH_PA01 as a
> ": From line 1, column 16 to line 1, column 23: Column 'PA01AI01' not
> found in table 'a'
>
> to resolve the problem we must make the table fields of the target Java
> class as public, is this a rule for ReflectiveSchema?
Re: calcite multi-threading problem
we checked the janino release note page
http://janino-compiler.github.io/janino/changelog.html
Fixed issue #141: Unable to find org.codehaus.commons.compiler.properties in
java 11: ICompilerFactories were loaded through the current thread's "context
class loader", which is a bad choice in some environments. Thus, the methods
"getDefaultCompilerFactory()", "getAllCompilerFactories()" and
"getCompilerFactory(className)" were duplicated and augmented with a
"classLoader" parameter.
what does this mean? the getDefaultCompilerFactory method with a classloader
parameter was removed intentionally?
but it seems calcite still needs it , so what version of janino should we use
for the latest calcite-core?
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 12:01 PM
To: "dev";
Subject: Re:calcite multi-threading problem
In our use case, the Java class which opens calcite connection is contained in
a external Java jar file, which is loaded dynamicly through classloaders other
than the default systemloader like this:
try {
ClassLoader loader = URLClassLoader.newInstance(
new URL[] { new URL("jar:file:" + path + "!/") },
getClass().getClassLoader());
Class clazz = Class.forName(classPath, true, loader);
we mention this because the error message shows a classloader parameter.
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 11:56 AM
To: "dev";
Subject: Re: calcite multi-threading problem
And if we remove calcite from our project, janino is not in "resolved
depencies" library list of pom.xml,
So we think it not the janino conflic problem.
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 9:49 AM
To: "dev";
Subject: Re: calcite multi-threading problem
can you share the solution for the problem, we are using the following
libraries, and it seems janino 3.1.6 is only dependent by calcite-core.
UTF-8
UTF-8
9
9
9
2.17.1
7.64.0.Final
org.apache.calcite
calcite-core
1.29.0
org.apache.calcite.avatica
avatica-core
1.20.0
org.drools
drools-core
${drools.version}
org.drools
drools-compiler
${drools.version}
org.drools
drools-decisiontables
${drools.version}
-- Original --
From: "xiong duan";;
Send time: Saturday, Feb 12, 2022 8:43 AM
To: "dev";
Subject: Re: calcite multi-threading problem
Yes, It is a dependency issue. I have met the same problem. +1 for Dmitry.
Dmitry Sysolyatin 于2022年2月11日周五 21:18写道：
> Actually, I had the same problem with spark. Spark 3.2.1 uses the old
> version of janino, calcite 1.29 uses the new version of janino and they are
> not compatible.
> If I downgrade janino version to version which spark uses I got an error
> not method found exception
> If I upgrade the janino version to the version which calcite uses I get the
> same error but with another method.
>
> As a result, I built a separate calcite jar package where I shaded janino
> dependency
>
>
>
> On Fri, Feb 11, 2022 at 2:39 PM stanilovsky evgeny <
> estanilovs...@gridgain.com> wrote:
>
> > for example on a current calcite branch:
> > grep janino gradle.properties
> > janino.version=3.1.6
> >
> >
> > > yes, we use drools in the same project, and drools uses janino
> > > too, is there a version list of janino which calcite supports?
> > >
> > >
> > >
> > >
> > > ---Original---
> > > From: "Dmitry Sysolyatin" > > Date: Fri, Feb 11, 2022 18:30 PM
> > > To: "dev" > > Subject: Re: calcite multi-threading problem
> > >
> > >
> > > Hi!
> > >
> >
> It looks like a dependency issue, not like a multi-threading issue. Most
> > >
> >
> likely you have some dependency that use another version of janino but
> > > calcite requires another one
> > >
> > >
> >
> On Fri, Feb 11, 2022 at 12:18 PM xiaobo > > wrote:
> > >
> > > > Hi,
> > > >
> > > >
> > >
> >
> > we open a calcite connection with ReflectiveSchema in one thread, and
> > >
> >
> > execute sqls in other threads against the connection, but failed with
> > > > this error
> > > >
> > > >
> > > >
> > > >
> > > > com.google.common.util.concurrent.ExecutionError:
> > > > java.lang.NoSuchMethodError:
> > > >
> > >
> >
> > org.codehaus.commons.compiler.CompilerFactoryFactory.getDefaultCompilerFactory(Ljava/lang/ClassLoader;)
> > > >
> > > > Lorg/codehaus/commons/compiler/ICompilerFactory;
> > > >
> > > >
> > >
> >
> > do we miss anything regarding to multi-threading with calcite?
> >
>
Re: calcite multi-threading problem
but strange enough, the same code works in a single thread.
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 8:02 PM
To: "dev";
Subject: Re: calcite multi-threading problem
we checked the janino release note page
http://janino-compiler.github.io/janino/changelog.html
Fixed issue #141: Unable to find org.codehaus.commons.compiler.properties in
java 11: ICompilerFactories were loaded through the current thread's "context
class loader", which is a bad choice in some environments. Thus, the methods
"getDefaultCompilerFactory()", "getAllCompilerFactories()" and
"getCompilerFactory(className)" were duplicated and augmented with a
"classLoader" parameter.
what does this mean? the getDefaultCompilerFactory method with a classloader
parameter was removed intentionally?
but it seems calcite still needs it , so what version of janino should we use
for the latest calcite-core?
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 12:01 PM
To: "dev";
Subject: Re:calcite multi-threading problem
In our use case, the Java class which opens calcite connection is contained in
a external Java jar file, which is loaded dynamicly through classloaders other
than the default systemloader like this:
try {
ClassLoader loader = URLClassLoader.newInstance(
new URL[] { new URL("jar:file:" + path + "!/") },
getClass().getClassLoader());
Class clazz = Class.forName(classPath, true, loader);
we mention this because the error message shows a classloader parameter.
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 11:56 AM
To: "dev";
Subject: Re: calcite multi-threading problem
And if we remove calcite from our project, janino is not in "resolved
depencies" library list of pom.xml,
So we think it not the janino conflic problem.
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 9:49 AM
To: "dev";
Subject: Re: calcite multi-threading problem
can you share the solution for the problem, we are using the following
libraries, and it seems janino 3.1.6 is only dependent by calcite-core.
UTF-8
UTF-8
9
9
9
2.17.1
7.64.0.Final
org.apache.calcite
calcite-core
1.29.0
org.apache.calcite.avatica
avatica-core
1.20.0
org.drools
drools-core
${drools.version}
org.drools
drools-compiler
${drools.version}
org.drools
drools-decisiontables
${drools.version}
-- Original --
From: "xiong duan";;
Send time: Saturday, Feb 12, 2022 8:43 AM
To: "dev";
Subject: Re: calcite multi-threading problem
Yes, It is a dependency issue. I have met the same problem. +1 for Dmitry.
Dmitry Sysolyatin 于2022年2月11日周五 21:18写道：
> Actually, I had the same problem with spark. Spark 3.2.1 uses the old
> version of janino, calcite 1.29 uses the new version of janino and they are
> not compatible.
> If I downgrade janino version to version which spark uses I got an error
> not method found exception
> If I upgrade the janino version to the version which calcite uses I get the
> same error but with another method.
>
> As a result, I built a separate calcite jar package where I shaded janino
> dependency
>
>
>
> On Fri, Feb 11, 2022 at 2:39 PM stanilovsky evgeny <
> estanilovs...@gridgain.com> wrote:
>
> > for example on a current calcite branch:
> > grep janino gradle.properties
> > janino.version=3.1.6
> >
> >
> > > yes, we use drools in the same project, and drools uses janino
> > > too, is there a version list of janino which calcite supports?
> > >
> > >
> > >
> > >
> > > ---Original---
> > > From: "Dmitry Sysolyatin" > > Date: Fri, Feb 11, 2022 18:30 PM
> > > To: "dev" > > Subject: Re: calcite multi-threading problem
> > >
> > >
> > > Hi!
> > >
> >
> It looks like a dependency issue, not like a multi-threading issue. Most
> > >
> >
> likely you have some dependency that use another version of janino but
> > > calcite requires another one
> > >
> > >
> >
> On Fri, Feb 11, 2022 at 12:18 PM xiaobo > > wrote:
> > >
> > > > Hi,
> > > >
> > > >
> > >
> >
> > we open a calcite connection with ReflectiveSchema in one thread, and
> > >
> >
> > execute sqls in other threads against the connection, but failed with
> > > > this error
> > > >
> > > >
> > > >
> > > >
> > > > com.google.common.util.concurrent.ExecutionError:
> > > > java.lang.NoSuchMethodError:
> > > >
> > >
> >
> > org.codehaus.commons.compiler.CompilerFactoryFactory.getDefaultCompilerFactory(Ljava/lang/ClassLoader;)
> > > >
> > > > Lorg/codeh
Re: calcite multi-threading problem
3.1.4 of janino and commons-compiler works now.
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 8:04 PM
To: "dev";
Subject: Re: calcite multi-threading problem
but strange enough, the same code works in a single thread.
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 8:02 PM
To: "dev";
Subject: Re: calcite multi-threading problem
we checked the janino release note page
http://janino-compiler.github.io/janino/changelog.html
Fixed issue #141: Unable to find org.codehaus.commons.compiler.properties in
java 11: ICompilerFactories were loaded through the current thread's "context
class loader", which is a bad choice in some environments. Thus, the methods
"getDefaultCompilerFactory()", "getAllCompilerFactories()" and
"getCompilerFactory(className)" were duplicated and augmented with a
"classLoader" parameter.
what does this mean? the getDefaultCompilerFactory method with a classloader
parameter was removed intentionally?
but it seems calcite still needs it , so what version of janino should we use
for the latest calcite-core?
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 12:01 PM
To: "dev";
Subject: Re:calcite multi-threading problem
In our use case, the Java class which opens calcite connection is contained in
a external Java jar file, which is loaded dynamicly through classloaders other
than the default systemloader like this:
try {
ClassLoader loader = URLClassLoader.newInstance(
new URL[] { new URL("jar:file:" + path + "!/") },
getClass().getClassLoader());
Class clazz = Class.forName(classPath, true, loader);
we mention this because the error message shows a classloader parameter.
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 11:56 AM
To: "dev";
Subject: Re: calcite multi-threading problem
And if we remove calcite from our project, janino is not in "resolved
depencies" library list of pom.xml,
So we think it not the janino conflic problem.
-- Original --
From: "xiaobo ";;
Send time: Saturday, Feb 12, 2022 9:49 AM
To: "dev";
Subject: Re: calcite multi-threading problem
can you share the solution for the problem, we are using the following
libraries, and it seems janino 3.1.6 is only dependent by calcite-core.
UTF-8
UTF-8
9
9
9
2.17.1
7.64.0.Final
org.apache.calcite
calcite-core
1.29.0
org.apache.calcite.avatica
avatica-core
1.20.0
org.drools
drools-core
${drools.version}
org.drools
drools-compiler
${drools.version}
org.drools
drools-decisiontables
${drools.version}
-- Original --
From: "xiong duan";;
Send time: Saturday, Feb 12, 2022 8:43 AM
To: "dev";
Subject: Re: calcite multi-threading problem
Yes, It is a dependency issue. I have met the same problem. +1 for Dmitry.
Dmitry Sysolyatin 于2022年2月11日周五 21:18写道：
> Actually, I had the same problem with spark. Spark 3.2.1 uses the old
> version of janino, calcite 1.29 uses the new version of janino and they are
> not compatible.
> If I downgrade janino version to version which spark uses I got an error
> not method found exception
> If I upgrade the janino version to the version which calcite uses I get the
> same error but with another method.
>
> As a result, I built a separate calcite jar package where I shaded janino
> dependency
>
>
>
> On Fri, Feb 11, 2022 at 2:39 PM stanilovsky evgeny <
> estanilovs...@gridgain.com> wrote:
>
> > for example on a current calcite branch:
> > grep janino gradle.properties
> > janino.version=3.1.6
> >
> >
> > > yes, we use drools in the same project, and drools uses janino
> > > too, is there a version list of janino which calcite supports?
> > >
> > >
> > >
> > >
> > > ---Original---
> > > From: "Dmitry Sysolyatin" > > Date: Fri, Feb 11, 2022 18:30 PM
> > > To: "dev" > > Subject: Re: calcite multi-threading problem
> > >
> > >
> > > Hi!
> > >
> >
> It looks like a dependency issue, not like a multi-threading issue. Most
> > >
> >
> likely you have some dependency that use another version of janino but
> > > calcite requires another one
> > >
> > >
> >
> On Fri, Feb 11, 2022 at 12:18 PM xiaobo > > wrote:
> > >
> > > > Hi,
> > > >
> > > >
> > >
> >
> > we open a calcite connection with ReflectiveSchema in one thread, and
> > >
> >
> > execute sqls in other threads against the connection, but failed with
> > > > this error
> > > >
> > > >
> > > >
> > > >
> > > > com.google.c
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Hey Xiabo,
You can do this, however it is easiest to do from the "FrameworkConfig"
object, like this:
import org.apache.calcite.tools.FrameworkConfig
// Need to set case-sensitive to false, or else it tries to
// look up capitalized table names and fails
//
// IE: "EMPS" instead of "emps"
val frameworkConfig: FrameworkConfig = Frameworks.newConfigBuilder()
.defaultSchema(connection.rootSchema)
.parserConfig(SqlParser.config().withCaseSensitive(false))
.build()
Hope this helps =)
On Fri, Feb 11, 2022 at 9:09 PM xiaobo wrote:
> sorry for the html escape characters,
> we tried the following and it does not work
>
> Class.forName("org.apache.calcite.jdbc.Driver");
> Properties info = new Properties();
> info.setProperty("lex", "JAVA");
> info.setProperty(InternalProperty.CASE_SENSITIVE.name(),
> "false");
> info.setProperty("defaultSchema", "hr");
> Connection connection =
> DriverManager.getConnection("jdbc:calcite:", info);
> CalciteConnection conn =
> connection.unwrap(CalciteConnection.class);
> SchemaPlus rootSchema = conn.getRootSchema();
> Schema schema = new ReflectiveSchema(target);
> rootSchema.add(schemaName, schema);
> return conn;
>
>
>
>
> -- Original --
> From: "xiaobo ";;
> Send time: Friday, Feb 11, 2022 11:20 PM
> To: "dev";
>
> Subject: can we set a default schema for calcite connection to avoid
> writing schema names in sql
>
>
>
> we have tried the following and it does not work
>
>
> Class.forName("org.apache.calcite.jdbc.Driver");
> Properties info =
> new Properties();
>
> info.setProperty("lex", "JAVA");
>
> info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> "false");
>
> info.setProperty("defaultSchema", "hr");
> try {
>
> Connection connection =
>
>
> DriverManager.getConnection("jdbc:calcite:", info);
>
> CalciteConnection conn =
>
>
> connection.unwrap(CalciteConnection.class);
>
> SchemaPlus rootSchema = conn.getRootSchema();
>
Re: Why are nested aggregations illegal? Best alternatives?
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 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
> 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
> 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
> >> 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
> >>> 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
> 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 (HY000): Invalid use of group function
> >
> > On Thu, Feb 10, 2022 at 12:39 PM Gavin Ray
> wrote:
> >
> >> Went to test this query out and found that it can't be
Re: Why are nested aggregations illegal? Best alternatives?
Correlated ARRAY sub-query?
> On Feb 12, 2022, at 10:40 AM, Gavin Ray 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 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
>> 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
>> 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
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
> 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
>> wrote:
>>>
>>> This is a SQL limitation.
>>>
>>> mysql> select sum(1);
>>> ++
>>> | sum(1) |
>>> ++
>>> | 1 |
>>> ++
>>> 1 row in set (0.00 sec)
>>
Re: Why are nested aggregations illegal? Best alternatives?
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 wrote:
> Correlated ARRAY sub-query?
>
> > On Feb 12, 2022, at 10:40 AM, Gavin Ray 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
> 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
> >> 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
> >> 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
> 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
> > wrote:
> >
> >> Some databases, e.g. O
Re: Why are nested aggregations illegal? Best alternatives?
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 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
> wrote:
>
>> Correlated ARRAY sub-query?
>>
>> > On Feb 12, 2022, at 10:40 AM, Gavin Ray 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
>> 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
>> >> 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
>> >> 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 > >
>> wrote:
>>
>> > That is really neat about Oracle.
>> >
>> > The alternative in general is to use a subquery:
>> > SELECT avg(avg(sal)) FROM emp
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Hi Gavin,
Thanks for your help,
the defaultSchema(SchemaPlus defaultSchema) method need a SchemaPlus which only
can be get after a connection is opened, but we want to set a target subschema
such as RelfectiveShcema to be the default one, and we guess the default schema
setting operation should be done before the connection is opened.
-- Original --
From: "Gavin Ray";;
Send time: Sunday, Feb 13, 2022 1:43 AM
To: "dev";
Subject: Re: can we set a default schema for calcite connection to avoid
writing schema names in sql
Hey Xiabo,
You can do this, however it is easiest to do from the "FrameworkConfig"
object, like this:
import org.apache.calcite.tools.FrameworkConfig
// Need to set case-sensitive to false, or else it tries to
// look up capitalized table names and fails
//
// IE: "EMPS" instead of "emps"
val frameworkConfig: FrameworkConfig = Frameworks.newConfigBuilder()
.defaultSchema(connection.rootSchema)
.parserConfig(SqlParser.config().withCaseSensitive(false))
.build()
Hope this helps =)
On Fri, Feb 11, 2022 at 9:09 PM xiaobo wrote:
> sorry for the html escape characters,
> we tried the following and it does not work
>
> Class.forName("org.apache.calcite.jdbc.Driver");
> Properties info = new Properties();
> info.setProperty("lex", "JAVA");
> info.setProperty(InternalProperty.CASE_SENSITIVE.name(),
> "false");
> info.setProperty("defaultSchema", "hr");
> Connection connection =
> DriverManager.getConnection("jdbc:calcite:", info);
> CalciteConnection conn =
> connection.unwrap(CalciteConnection.class);
> SchemaPlus rootSchema = conn.getRootSchema();
> Schema schema = new ReflectiveSchema(target);
> rootSchema.add(schemaName, schema);
> return conn;
>
>
>
>
> -- Original --
> From: "xiaobo ";;
> Send time: Friday, Feb 11, 2022 11:20 PM
> To: "dev";
>
> Subject: can we set a default schema for calcite connection to avoid
> writing schema names in sql
>
>
>
> we have tried the following and it does not work
>
>
> Class.forName("org.apache.calcite.jdbc.Driver");
> Properties info =
> new Properties();
>
> info.setProperty("lex", "JAVA");
>
> info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> "false");
>
> info.setProperty("defaultSchema", "hr");
> try {
>
> Connection connection =
>
>
> DriverManager.getConnection("jdbc:calcite:", info);
>
> CalciteConnection conn =
>
>
> connection.unwrap(CalciteConnection.class);
>
> SchemaPlus rootSchema = conn.getRootSchema();
>
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
You can create an empty root schema, add a ReflectiveSchema, and then set
this as the default schema:
val rootSchema: SchemaPlus = Frameworks.createRootSchema(true)
val hrReflectiveSchema = ReflectiveSchema(HrSchema())
rootSchema.add("hr", hrReflectiveSchema)
Frameworks.newConfigBuilder()
.defaultSchema(rootSchema.getSubSchema("hr"))
On Sat, Feb 12, 2022 at 9:14 PM xiaobo wrote:
> Hi Gavin,
> Thanks for your help,
>
> the defaultSchema(SchemaPlus defaultSchema) method need a SchemaPlus which
> only can be get after a connection is opened, but we want to set a target
> subschema such as RelfectiveShcema to be the default one, and we guess the
> default schema setting operation should be done before the connection is
> opened.
>
>
>
> -- Original --
> From: "Gavin Ray";;
> Send time: Sunday, Feb 13, 2022 1:43 AM
> To: "dev";
>
> Subject: Re: can we set a default schema for calcite connection to avoid
> writing schema names in sql
>
>
>
> Hey Xiabo,
>
> You can do this, however it is easiest to do from the "FrameworkConfig"
> object, like this:
>
> import org.apache.calcite.tools.FrameworkConfig
> // Need to set case-sensitive to false, or else it tries to
> // look up capitalized table names and fails
> //
> // IE: "EMPS" instead of "emps"
> val frameworkConfig: FrameworkConfig = Frameworks.newConfigBuilder()
> .defaultSchema(connection.rootSchema)
> .parserConfig(SqlParser.config().withCaseSensitive(false))
> .build()
>
> Hope this helps =)
>
> On Fri, Feb 11, 2022 at 9:09 PM xiaobo
> wrote:
>
> > sorry for the html escape characters,
> > we tried the following and it does not work
> >
> > Class.forName("org.apache.calcite.jdbc.Driver");
> > Properties info = new Properties();
> > info.setProperty("lex", "JAVA");
> > info.setProperty(InternalProperty.CASE_SENSITIVE.name(),
> > "false");
> > info.setProperty("defaultSchema", "hr");
> > Connection connection =
> > DriverManager.getConnection("jdbc:calcite:", info);
> > CalciteConnection conn =
> > connection.unwrap(CalciteConnection.class);
> > SchemaPlus rootSchema = conn.getRootSchema();
> > Schema schema = new ReflectiveSchema(target);
> > rootSchema.add(schemaName, schema);
> > return conn;
> >
> >
> >
> >
> > -- Original --
> > From: "xiaobo ";;
> > Send time: Friday, Feb 11, 2022 11:20 PM
> > To: "dev";
> >
> > Subject: can we set a default schema for calcite connection to avoid
> > writing schema names in sql
> >
> >
> >
> > we have tried the following and it does not work
> >
> >
> > Class.forName("org.apache.calcite.jdbc.Driver");
> > Properties info
> =
> > new Properties();
> >
> > info.setProperty("lex", "JAVA");
> >
> > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > "false");
> >
> > info.setProperty("defaultSchema", "hr");
> > try {
> >
> > Connection connection =
> >
> >
> > DriverManager.getConnection("jdbc:calcite:", info);
> >
> > CalciteConnection conn =
> >
> >
> > connection.unwrap(CalciteConnection.class);
> >
> > SchemaPlus rootSchema = conn.getRootSchema();
> >
Re: Why are nested aggregations illegal? Best alternatives?
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", 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 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 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
>> wrote:
>>
>>> Correlated ARRAY sub-query?
>>>
>>> > On Feb 12, 2022, at 10:40 AM, Gavin Ray 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
>>> >
Re: Why are nested aggregations illegal? Best alternatives?
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 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", 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 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 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
>>> wrote:
>>>
Correlated ARRAY sub-query?
> On Feb 12, 2022, at 10:40 AM, Gavin Ray
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
