[jira] [Created] (FLINK-32642) The upsert mode doesn't work for the compound keys
jasonliangyc created FLINK-32642:
Summary: The upsert mode doesn't work for the compound keys
Key: FLINK-32642
URL: https://issues.apache.org/jira/browse/FLINK-32642
Project: Flink
Issue Type: Bug
Components: Table SQL / Client
Reporter: jasonliangyc
Attachments: image-2023-07-21-23-55-47-399.png,
image-2023-07-21-23-56-56-543.png, image-2023-07-21-23-57-22-186.png,
image-2023-07-22-00-11-56-363.png
Hi, the issue can be produced by following below steps:
*1.* Create two tables in sqlserver, one is the sink table, the other one is
cdc source table, the sink table has a compound key(date_str,time_str).
*2.* Create the corresponding flink tables in sql-client.
{code:java}
--create sink table in sqlserver
CREATE TABLE cumulative_cnt (
date_str VARCHAR(50),
time_str VARCHAR(50),
cnt INTEGER
CONSTRAINT PK_cumulative_cnt PRIMARY KEY (date_str,time_str)
);
--create source cdc table in sqlserver
CREATE TABLE user_behavior (
id INTEGER NOT NULL IDENTITY(101,1) PRIMARY KEY,
create_date datetime NOT NULL,
click_event VARCHAR(255) NOT NULL
);
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'user_behavior',
@role_name = NULL,
@supports_net_changes = 1
GO
--create flink tables through sql-client
CREATE TABLE cumulative_cnt (
date_str STRING,
time_str STRING,
cnt BIGINT,
PRIMARY KEY (date_str, time_str)NOT ENFORCED
) WITH (
'connector' = 'jdbc',
'url' = 'jdbc:sqlserver://:1433;databaseName=',
'username' = '',
'password' = '',
'table-name' = 'cumulative_cnt'
);
--create flink cdc table through sql-client
CREATE TABLE user_behavior (
id int,
create_date TIMESTAMP(0),
click_event STRING
) WITH (
'connector' = 'sqlserver-cdc',
'hostname' = '',
'port' = '1433',
'username' = '',
'password' = '',
'database-name' = '',
'schema-name' = 'dbo',
'table-name' = 'user_behavior'
); {code}
*3.* Run below sql through sql-client to start the job for capturing the cdc
data and do the aggregation and finally insert the result into target table.
{code:java}
insert into cumulative_cnt
select
date_str,
max(time_str) as time_str,
count(*) as cnt
from
(
select
DATE_FORMAT(create_date, '-MM-dd') as date_str,
SUBSTR(DATE_FORMAT(create_date, 'HH:mm'),1,4) || '0' as time_str
from user_behavior
) group by date_str;
{code}
*4.* Insert two records for testing.
{code:java}
INSERT INTO user_behavior(create_date, click_event)VALUES ('2023-06-01
01:01:00','click1');
INSERT INTO user_behavior(create_date, click_event)VALUES ('2023-06-01
02:20:00','click1');{code}
*5.* Checked the result in db( pls see the screen 1) and found that the target
table only have one record, but it is not the expectation cause the two source
records have different time, thus the compound key(date_str, time_str) shoud
be different( pls see the screen 2 )
There should be two records in the target table:
2023-06-01 01:00 1
2023-06-01 02:20 2
screen 1
!image-2023-07-22-00-11-56-363.png|width=274,height=199!
screen 2
!image-2023-07-21-23-57-22-186.png|width=572,height=120!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (FLINK-32587) The results returned from the CDC sql query are null or the value was changed unexpectly
jasonliangyc created FLINK-32587:
Summary: The results returned from the CDC sql query are null or
the value was changed unexpectly
Key: FLINK-32587
URL: https://issues.apache.org/jira/browse/FLINK-32587
Project: Flink
Issue Type: Bug
Components: Table SQL / Client
Affects Versions: 1.17.1, 1.17.0
Reporter: jasonliangyc
Attachments: image-2023-07-13-17-35-32-235.png,
image-2023-07-13-17-37-56-908.png
I created a CDC table as below and then run the query 'select * from so_cdc'
through sql-client, it gives me the unexpected results.
{code:java}
CREATE TABLE so_cdc (
REC_ID STRING,
Create_Date TIMESTAMP(3),
PRIMARY KEY (REC_ID) NOT ENFORCED
) WITH (
'connector' = 'sqlserver-cdc',
'hostname' = '',
'port' = '',
'username' = 'xxx',
'password' = '',
'database-name' = '',
'schema-name' = '',
'table-name' = 'xxx',
'scan.startup.mode' = 'latest-offset'
); {code}
Run the query for the first time, the data look normal.
!image-2023-07-13-17-35-32-235.png|width=535,height=141!
But after i run the same query multiple times, it gives me the unexpected data,
and i'm sure that these two columns of my cdc source table don't contain these
data
!image-2023-07-13-17-37-56-908.png|width=469,height=175!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (FLINK-32579) The filter criteria on the lookup table of Lookup join has no effect
jasonliangyc created FLINK-32579:
Summary: The filter criteria on the lookup table of Lookup join
has no effect
Key: FLINK-32579
URL: https://issues.apache.org/jira/browse/FLINK-32579
Project: Flink
Issue Type: Bug
Components: Table SQL / Client
Affects Versions: 1.17.1, 1.17.0
Reporter: jasonliangyc
Attachments: image-2023-07-12-09-31-18-261.png,
image-2023-07-12-09-42-59-231.png, image-2023-07-12-09-47-31-397.png
*1.* I joined two tables using the lookup join as below query in sql-client,
the filter criteria of (p.name = '??') didn't shows up in the execution
detail and it returned the rows only base on one condiction (cdc.product_id =
p.id)
{code:java}
select
cdc.order_id,
cdc.order_date,
cdc.customer_name,
cdc.price,
p.name
FROM orders AS cdc
left JOIN products
FOR SYSTEM_TIME AS OF cdc.proc_time as p ON p.name = '??' and
cdc.product_id = p.id
; {code}
!image-2023-07-12-09-31-18-261.png|width=657,height=132!
*2.* It showed the werid results when i changed the query as below, cause there
were no data in the table(products) that the value of column 'name' is '??'
and and execution detail didn't show us the where criteria.
{code:java}
select
cdc.order_id,
cdc.order_date,
cdc.customer_name,
cdc.price,
p.name
FROM orders AS cdc
left JOIN products
FOR SYSTEM_TIME AS OF cdc.proc_time as p ON cdc.product_id = p.id
where p.name = '??'
; {code}
!image-2023-07-12-09-42-59-231.png|width=684,height=102!
!image-2023-07-12-09-47-31-397.png|width=685,height=120!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
