[
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:14 PM:
------------------------------------------------------------
By checking the code and sql standard. 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.
however, the new sql-standard-2003, 6.36 allows array 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 either)
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 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. 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.
however, the new sql-standard-2003, 6.36 allows array 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 either)
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 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)
