[
https://issues.apache.org/jira/browse/CALCITE-6041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17777321#comment-17777321
]
Ran Tao edited comment on CALCITE-6041 at 10/19/23 3:35 PM:
------------------------------------------------------------
By checking the code and sql standard. I have got some background/informations:
calcite currently support:
{code:java}
select array(select empno from emps [where...]);
select multiset(select empno from emps [where...]);
select map(select empno,deptno from emps [where...]); {code}
they can not use LIMIT or OrderBy (the parser use LeafQueryOrExpr not allow
OrderBy/LIMIT).
however, the sql-standard-2003, 6.36 allows array query with orderby:
{code:java}
<array value constructor by query> ::=
ARRAY <left paren> <query expression> [ <order by clause> ] <right paren>
{code}
{code:java}
<multiset value constructor by query> ::=
MULTISET <left paren> <query expression> <right paren> {code}
Generally speaking, MULTISET without OrderBy is reasonable. but maybe we can
support array sub-query with OrderBy. (whether to support LIMIT? need discussed)
Considering MAP sub-query, it is not a sql standard collection. We can discuss
it, but in terms of map characteristics, it should be unordered similar to
MULTISET, but my question is that currently calcite uses LinkedHashMap for
storage, ensuring the order. I'm asking this because if we use LinkedHashMap
then if we support OrderBy, we will get deterministic result for MAP. I'm not
sure if this is expected.
But in any case, the support of OrderBy or LIMIT is orthogonal to this issue,
and we can record or support it later.
was (Author: lemonjing):
By checking the code and sql standard. I have got some background/informations:
calcite currently support:
{code:java}
select array(select empno from emps [where...]);
select multiset(select empno from emps [where...]);
select map(select empno,deptno from emps [where...]); {code}
they can not use LIMIT or OrderBy (the parser use LeafQueryOrExpr not allow
OrderBy/LIMIT).
however, the sql-standard-2003, 6.36 allows array query with orderby:
{code:java}
<array value constructor by query> ::=
ARRAY <left paren> <query expression> [ <order by clause> ] <right paren>
{code}
{code:java}
<multiset value constructor by query> ::=
MULTISET <left paren> <query expression> <right paren> {code}
MULTISET without OrderBy is reasonable. but maybe we can support array
sub-query with OrderBy. (whether to support LIMIT? need discussed)
Considering MAP sub-query, it is not a sql standard collection. We can discuss
it, but in terms of map characteristics, it should be unordered similar to
MULTISET, but my question is that currently calcite uses LinkedHashMap for
storage, ensuring the order. I'm asking this because if we use LinkedHashMap
then if we support OrderBy, we will get deterministic result for MAP. I'm not
sure if this is expected.
But in any case, the support of OrderBy or LIMIT is orthogonal to this issue,
and we can record or support it later.
> MAP sub-query gives NullPointerException
> ----------------------------------------
>
> Key: CALCITE-6041
> URL: https://issues.apache.org/jira/browse/CALCITE-6041
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.35.0
> Reporter: Ran Tao
> Assignee: Ran Tao
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> calcite support array/map/multiset query constructor, but if we run map query
> such as:
> {code:java}
> select map(select 1, 2)
> select map(select empno, deptno from emps); {code}
> It will cause exception:
> {noformat}
> java.sql.SQLException: Error while executing SQL "select map(select 1, 2)":
> Unable to implement EnumerableNestedLoopJoin(condition=[true],
> joinType=[semi]): rowcount = 1.0, cumulative cost = \{13.0 rows, 3.0 cpu, 0.0
> io}, id = 72
> EnumerableCollect(field=[x]): rowcount = 1.0, cumulative cost = \{2.0 rows,
> 2.0 cpu, 0.0 io}, id = 69
> EnumerableValues(tuples=[[\\{ 1, 2 }]]): rowcount = 1.0, cumulative cost
> = \{1.0 rows, 1.0 cpu, 0.0 io}, id = 38
> EnumerableValues(tuples=[[\\{ 0 }]]): rowcount = 1.0, cumulative cost =
> \{1.0 rows, 1.0 cpu, 0.0 io}, id = 35
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at
> org.apache.calcite.test.SqlOperatorTest$TesterImpl.check(SqlOperatorTest.java:13107)
> at
> org.apache.calcite.sql.test.SqlOperatorFixture.check(SqlOperatorFixture.java:439)
> at
> org.apache.calcite.sql.test.SqlOperatorFixture.check(SqlOperatorFixture.java:415)
> at
> org.apache.calcite.sql.test.SqlOperatorFixture.check(SqlOperatorFixture.java:420)
> at
> org.apache.calcite.test.SqlOperatorTest.testMapQueryConstructor(SqlOperatorTest.java:10235)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:217)
> at
> org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:213)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:138)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
> at
> org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
> at
> org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
> at
> org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
> at java.util.ArrayList.forEach(ArrayList.java:1259)
> at
> org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
> at
> org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
> at
> org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
> at
> org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
> at java.util.ArrayList.forEach(ArrayList.java:1259)
> at
> org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
> at
> org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
> at
> org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
> at
> org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
> at
> org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
> at
> org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
> at
> org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
> at
> org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
> at
> org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:147)
> at
> org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:127)
> at
> org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:90)
> at
> org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:55)
> at
> org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:102)
> at
> org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:54)
> at
> org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
> at
> org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)
> at
> org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)
> at
> org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)
> at
> com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57)
> at
> com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
> at
> com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
> at
> com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
> at
> com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
> at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
> Caused by: java.lang.IllegalStateException: Unable to implement
> EnumerableNestedLoopJoin(condition=[true], joinType=[semi]): rowcount = 1.0,
> cumulative cost = \{13.0 rows, 3.0 cpu, 0.0 io}, id = 72
> EnumerableCollect(field=[x]): rowcount = 1.0, cumulative cost = \{2.0 rows,
> 2.0 cpu, 0.0 io}, id = 69
> EnumerableValues(tuples=[[\\{ 1, 2 }]]): rowcount = 1.0, cumulative cost
> = \{1.0 rows, 1.0 cpu, 0.0 io}, id = 38
> EnumerableValues(tuples=[[\\{ 0 }]]): rowcount = 1.0, cumulative cost =
> \{1.0 rows, 1.0 cpu, 0.0 io}, id = 35
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:117)
> at
> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:112)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1171)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:324)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:702)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> ... 76 more
> Suppressed: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)
> at
> org.apache.calcite.adapter.enumerable.EnumerableCollect.implement(EnumerableCollect.java:99)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:107)
> at
> org.apache.calcite.adapter.enumerable.EnumerableNestedLoopJoin.implement(EnumerableNestedLoopJoin.java:155)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114)
> ... 87 more
> {noformat}
> btw, we are missing the test case of MapQueryConstructor, so it cannot be
> covered in build pipeline currently.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
