[
https://issues.apache.org/jira/browse/CALCITE-6652?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17903855#comment-17903855
]
suibianwanwan edited comment on CALCITE-6652 at 12/8/24 4:18 PM:
-----------------------------------------------------------------
Hi, [~julianhyde]. As discussed in Calcite-1317, we can't do a conversion
between max and order by limit 1 when rowCount is empty.
But if the dimension is included in the aggregation, then a row count of 0 will
also produce an output of 0. In Correlate, aggregation does grouping on corVar
when decorrelate as Join, so in this case, so in this case the two are
equivalent.
was (Author: JIRAUSER301334):
Hi, [~julianhyde]. As discussed in Calcite-1317, we can't do a conversion
between max and order by limit 1 when rowCount is empty.
But the two are equivalent if they have a group by dimension. In Correlate,
aggregation does grouping on corVar when decorrelate as Join, so in this case,
so in this case the two are equivalent.
> RelDecorrelator can't decorrelate query with limit 1
> ----------------------------------------------------
>
> Key: CALCITE-6652
> URL: https://issues.apache.org/jira/browse/CALCITE-6652
> Project: Calcite
> Issue Type: Bug
> Reporter: suibianwanwan
> Assignee: suibianwanwan
> Priority: Major
>
> The following query can't decorrelate by RelDecorrelate:
> {code:java}
> @Test void testDecorrelateFetchOne1() {
> final String query = "SELECT deptno FROM dept WHERE\n" +
> "1000 > (SELECT sal FROM emp WHERE dept.deptno = emp.deptno order by
> emp.sal limit 1)";
> sql(query).withRule(
> CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
> .withLateDecorrelate(true)
> .check();
> }{code}
> is equivalent to the following query, and the following query can be
> decorrelated into a join, so we can do some appropriate conversions in
> RelDecorrelate:
> {code:java}
> @Test void testDecorrelateFetchOne1() {
> final String query = "SELECT deptno FROM dept WHERE\n" +
> "1000 > (SELECT min(sal) FROM emp WHERE dept.deptno = emp.deptno)";
> sql(query).withRule(
> CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
> .withLateDecorrelate(true)
> .check();
> }{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
