[HIVE-24902] Incorrect result after fold CASE into COALESCE

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.1.2, 4.0.0
• Fix Version/s: 4.0.0-alpha-1
• Component/s: CBO
• Labels: pull-request-available
• Assignee: Nemon Lou
• Reporter: Nemon Lou
• Created: 18/Mar/21 04:03
• Updated: 17/Nov/22 08:48
• Resolved: 06/Aug/21 13:25

Description

The following sql returns only one record (20210308) but expected two(20210308
20210309).

select * from (
select 
	case when b.a=1
		then  
			cast (from_unixtime(unix_timestamp(cast(20210309 as string),'yyyyMMdd') - 86400,'yyyyMMdd') as bigint)
		else 
			20210309 
	   end 
as col
from 
(select stack(2,1,2) as (a))
 as b
) t 
where t.col is not null;

The query plan has incorrect predict:
predicate: COALESCE((col0 = 1),false) (type: boolean)

STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        TableScan
          alias: _dummy_table
          Row Limit Per Split: 1
          Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: COMPLETE
          Select Operator
            expressions: 2 (type: int), 1 (type: int), 2 (type: int)
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 1 Data size: 12 Basic stats: COMPLETE Column stats: COMPLETE
            UDTF Operator
              Statistics: Num rows: 1 Data size: 12 Basic stats: COMPLETE Column stats: COMPLETE
              function name: stack
              Filter Operator
                predicate: COALESCE((col0 = 1),false) (type: boolean)
                Statistics: Num rows: 1 Data size: 12 Basic stats: COMPLETE Column stats: COMPLETE
                Select Operator
                  expressions: CASE WHEN ((col0 = 1)) THEN (20210308L) ELSE (20210309L) END (type: bigint)
                  outputColumnNames: _col0
                  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
                  ListSink

Time taken: 0.155 seconds, Fetched: 28 row(s)

Issue Links

• links to: GitHub Pull Request #2100