[
https://issues.apache.org/jira/browse/CALCITE-7928?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18143583#comment-18143583
]
Ruben Q L commented on CALCITE-7928:
------------------------------------
Thanks for looking into this [~mbudiu]!
It's interesting... did you check what was the output plan of the "old
decorrleator" (which did not suffer from timeout) vs the "new top-down
decorralator" (which experienced the timeout) on the problematic query?
> Volcano optimizer takes a very long time for a cross join lateral query
> -----------------------------------------------------------------------
>
> 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)
