julianhyde commented on a change in pull request #1526: [CALCITE-3439] Support
Intersect and Minus in RelMdPredicates
URL: https://github.com/apache/calcite/pull/1526#discussion_r338403532
##########
File path: core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
##########
@@ -1589,6 +1589,67 @@ private void checkPredicates(RelOptCluster cluster,
RelOptTable empTable,
sortsAs("[IS NULL($0)]"));
}
+ @Test public void testPullUpPredicatesFromUnion() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ RelNode rel = null;
+ rel = convertSql(""
+ + "select empno from emp where empno=1\n"
+ + "union all\n"
+ + "select empno from emp where empno=1");
+ assertThat(mq.getPulledUpPredicates(rel).pulledUpPredicates,
+ sortsAs("[=($0, 1)]"));
+
+ rel = convertSql(""
+ + "select empno, deptno from emp where empno=1 or deptno=2\n"
+ + "union all\n"
+ + "select empno, deptno from emp where empno=3 or deptno=4");
+ assertThat(mq.getPulledUpPredicates(rel).pulledUpPredicates,
+ sortsAs("[OR(=($0, 1), =($1, 2), =($0, 3), =($1, 4))]"));
+
+ rel = convertSql(""
+ + "select empno, comm, deptno from emp where empno=1 and comm=2 and
deptno=3\n"
+ + "union all\n"
+ + "select empno, comm, deptno from emp where empno=1 and comm=4");
+ assertThat(mq.getPulledUpPredicates(rel).pulledUpPredicates,
+ sortsAs("[=($0, 1), OR(AND(=($1, 2), =($2, 3)), =($1, 4))]"));
+
+ }
+
+ @Test public void testPullUpPredicatesFromIntersect() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ RelNode rel = null;
+ rel = convertSql(""
+ + "select empno from emp where empno=1\n"
+ + "intersect all\n"
+ + "select empno from emp where empno=1");
+ assertThat(mq.getPulledUpPredicates(rel).pulledUpPredicates,
+ sortsAs("[=($0, 1)]"));
+
+ rel = convertSql(""
+ + "select empno, deptno from emp where empno=1 and deptno=2\n"
+ + "intersect all\n"
+ + "select empno, deptno from emp where empno=1 and deptno=3");
+ assertThat(mq.getPulledUpPredicates(rel).pulledUpPredicates,
+ sortsAs("[=($0, 1)]"));
+
+ rel = convertSql(""
+ + "select empno, deptno, comm from emp where 1=empno and deptno=2\n"
+ + "intersect all\n"
+ + "select empno, deptno, comm from emp where empno=1 and comm=3");
+ assertThat(mq.getPulledUpPredicates(rel).pulledUpPredicates,
+ sortsAs("[=($0, 1)]"));
+ }
+
+ @Test public void testPullUpPredicatesFromMinus() {
+ final RelMetadataQuery mq = RelMetadataQuery.instance();
+ RelNode rel = convertSql(""
+ + "select empno, deptno, comm from emp where empno=1 and deptno=2\n"
+ + "except all\n"
+ + "select empno, deptno, comm from emp where comm=3");
+ assertThat(mq.getPulledUpPredicates(rel).pulledUpPredicates,
+ sortsAs("[=($0, 1), =($1, 2)]"));
Review comment:
True in this case (because the two branches have the same FROM clause) but I
think it may be difficult to deduce in general.
For the case that the branches have the same FROM clause, we'd be better
served to have a rewrite to 'select ... from emp where empno = 1 and deptno = 2
and not (comm = 3)'. I wonder how often that rewrite would apply in practice.
----------------------------------------------------------------
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:
[email protected]
With regards,
Apache Git Services
