[HIVE-8280] CBO : When filter is applied on dimension table PK/FK code path is not in effect.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.14.0
• Fix Version/s: 0.14.0
• Component/s: CBO
• Assignee: Harish Butani
• Reporter: Mostafa Mokhtar
• Created: 26/Sep/14 22:51
• Updated: 13/Nov/14 19:42
• Resolved: 13/Oct/14 18:09

Description

When a filter is applied on PK side joins don't qualify as PK/FK join.

In getUniqueKeys when a filter is applied on the table the child is no longer a table scan.

  public Set<BitSet> getUniqueKeys(ProjectRelBase rel, boolean ignoreNulls) {

    RelNode child = rel.getChild();

    if (!(child instanceof HiveTableScanRel)) {
      Function<RelNode, Metadata> fn = RelMdUniqueKeys.SOURCE.apply(
          rel.getClass(), BuiltInMetadata.UniqueKeys.class);
      return ((BuiltInMetadata.UniqueKeys) fn.apply(rel))
          .getUniqueKeys(ignoreNulls);
    } 

Repro

with ss as 
(select 
    ss_customer_sk, ss_item_sk, ss_ticket_number
from
    store_sales,
    store
where
    s_store_sk = ss_store_sk
        and s_market_id = 4), 
sr as
(select sr_customer_sk,sr_item_sk ,sr_ticket_number from store_returns, store where s_store_sk = sr_store_sk and s_market_id=4) 
select 
    count(*)
from
    ss,
    sr
where
    ss_customer_sk = sr_customer_sk
        and ss_item_sk = sr_item_sk
        and ss_ticket_number = sr_ticket_number;

Issue Links

• is a clone of: HIVE-8263 CBO : TPC-DS Q64 is item is joined last with store_sales while it should be first as it is the most selective