[FLINK-31003] Flink SQL IF / CASE WHEN Funcation incorrect

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.15.0, 1.15.1, 1.16.0, 1.15.2, 1.15.3, 1.16.1
• Component/s: Table SQL / API
• Assignee: Unassigned
• Reporter: weiqinpan
• Created: 10/Feb/23 03:57
• Updated: 13/Feb/23 08:31
• Resolved: 13/Feb/23 08:31

Description

When I execute the below sql using sql-client，i found something wrong.

 

CREATE TEMPORARY TABLE source (
  mktgmsg_biz_type STRING,
  marketing_flow_id STRING,
  mktgmsg_campaign_id STRING
)
WITH
(
  'connector' = 'filesystem',
  'path' = 'file:///Users/xxx/Desktop/demo.json',
  'format' = 'json'
); 

-- return correct value('marketing_flow_id') 
SELECT IF(`marketing_flow_id` IS NOT NULL, `marketing_flow_id`, '') FROM source;

-- return incorrect value('')
SELECT IF(`marketing_flow_id` IS  NULL, '', `marketing_flow_id`) FROM source;

The demo.json data is 

 

{"mktgmsg_biz_type": "marketing_flow", "marketing_flow_id": "marketing_flow_id", "mktgmsg_campaign_id": "mktgmsg_campaign_id"} 

 

 

BTW, use case when + if / ifnull also have something wrong.

 

-- return wrong value(''), expect return marketing_flow_id
select CASE
  WHEN `mktgmsg_biz_type` = 'marketing_flow'     THEN IF(`marketing_flow_id` IS NULL, `marketing_flow_id`, '')
  WHEN `mktgmsg_biz_type` = 'mktgmsg_campaign'   THEN IF(`mktgmsg_campaign_id` IS NULL, '', `mktgmsg_campaign_id`)
  ELSE ''
  END AS `message_campaign_instance_id` FROM source;

-- return wrong value('')
select CASE
  WHEN `mktgmsg_biz_type` = 'marketing_flow'     THEN IFNULL(`marketing_flow_id`, '')
  WHEN `mktgmsg_biz_type` = 'mktgmsg_campaign'   THEN IFNULL(`mktgmsg_campaign_id`, '')
  ELSE ''
  END AS `message_campaign_instance_id` FROM source;

-- return correct value, the difference is [else return ' ']
select CASE
  WHEN `mktgmsg_biz_type` = 'marketing_flow'     THEN IFNULL(`marketing_flow_id`, '')
  WHEN `mktgmsg_biz_type` = 'mktgmsg_campaign'   THEN IFNULL(`mktgmsg_campaign_id`, '')
  ELSE ' '
  END AS `message_campaign_instance_id` FROM source;

 

 

 

Issue Links

• is duplicated by: FLINK-30559 May get wrong result for `if` expression if it's string data type