[ https://issues.apache.org/jira/browse/HIVE-24087?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Vineet Garg reassigned HIVE-24087: ---------------------------------- > FK side join elimination in presence of PK-FK constraint > -------------------------------------------------------- > > Key: HIVE-24087 > URL: https://issues.apache.org/jira/browse/HIVE-24087 > Project: Hive > Issue Type: Improvement > Components: Query Planning > Reporter: Vineet Garg > Assignee: Vineet Garg > Priority: Major > > If there is PK-FK join FK join could be eliminated by removing FK side if > following conditions are met > * There is no row filtering on FK side. > * No columns from FK side is required after JOIN. > * FK join columns are guranteed to be unique (have group by) > * FK join columns are guranteed to be NOT NULL (either IS NOT NULL filter or > constraint) > *Example* > {code:sql} > EXPLAIN > SELECT customer_removal_n0.* > FROM customer_removal_n0 > JOIN > (SELECT lo_custkey > FROM lineorder_removal_n0 > WHERE lo_custkey IS NOT NULL > GROUP BY lo_custkey) fkSide ON fkSide.lo_custkey = > customer_removal_n0.c_custkey; > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)