[ https://issues.apache.org/jira/browse/CALCITE-5193?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17866062#comment-17866062 ]
Viggo Chen commented on CALCITE-5193: ------------------------------------- [~Chunwei Lei] I think this is a great idea. But why didn't you continue to follow up on this feature, is there a theoretical flaw? > Push filter whose conditions include join keys and are composed by OR into > inputs of full join > ---------------------------------------------------------------------------------------------- > > Key: CALCITE-5193 > URL: https://issues.apache.org/jira/browse/CALCITE-5193 > Project: Calcite > Issue Type: Improvement > Reporter: Chunwei Lei > Priority: Major > > For example, > {code:sql} > select * from a full join b on a.id=b.id where a.id=1 or b.id=2; > {code} > can be transformed to > {code:sql} > select * from > (select * from a where id=1 or id=2) a > full join > (select * from b where id=1 or id=2) b > on a.id=b.id; > {code} > If {{a}} and {{b}} are both partitioned tables and id is the partition key, > we can do partition pruning with this transformation, which is a big > improvement. > This improvement is inspired by the query > {code:java} > select * from a full join b on a.id=b.id and a.pt=b.pt where COALESCE(a.pt, > b.pt)='20220601'; > {code} > which costs a lot due to it scans all partitions in table {{a}} and {{b}}. -- This message was sent by Atlassian Jira (v8.20.10#820010)