[HIVE-11477] CBO inserts a UDF cast for integer type promotion (only for negative numbers)

• Type: Bug
• Status: Patch Available
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 2.0.0
• Assignee: Pengcheng Xiong
• Reporter: Prasanth Jayachandran
• Created: 05/Aug/15 22:59
• Updated: 10/Aug/15 18:06

Description

When CBO is enabled, filters which compares tinyint, smallint columns with constant integer types will insert a UDFToInteger cast for the columns. When CBO is disabled, there is no such UDF. This behaviour breaks ORC predicate pushdown feature as ORC ignores UDFs in the filters.

In the following examples column t is tinyint

Explain for select count from orc_ppd where t < -127; (CBO OFF)

Filter Operator [FIL_9]
                           predicate:(t = 125) (type: boolean)
                           Statistics:Num rows: 1050 Data size: 611757 Basic stats: COMPLETE Column stats: NONE
                           TableScan [TS_0]
                              alias:orc_ppd
                              Statistics:Num rows: 2100 Data size: 1223514 Basic stats: COMPLETE Column stats: NONE

Explain for select count from orc_ppd where t < -127; (CBO ON)

Filter Operator [FIL_10]
                           predicate:(UDFToInteger(t) < -127) (type: boolean)
                           Statistics:Num rows: 700 Data size: 407838 Basic stats: COMPLETE Column stats: NONE
                           TableScan [TS_0]
                              alias:orc_ppd
                              Statistics:Num rows: 2100 Data size: 1223514 Basic stats: COMPLETE Column stats: NONE

CBO does not insert such cast for non-negative numbers

Explain for select count from orc_ppd where t < 127; (CBO ON)

Filter Operator [FIL_10]
                           predicate:(t < 127) (type: boolean)
                           Statistics:Num rows: 700 Data size: 407838 Basic stats: COMPLETE Column stats: NONE
                           TableScan [TS_0]
                              alias:orc_ppd
                              Statistics:Num rows: 2100 Data size: 1223514 Basic stats: COMPLETE Column stats: NONE