[ https://issues.apache.org/jira/browse/CALCITE-5193?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17866241#comment-17866241 ]
Viggo Chen edited comment on CALCITE-5193 at 7/16/24 6:09 AM: -------------------------------------------------------------- [~Chunwei Lei] It is intuitive to pushdown `COALESCE(a.pt, b.pt)='20220601'` as `pt='20220601'` filter to each input. But how do we abstractly judge whether a filter condition can be pushed down? I have a preliminary idea, but I'm not sure if it's correct. For left input, replace all field from right input as null to create a new filter to pushdown. And the same applies to the right side. was (Author: JIRAUSER300637): [~Chunwei Lei] It is intuitive to pushdown `COALESCE(a.pt, b.pt)='20220601'` as `pt='20220601'` filter to each input. But how do we abstractly judge whether a filter condition can be pushed down? I have a preliminary idea, but I'm not sure if it's correct. For left input, replace all field from right input as null to create a new filter to pushdown. And the same applies to the right side. > 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)