[FLINK-32296] Flink SQL handle array of row incorrectly

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.15.3, 1.16.2, 1.17.1
• Fix Version/s: 1.18.0, 1.16.3, 1.17.2, 1.19.0
• Component/s: Table SQL / API
• Labels: pull-request-available
• Assignee: Sergey Nuyanzin
• Reporter: Lim Qing Wei
• Created: 09/Jun/23 09:01
• Updated: 25/Oct/23 10:01
• Resolved: 23/Aug/23 19:26

Description

FlinkSQL produce incorrect result when involving data with type of ARRAY<ROW>, here's a reproduction:

 

 

CREATE TEMPORARY VIEW bug_data as (
SELECT CAST(ARRAY[
(10, '2020-01-10'), (101, '244ddf'), (1011, '2asdfaf'), (1110, '200'), (2210, '20-01-10'), (4410, '21111')
] AS ARRAY<ROW<A INT, B STRING>>)
UNION
SELECT CAST(ARRAY[
(10, '2020-01-10'), (121, '244ddf'), (2222, '2asdfaf'), (32243, '200'), (2210, '33333-01-10'), (4410, '23243243')
] AS ARRAY<ROW<A INT, B STRING>>)
UNION SELECT CAST(ARRAY[
(10, '2020-01-10'), (222, '244ddf'), (1011, '2asdfaf'), (1110, '200'), (24367, '20-01-10'), (4410, '21111')
] AS ARRAY<ROW<A INT, B STRING>>)
UNION SELECT CAST(ARRAY[
(10, '2020-01-10'), (5666, '244ddf'), (435243, '2asdfaf'), (56567, '200'), (2210, '20-01-10'), (4410, '21111')
] AS ARRAY<ROW<A INT, B STRING>>)
UNION SELECT CAST(ARRAY[
(10, '2020-01-10'), (43543, '244ddf'), (1011, '2asdfaf'), (1110, '200'), (8967564, '20-01-10'), (4410, '21111')
] AS ARRAY<ROW<A INT, B STRING>>)
);

CREATE TABLE sink (
r ARRAY<ROW<A INT, B STRING>>
) WITH ('connector' = 'print'); 

 

 

In all 1.15. 1.16 and 1.17 version I've tested, it produces the following:

 

[+I[4410, 21111], +I[4410, 21111], +I[4410, 21111], +I[4410, 21111], +I[4410, 21111], +I[4410, 21111]]

[+I[4410, 23243243], +I[4410, 23243243], +I[4410, 23243243], +I[4410, 23243243], +I[4410, 23243243], +I[4410, 23243243]]

 

 

I think this is unexpected/wrong because:

1. The query should produce 5 rows, not 2
2. The data is also wrong, noticed it just make every row in the array the same, but the input are not the same.

 

Issue Links

• is duplicated by: FLINK-31778 Casting array of rows produces incorrect result
• is duplicated by: FLINK-32897 RowToRowCastRule will get wrong result while cast Array(Row()) type
• links to: GitHub Pull Request #22923