[jira] [Commented] (FLINK-31003) Flink SQL IF / CASE WHEN Funcation incorrect
[ https://issues.apache.org/jira/browse/FLINK-31003?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17687663#comment-17687663 ] weiqinpan commented on FLINK-31003: --- BTW, CASE WHEN + IFNULL also have logic problem. > Flink SQL IF / CASE WHEN Funcation incorrect > > > Key: FLINK-31003 > URL: https://issues.apache.org/jira/browse/FLINK-31003 > Project: Flink > Issue Type: Bug > Components: Table SQL / API >Affects Versions: 1.15.0, 1.15.1, 1.16.0, 1.15.2, 1.15.3, 1.16.1 >Reporter: weiqinpan >Priority: Major > > When I execute the below sql using sql-client,i found something wrong. > > {code:java} > 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;{code} > The demo.json data is > > {code:java} > {"mktgmsg_biz_type": "marketing_flow", "marketing_flow_id": > "marketing_flow_id", "mktgmsg_campaign_id": "mktgmsg_campaign_id"} {code} > > > BTW, use case when + if / ifnull also have something wrong. > > {code:java} > -- 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; > {code} > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (FLINK-31003) Flink SQL IF / CASE WHEN Funcation incorrect
[ https://issues.apache.org/jira/browse/FLINK-31003?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17687149#comment-17687149 ] weiqinpan edited comment on FLINK-31003 at 2/10/23 4:20 PM: The value of marketing_flow_id is not null, but the result of below sql is empty. So unbelievable. {code:java} SELECT IF(`marketing_flow_id` IS NULL, '', `marketing_flow_id`) FROM source; {code} was (Author: JIRAUSER298918): The field of marketing_flow_id is not null, but the result of below sql is empty. So unbelievable. {code:java} SELECT IF(`marketing_flow_id` IS NULL, '', `marketing_flow_id`) FROM source; {code} > Flink SQL IF / CASE WHEN Funcation incorrect > > > Key: FLINK-31003 > URL: https://issues.apache.org/jira/browse/FLINK-31003 > Project: Flink > Issue Type: Bug > Components: Table SQL / API >Affects Versions: 1.15.0, 1.15.1, 1.16.0, 1.15.2, 1.15.3, 1.16.1 >Reporter: weiqinpan >Priority: Major > > When I execute the below sql using sql-client,i found something wrong. > > {code:java} > 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;{code} > The demo.json data is > > {code:java} > {"mktgmsg_biz_type": "marketing_flow", "marketing_flow_id": > "marketing_flow_id", "mktgmsg_campaign_id": "mktgmsg_campaign_id"} {code} > > > BTW, use case when + if / ifnull also have something wrong. > > {code:java} > -- 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; > {code} > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (FLINK-31003) Flink SQL IF / CASE WHEN Funcation incorrect
[ https://issues.apache.org/jira/browse/FLINK-31003?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17687149#comment-17687149 ] weiqinpan commented on FLINK-31003: --- The field of marketing_flow_id is not null, but the result of below sql is empty. So unbelievable. {code:java} SELECT IF(`marketing_flow_id` IS NULL, '', `marketing_flow_id`) FROM source; {code} > Flink SQL IF / CASE WHEN Funcation incorrect > > > Key: FLINK-31003 > URL: https://issues.apache.org/jira/browse/FLINK-31003 > Project: Flink > Issue Type: Bug > Components: Table SQL / API >Affects Versions: 1.15.0, 1.15.1, 1.16.0, 1.15.2, 1.15.3, 1.16.1 >Reporter: weiqinpan >Priority: Major > > When I execute the below sql using sql-client,i found something wrong. > > {code:java} > 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;{code} > The demo.json data is > > {code:java} > {"mktgmsg_biz_type": "marketing_flow", "marketing_flow_id": > "marketing_flow_id", "mktgmsg_campaign_id": "mktgmsg_campaign_id"} {code} > > > BTW, use case when + if / ifnull also have something wrong. > > {code:java} > -- 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; > {code} > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (FLINK-31003) Flink SQL IF / CASE WHEN Funcation incorrect
[ https://issues.apache.org/jira/browse/FLINK-31003?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17686862#comment-17686862 ] weiqinpan commented on FLINK-31003: --- Only find this in Flink 1.15.0 +, everything is right before 1.15.0. > Flink SQL IF / CASE WHEN Funcation incorrect > > > Key: FLINK-31003 > URL: https://issues.apache.org/jira/browse/FLINK-31003 > Project: Flink > Issue Type: Bug > Components: Table SQL / API >Affects Versions: 1.15.0, 1.15.1, 1.16.0, 1.15.2, 1.15.3, 1.16.1 >Reporter: weiqinpan >Priority: Major > > When I execute the below sql using sql-client,i found something wrong. > > {code:java} > 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;{code} > The demo.json data is > > {code:java} > {"mktgmsg_biz_type": "marketing_flow", "marketing_flow_id": > "marketing_flow_id", "mktgmsg_campaign_id": "mktgmsg_campaign_id"} {code} > > > BTW, use case when + if / ifnull also have something wrong. > > {code:java} > -- 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; > {code} > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (FLINK-31003) Flink SQL IF / CASE WHEN Funcation incorrect
weiqinpan created FLINK-31003: - Summary: Flink SQL IF / CASE WHEN Funcation incorrect Key: FLINK-31003 URL: https://issues.apache.org/jira/browse/FLINK-31003 Project: Flink Issue Type: Bug Components: Table SQL / API Affects Versions: 1.16.1, 1.15.3, 1.15.2, 1.16.0, 1.15.1, 1.15.0 Reporter: weiqinpan When I execute the below sql using sql-client,i found something wrong. {code:java} 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;{code} The demo.json data is {code:java} {"mktgmsg_biz_type": "marketing_flow", "marketing_flow_id": "marketing_flow_id", "mktgmsg_campaign_id": "mktgmsg_campaign_id"} {code} BTW, use case when + if / ifnull also have something wrong. {code:java} -- 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; {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)