[HIVE-22777] Sorted dynamic partition optimization doesn't work if plan require implicit cast

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Query Planning
• Labels: pull-request-available
• Target Version/s: 4.0.0
• Assignee: Vineet Garg
• Reporter: Vineet Garg
• Created: 24/Jan/20 22:47
• Updated: 17/Nov/22 08:50
• Resolved: 05/Feb/20 01:26

Description

Repro
set hive.stats.autogather=false;
set hive.optimize.sort.dynamic.partition.threshold=1;
set hive.optimize.bucketingsorting = true;

drop table if exists t1_staging;
create table t1_staging(
                           a string,
                           b int,
                           c int,
                           d string)
    partitioned by (e  decimal(18,0))
    clustered by(a)
        into 256 buckets STORED AS TEXTFILE;
load data local inpath '../../data/files/sortdp/000000_0' overwrite into table t1_staging partition (e=100);

drop table t1_n147;
create table t1_n147(
                        a string,
                        b decimal(6,0),
                        c int,
                        d string)
    partitioned by (e decimal(3,0))
    clustered by(a,b)
        into 10 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true');

set hive.stats.autogather=false;
set hive.optimize.bucketingsorting = true;
explain insert overwrite table t1_n147 partition(e) select a,b,c,d,e  from t1_staging;

Issue Links

• relates to: HIVE-20910 Insert in bucketed table fails due to dynamic partition sort optimization
• links to: GitHub Pull Request #892