[
https://issues.apache.org/jira/browse/CALCITE-6266?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17866849#comment-17866849
]
Julian Hyde edited comment on CALCITE-6266 at 7/20/24 12:08 AM:
----------------------------------------------------------------
I tried a similar query on Postgres:
{code}
with dept (deptno, dname) as (values (10,'ACCOUNTING'),(20,'RESEARCH')),
emp (empno, deptno, ename) as (values (7369, 20, 'SMITH'), (7782, 10,
'CLARK'), (7499, 30, 'ALLEN'))
select *
from dept,
lateral (select * from emp where emp.deptno = dept.deptno) as emp
cross join (values ('A'), ('B')) as v (v);
{code}
It's valid on Postgres, fails on Calcite. So I now agree that this is a bug.
Assigning to myself.
Note that this query uses LATERAL but its uses a correlated subquery rather
than TABLE (table function). The problem is the associativity (strength) of the
comma join operator versus 'cross join'. Cross join is 'winning' and it should
not be.
Here's a dev branch:
https://github.com/julianhyde/calcite/tree/6266-lateral-cross-join
was (Author: julianhyde):
I tried a similar query on Postgres:
{code}
with dept (deptno, dname) as (values (10,'ACCOUNTING'),(20,'RESEARCH')),
emp (empno, deptno, ename) as (values (7369, 20, 'SMITH'), (7782, 10,
'CLARK'), (7499, 30, 'ALLEN'))
select *
from dept,
lateral (select * from emp where emp.deptno = dept.deptno) as emp
cross join (values ('A'), ('B')) as v (v);
{code}
It's valid on Postgres, fails on Calcite. So I now agree that this is a bug.
Assigning to myself.
Here's a dev branch:
https://github.com/julianhyde/calcite/tree/6266-lateral-cross-join
> SqlValidatorException with LATERAL TABLE and JOIN
> -------------------------------------------------
>
> Key: CALCITE-6266
> URL: https://issues.apache.org/jira/browse/CALCITE-6266
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.31.0, 1.32.0, 1.33.0, 1.34.0, 1.35.0, 1.36.0
> Reporter: Jeyhun Karimov
> Assignee: Julian Hyde
> Priority: Critical
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> I have the following test in SqlValidatorTest.java
> {code:java}
> @Test void test() {
> sql("select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))")
> .ok();
> }
> {code}
> This test passes on Calcite {{calcite-1.29.0}} and {{calcite-1.30.0}} but
> fails on other releases, including the main branch
> (c774c313a81d01c4e3e77cf296d04839c5ab04c0). The exception is:
> {code:java}
> org.opentest4j.AssertionFailedError: Validator threw unexpected exception;
> query [select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))]; exception [Column 'DEPTNO' not found in any table]; class
> [class org.apache.calcite.sql.validate.SqlValidatorException]; pos [line 1
> col 40 thru line 1 col 40]
> at org.junit.jupiter.api.AssertionUtils.fail(AssertionUtils.java:38)
> at org.junit.jupiter.api.Assertions.fail(Assertions.java:135)
> at org.apache.calcite.sql.test.SqlTests.checkEx(SqlTests.java:352)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertExceptionIsThrown(AbstractSqlTester.java:112)
> at
> org.apache.calcite.test.SqlValidatorFixture.ok(SqlValidatorFixture.java:191)
> at
> org.apache.calcite.test.SqlValidatorTest.jey2(SqlValidatorTest.java:284)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
> 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.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45)
> 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
> org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService$ExclusiveTask.compute(ForkJoinPoolHierarchicalTestExecutorService.java:185)
> at
> org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService.invokeAll(ForkJoinPoolHierarchicalTestExecutorService.java:129)
> 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.ForkJoinPoolHierarchicalTestExecutorService$ExclusiveTask.compute(ForkJoinPoolHierarchicalTestExecutorService.java:185)
> at
> org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService.invokeAll(ForkJoinPoolHierarchicalTestExecutorService.java:129)
> 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.ForkJoinPoolHierarchicalTestExecutorService$ExclusiveTask.compute(ForkJoinPoolHierarchicalTestExecutorService.java:185)
> at
> java.base/java.util.concurrent.RecursiveAction.exec(RecursiveAction.java:189)
> at
> java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:290)
> at
> java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1020)
> at
> java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1656)
> at
> java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1594)
> at
> java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:183)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
