[HIVE-24101] Invalid table alias order by columns(>=2) if cbo is false

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 3.1.0
• Component/s: CBO
• Assignee: Unassigned
• Reporter: zhaolong
• Created: 01/Sep/20 03:28
• Updated: 01/Sep/20 06:14
• Resolved: 01/Sep/20 06:14

Description

create table a
(
item_id string,
stru_area_id string
)partitioned by ( PT_DT string) stored as orc;

create table b
(
CREATE_ORG_ID string,
PROMOTION_ID string,
PROMOTION_STATUS string
) partitioned by (pt_dt string) stored as orc;

create table c
(
STRU_ID string,
SUP_STRU string
) partitioned by(pt_dt string) stored as orc;

set hive.cbo.enable=false;

explain
insert into table a partition( PT_DT = '2020-08-22' )
(item_id , stru_area_id)
select
'zzzz' ITEM_ID , T.STRU_ID STRU_AREA_ID
from (
select
STRU_ID STRU_ID ,T0.STRU_ID STRU_ID_BRANCH
from c T0
) T
inner join (
select
CREATE_ORG_ID
from b TT
) TIV
on ( STRU_ID_BRANCH = CREATE_ORG_ID )
group by T.STRU_ID
order by 1,2;

if delete order by 1,2 it`s ok.