julianhyde commented on a change in pull request #1121: [CALCITE-2936] Simplify
EXISTS or NOT EXISTS sub-query that has "GROUP BY ()"
URL: https://github.com/apache/calcite/pull/1121#discussion_r267653025
##########
File path:
core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
##########
@@ -3150,6 +3150,60 @@ group by deptno]]>
LogicalAggregate(group=[{0}], EXPR$1=[SUM($1) FILTER $2], EXPR$2=[COUNT()])
LogicalProject(DEPTNO=[$7], $f1=[*($5, 2)], $f2=[AND(<>($0, 1), <>($0, 2))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSimplifyExistsAggregateSubQuery">
+ <Resource name="sql">
+ <![CDATA[SELECT e1.empno
+ FROM emp e1 where exists
+ (select avg(sal) from emp e2 where e1.empno = e2.empno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSimplifyNotExistsAggregateSubQuery">
+ <Resource name="sql">
+ <![CDATA[SELECT e1.empno
+ FROM emp e1 where not exists
+ (select avg(sal) from emp e2 where e1.empno = e2.empno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[NOT(true)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSimplifyExistsValuesSubQuery">
+ <Resource name="sql">
+ <![CDATA[select deptno
+ from EMP
+ where exists (values 10)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSimplifyNotExistsValuesSubQuery">
+ <Resource name="sql">
+ <![CDATA[select deptno
+ from EMP
+ where not exists (values 10)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalFilter(condition=[NOT(true)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
Review comment:
+1 for 'not (true)'. do the simplest thing that can possibly work, and let
other parts of the system (constant reduction) play their part.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
With regards,
Apache Git Services
