[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)
