Chunwei Lei created CALCITE-5193:
------------------------------------
Summary: 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
For example,
{code:java}
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:java}
select * from
(select * from a where id=1) a
full join
(select * from b where 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 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.7#820007)
