Hi All, I have a System Function (IF AND CASE WHEN...
# troubleshootingw
Wei Qin Pan, Max
02/09/2023, 6:19 AMHi All, I have a System Function (IF AND CASE WHEN) issue occurred. I using Flink 1.15.0 + with Flink SQL.
Copy code
-- when i execute this sql, i can get the correct result
SELECT IF(`marketing_flow_id` IS NOT NULL, `marketing_flow_id`, '') FROM source;
-- when i execute this sql, i get the error result(return blank)
SELECT IF(`marketing_flow_id` IS NULL, '', `marketing_flow_id`) FROM source;Wei Qin Pan, Max
02/09/2023, 6:19 AMhere is my code
Copy code
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'
); Copy code
{
"mktgmsg_biz_type": "mktgmsg_campaign",
"marketing_flow_id": "marketing_flow_id",
"mktgmsg_campaign_id": "mktgmsg_campaign_id"
}Wei Qin Pan, Max
02/09/2023, 6:20 AMmeanwhile i use case when also occurred this isue
Copy code
-- expect result is 'mktgmsg_campaign_id', but i got blank
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;Wei Qin Pan, Max
02/09/2023, 6:20 AMwhen i run in Flink 1.12.5, both are all right.