You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "pingle wang (JIRA)" <ji...@apache.org> on 2019/07/25 09:33:00 UTC
[jira] [Updated] (FLINK-13425) Table api sqlUpdate execute case
when expression occur data error
[ https://issues.apache.org/jira/browse/FLINK-13425?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
pingle wang updated FLINK-13425:
--------------------------------
Description:
The flink job running in our online environment, from the beginning of the 1st to the 23rd, found that the data written to mysql on the 17th has abnormal changes, such as the case when the converted constant value has extra space to write.
flink execute sql :
{code:java}
insert into
date_pos_area_version_source
select
Date_Format(receive_time, _UTF-16LE'yyyyMMdd') AS `date`,
(
case
when country_id is null
or country_id = 10184 then 'mainland'
when country_id = 10239 then 'hongkong'
when country_id = 10248 then 'taiwan'
when country_id = 10257 then 'macau'
else 'overseas'
end
) as big_area,
sum(ad_action) as click_count
from
date_pos_area_version_source_view
where
ad_position_id is not null
group by
Date_Format(receive_time, _UTF-16LE'yyyyMMdd'),
(case
when country_id is null
or country_id = 10184 then 'mainland'
when country_id = 10239 then 'hongkong'
when country_id = 10248 then 'taiwan'
when country_id = 10257 then 'macau'
else 'overseas'
end
){code}
flink graph:
!image-2019-07-25-17-01-26-076.png!
mysql result like :
{code:java}
9ab1c5afa4946ca0040271736f38c83a hongkong 20190717
0acfa9f5133f5b558e4642ce0870ea77 macau 20190717
cc571067754687a72ee0e8d224c6115a mainland 20190717
adb9f8b618195e195c90b09815a94842 overseas 20190717
aff685603b0f02debc8329a1dc7905d0 taiwan 20190717
9ab1c5afa4946ca0040271736f38c83a hongkong 20190630
9690a92f29519fbfef104011784221e7 macau 20190630
cc571067754687a72ee0e8d224c6115a mainland 20190630
adb9f8b618195e195c90b09815a94842 overseas 20190630
31779ba135934ed036644deb47eb1e54 taiwan 20190630
{code}
!image-2019-07-25-16-50-02-632.png!
was:
The flink job running in our online environment, from the beginning of the 1st to the 23rd, found that the data written to mysql on the 17th has abnormal changes, such as the case when the converted constant value has extra space to write.
flink execute sql :
{code:java}
insert into
date_pos_area_version_source
select
Date_Format(receive_time, _UTF-16LE'yyyyMMdd') AS `date`,
(
case
when country_id is null
or country_id = 10184 then 'mainland'
when country_id = 10239 then 'hongkong'
when country_id = 10248 then 'taiwan'
when country_id = 10257 then 'macau'
else 'overseas'
end
) as big_area,
sum(ad_action) as click_count
from
date_pos_area_version_source_view
where
ad_position_id is not null
group by Date_Format(receive_time, _UTF-16LE'yyyyMMdd'), big_area{code}
flink graph:
!image-2019-07-25-17-01-26-076.png!
mysql result like :
{code:java}
9ab1c5afa4946ca0040271736f38c83a hongkong 20190717
0acfa9f5133f5b558e4642ce0870ea77 macau 20190717
cc571067754687a72ee0e8d224c6115a mainland 20190717
adb9f8b618195e195c90b09815a94842 overseas 20190717
aff685603b0f02debc8329a1dc7905d0 taiwan 20190717
9ab1c5afa4946ca0040271736f38c83a hongkong 20190630
9690a92f29519fbfef104011784221e7 macau 20190630
cc571067754687a72ee0e8d224c6115a mainland 20190630
adb9f8b618195e195c90b09815a94842 overseas 20190630
31779ba135934ed036644deb47eb1e54 taiwan 20190630
{code}
!image-2019-07-25-16-50-02-632.png!
> Table api sqlUpdate execute case when expression occur data error
> -----------------------------------------------------------------
>
> Key: FLINK-13425
> URL: https://issues.apache.org/jira/browse/FLINK-13425
> Project: Flink
> Issue Type: Improvement
> Components: Table SQL / API, Table SQL / Planner
> Affects Versions: 1.6.3, 1.7.2
> Reporter: pingle wang
> Priority: Major
> Attachments: image-2019-07-25-16-50-02-632.png, image-2019-07-25-17-01-26-076.png
>
>
> The flink job running in our online environment, from the beginning of the 1st to the 23rd, found that the data written to mysql on the 17th has abnormal changes, such as the case when the converted constant value has extra space to write.
> flink execute sql :
>
> {code:java}
> insert into
> date_pos_area_version_source
> select
> Date_Format(receive_time, _UTF-16LE'yyyyMMdd') AS `date`,
> (
> case
> when country_id is null
> or country_id = 10184 then 'mainland'
> when country_id = 10239 then 'hongkong'
> when country_id = 10248 then 'taiwan'
> when country_id = 10257 then 'macau'
> else 'overseas'
> end
> ) as big_area,
> sum(ad_action) as click_count
> from
> date_pos_area_version_source_view
> where
> ad_position_id is not null
> group by
> Date_Format(receive_time, _UTF-16LE'yyyyMMdd'),
> (case
> when country_id is null
> or country_id = 10184 then 'mainland'
> when country_id = 10239 then 'hongkong'
> when country_id = 10248 then 'taiwan'
> when country_id = 10257 then 'macau'
> else 'overseas'
> end
> ){code}
> flink graph:
> !image-2019-07-25-17-01-26-076.png!
> mysql result like :
> {code:java}
> 9ab1c5afa4946ca0040271736f38c83a hongkong 20190717
> 0acfa9f5133f5b558e4642ce0870ea77 macau 20190717
> cc571067754687a72ee0e8d224c6115a mainland 20190717
> adb9f8b618195e195c90b09815a94842 overseas 20190717
> aff685603b0f02debc8329a1dc7905d0 taiwan 20190717
> 9ab1c5afa4946ca0040271736f38c83a hongkong 20190630
> 9690a92f29519fbfef104011784221e7 macau 20190630
> cc571067754687a72ee0e8d224c6115a mainland 20190630
> adb9f8b618195e195c90b09815a94842 overseas 20190630
> 31779ba135934ed036644deb47eb1e54 taiwan 20190630
> {code}
> !image-2019-07-25-16-50-02-632.png!
>
>
>
>
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)