[
https://issues.apache.org/jira/browse/CALCITE-7928?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18143226#comment-18143226
]
Mihai Budiu commented on CALCITE-7928:
--------------------------------------
Here is a query which is equivalent with the one produced by the decorrelator,
which has no correlated subqueries, yet still requires more than 2M
applications of a planner rule:
{code:java}
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')),
vv (v) as (values 'A', 'M'),
d as (select deptno, deptno > 0 as f from dept where deptno > 0),
dv as (select vv.v, d.deptno, d.f from vv cross join d),
dv2 as (select d.deptno, d.f, vv.v from vv cross join d)
select a.deptno, a.dname, m.v, m.empno, m.edeptno, m.ename
from (select deptno, dname, deptno > 0 as f from dept) as a
join (
select dv.v, dv.deptno, dv.f, e.empno, e.edeptno, e.ename, e.ddeptno, e.df,
e.dvv
from dv
join (select emp.empno, emp.deptno as edeptno, emp.ename,
dv2.deptno as ddeptno, dv2.f as df, dv2.v as dvv
from emp
join dv2
on emp.deptno = dv2.deptno and position(dv2.v in emp.ename) > 0) as e
on dv.deptno is not distinct from e.ddeptno
and dv.f is not distinct from e.df
and dv.v is not distinct from e.dvv) as m
on a.deptno is not distinct from m.deptno and a.f is not distinct from m.f;
{code}
So I think the issue should be rephrased to deal with the Volcano planner as
it's used by the quidem tests.
> Cross join lateral query blows up in the new top-down decorrelator
> -------------------------------------------------------------------
>
> Key: CALCITE-7928
> URL: https://issues.apache.org/jira/browse/CALCITE-7928
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Ruben Q L
> Priority: Major
> Labels: pull-request-available
>
> Seen with the following query (added to lateral.iq file via CALCITE-7911),
> but only when it is executed by CoreQuidemTest2, i.e. with the new top down
> decorrelator:
> {noformat}
> 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,
> (values 'A', 'M') as v (v)
> cross join lateral (select * from emp
> where emp.deptno = dept.deptno
> and position(v.v in emp.ename) > 0) as emp;
> +--------+------------+---+-------+---------+-------+
> | DEPTNO | DNAME | V | EMPNO | DEPTNO0 | ENAME |
> +--------+------------+---+-------+---------+-------+
> | 10 | ACCOUNTING | A | 7782 | 10 | CLARK |
> | 20 | RESEARCH | M | 7369 | 20 | SMITH |
> +--------+------------+---+-------+---------+-------+
> (2 rows)
> !ok
> {noformat}
> Locally, I observed that running CoreQuidemTest2, each *.iq file is executed
> in milliseconds or a few seconds, except for lateral.iq which took almost 20
> seconds. Removing this query, and lateral.iq went down to 200ms.
> On the CI, this has led to timeout errors, example here:
> [https://ci-builds.apache.org/job/Calcite/job/Calcite-sonar/job/main/1796/cloudbees-pipeline-explorer/]
> {noformat}
> ...
> FAILURE 322.5sec, org.apache.calcite.test.CoreQuidemTest2 > test(String)[41],
> [41] sql/lateral.iq
> java.util.concurrent.TimeoutException: test(java.lang.String) timed out
> after 5 minutes
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625)
> at
> java.base/java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:762)
> at
> java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.Spliterators$ArraySpliterator.forEachRemaining(Spliterators.java:992)
> at
> java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
> at
> java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
> at
> java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
> at
> java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
> at
> java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625)
> at
> java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
> at
> java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
> at
> java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
> at
> java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
> at
> java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
> at
> java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625)
> at
> java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
> at
> java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
> at
> java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
> at
> java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
> at
> java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:373)
> at
> java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1182)
> at
> java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1655)
> at
> java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1622)
> at
> java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:165)
>
>
> CoreQuidemTest2 > test(String) > [44] sql/functions.iq STANDARD_ERROR
> [Fatal Error] :1:2: XML document structures must start and end within the
> same entity.
>
> CoreQuidemTest2 > test(String) > [44] sql/functions.iq STANDARD_OUT
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> 2.2sec, org.apache.calcite.test.CoreQuidemTest2 > test(String)[44],
> [44] sql/functions.iq
> 2.1sec, org.apache.calcite.test.CoreQuidemTest2 > test(String)[47],
> [47] sql/new-decorr.iq
>
> CoreQuidemTest2 > test(String) > [48] sql/unsigned.iq STANDARD_OUT
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> FAILURE 533.0sec, 48 completed, 1 failed, 0 skipped,
> org.apache.calcite.test.CoreQuidemTest2 > test(String)
> FAILURE 533.0sec, 48 completed, 1 failed, 0 skipped,
> org.apache.calcite.test.CoreQuidemTest2
> FAILURE 547.2sec, 17294 completed, 1 failed, 144 skipped, Gradle Test Run
> :core:test
>
> 17294 tests completed, 1 failed, 144 skipped
>
> > Task :core:test FAILED
> {noformat}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
