LakeShen commented on code in PR #3396:
URL: https://github.com/apache/calcite/pull/3396#discussion_r1337437624
##########
core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml:
##########
@@ -6022,6 +6022,176 @@ LogicalProject(EXPR$0=[1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalFilter(condition=[>($5, 100)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLimitMerge">
+ <Resource name="sql">
+ <![CDATA[select deptno from
+(select deptno from emp where sal > 100 limit 10000)
+limit 10]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalProject(DEPTNO=[$7])
+ LogicalSort(fetch=[10])
+ LogicalSort(fetch=[10000])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalSort(fetch=[10])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLimitMerge1">
+ <Resource name="sql">
+ <![CDATA[select deptno from
+(select deptno from emp where sal > 100 order by sal desc limit 10000)
+limit 10]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalSort(fetch=[10])
+ LogicalSort(sort0=[$5], dir0=[DESC], fetch=[10000])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalSort(sort0=[$5], dir0=[DESC], fetch=[10])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLimitMerge2">
+ <Resource name="sql">
+ <![CDATA[select deptno from
+(select deptno from emp where sal > 100 order by sal desc limit 10)
+limit 10000]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalSort(fetch=[10000])
+ LogicalSort(sort0=[$5], dir0=[DESC], fetch=[10])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalSort(sort0=[$5], dir0=[DESC], fetch=[10])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLimitMerge3">
+ <Resource name="sql">
+ <![CDATA[select deptno from
+(select deptno from emp where sal > 100 limit 100)
+order by deptno desc]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalProject(DEPTNO=[$7])
+ LogicalSort(sort0=[$7], dir0=[DESC])
+ LogicalSort(fetch=[100])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalSort(sort0=[$7], dir0=[DESC])
+ LogicalSort(fetch=[100])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLimitMerge4">
+ <Resource name="sql">
+ <![CDATA[select deptno from
+(select deptno from emp where sal > 100 order by sal desc limit 10)
+order by deptno limit 10000]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalSort(sort0=[$7], dir0=[ASC], fetch=[10000])
+ LogicalSort(sort0=[$5], dir0=[DESC], fetch=[10])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalSort(sort0=[$7], dir0=[ASC], fetch=[10000])
Review Comment:
> I'm wondering if outer limit size > inner limit size, such as
testLimitMerge4, actually we can use limit 10 instead of 10000.
Hi @chucheng92 , thanks for your review.
This rule only deal with the limit,it could not deal with the `limit` and
`order by` together.
In this scenario,the subquery has order by and limit,which could call
TopN,and the outer is also TopN,we could not merge two TopN,so LimitMerge would
not work.
More detail could see Presto's [MergeLimits
](https://github.com/prestodb/presto/blob/9d1a8ae1d79172de1a50eaed0de2d2cd1460ec52/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimits.java#L26C14-L26C25)Rule
##########
core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml:
##########
@@ -6022,6 +6022,176 @@ LogicalProject(EXPR$0=[1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalFilter(condition=[>($5, 100)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLimitMerge">
+ <Resource name="sql">
+ <![CDATA[select deptno from
+(select deptno from emp where sal > 100 limit 10000)
+limit 10]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalProject(DEPTNO=[$7])
+ LogicalSort(fetch=[10])
+ LogicalSort(fetch=[10000])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalSort(fetch=[10])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLimitMerge1">
+ <Resource name="sql">
+ <![CDATA[select deptno from
+(select deptno from emp where sal > 100 order by sal desc limit 10000)
+limit 10]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalSort(fetch=[10])
+ LogicalSort(sort0=[$5], dir0=[DESC], fetch=[10000])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalSort(sort0=[$5], dir0=[DESC], fetch=[10])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLimitMerge2">
+ <Resource name="sql">
+ <![CDATA[select deptno from
+(select deptno from emp where sal > 100 order by sal desc limit 10)
+limit 10000]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalSort(fetch=[10000])
+ LogicalSort(sort0=[$5], dir0=[DESC], fetch=[10])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalSort(sort0=[$5], dir0=[DESC], fetch=[10])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLimitMerge3">
+ <Resource name="sql">
+ <![CDATA[select deptno from
+(select deptno from emp where sal > 100 limit 100)
+order by deptno desc]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalProject(DEPTNO=[$7])
+ LogicalSort(sort0=[$7], dir0=[DESC])
+ LogicalSort(fetch=[100])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalSort(sort0=[$7], dir0=[DESC])
+ LogicalSort(fetch=[100])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLimitMerge4">
+ <Resource name="sql">
+ <![CDATA[select deptno from
+(select deptno from emp where sal > 100 order by sal desc limit 10)
+order by deptno limit 10000]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalSort(sort0=[$7], dir0=[ASC], fetch=[10000])
+ LogicalSort(sort0=[$5], dir0=[DESC], fetch=[10])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalSort(sort0=[$7], dir0=[ASC], fetch=[10000])
Review Comment:
> I'm wondering if outer limit size > inner limit size, such as
testLimitMerge4, actually we can use limit 10 instead of 10000.
Hi @chucheng92 , thanks for your review.
This rule only deal with the limit,it could not deal with the `limit` and
`order by` together.
In this scenario,the subquery has order by and limit,which could call
TopN,and the outer is also TopN,we could not merge two TopN,so LimitMerge would
not work.
More detail could see Presto's [MergeLimits
](https://github.com/prestodb/presto/blob/9d1a8ae1d79172de1a50eaed0de2d2cd1460ec52/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimits.java#L26C14-L26C25)Rule
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
