[HIVE-15888] constant propagation optimizer failed when query has the same alias with subquery

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.2.1
• Component/s: Logical Optimizer
• Assignee: Unassigned
• Reporter: Walter Wu
• Created: 13/Feb/17 03:04
• Updated: 14/Feb/17 02:56
• Resolved: 14/Feb/17 02:56

Description

Example :
select *
from dpdim_employee_org_d c
join
(
select a.* from dpmid_md_organization a
left outer join dpmid_md_organization b
on a.organizationid = b.superiororganizationid and b.hisdate = '2016-10-05'
where a.hisdate = '2016-09-05'
and b.organizationid is null
) b
on c.org_id = b.organizationid
and c.hp_cal_dt = '2016-09-05' limit 10;

Description:
when ppd optimize is enabled this query has empty result . If we unenabled constant propagation optimize or we replace the subquery alias 'b' with 'b1' , this query will work correctly.
I explain this query and find that after ppd optimize Filter Operator predicate conf changed from 'predicate: superiororganizationid is not null (type: boolean)' to 'predicate: false (type: boolean)'.
The subquery has a filter predicate conf 'b.organizationid is null','b.organizationid' should equal to 'b:b.organizationid' . The outer query has a filter predicate conf 'b.organizationid is not null', 'b.organizationid' should equal to 'b:a.organizationid'. While rowSchema get Column Info on tabAlias:'b' and alias:'organizationid'. constant propagation optimize combine 'b.organizationid is not null' and 'b.organizationid is null' to 'constant false' .

Issue Links

• is related to: HIVE-14705 Hive outer queries is not picking up the right column from subqueries
• is related to: HIVE-13733 CTE + "IS NULL" predicate + column aliasing as existing column leads to wrong results
• is related to: HIVE-15495 subquery's column alias can not be the same with partition column name
• is related to: HIVE-13602 TPCH q16 return wrong result when CBO is on