You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by "Teunissen, F.G.J. (Fred) via user" <us...@flink.apache.org> on 2026/04/01 13:23:10 UTC
[QUESTION] Nested Row Nullability Issue in Flink SQL 2.2.0 - COALESCE with NULL Nested Rows
Hello Flink community,
I'm experiencing an issue with nested row handling in **Flink SQL 2.2.0** that doesn't occur in earlier versions (1.20.3, 2.1.1).
Problem Description
When performing a LEFT JOIN with a lookup table containing nested ROW fields, the `COALESCE()` function returns empty/null values instead of the fallback value in Flink 2.2.0.
Expected Behavior (Flink 1.20.3, 2.1.1):
- When `lookup.httpcode500.error.message` is NULL, `COALESCE()` returns 'no error'
- When `lookup.httpcode500.error.message` has a value, it returns that value
Actual Behavior (Flink 2.2.0):
- When `lookup.httpcode500.error.message` is NULL, `COALESCE()` returns empty string instead of 'no error'
Workaround
Enabling `table.legacy-nested-row-nullability' = 'true'` fixes the issue, but I'd prefer to find a solution compatible with 2.2.0's new nullability handling.
Minimal Reproducible Example
Query:
SELECT
id,
`value`,
i.type,
subtype,
COALESCE(lookup.httpcode500.error.message, 'no error') as message
FROM input AS i
LEFT JOIN lookup
ON MOD(i.type, 5) = lookup.type;
Lookup Table Schema:
CREATE TABLE lookup (
type INTEGER,
subtype BIGINT,
httpcode500 ROW<
`error` ROW<
code BIGINT NOT NULL,
message STRING NOT NULL
> NOT NULL
>,
PRIMARY KEY (type) NOT ENFORCED
) WITH (
'connector' = 'filesystem',
'path' = 'file:///data/lookup',
'format' = 'avro'
);
Test Data:
type | subtype | httpcode500
-----|---------|-------------------------------------------
0 | 100 | {"error": {"code": 500, "message": "Internal Server Error"}}
1 | 200 | NULL
2 | NULL | {"error": {"code": 500, "message": "Internal Server Error"}}
3 | NULL | NULL
Input Table Schema:
CREATE TABLE input (
id STRING,
`value` BIGINT,
type INTEGER,
ingest_time TIMESTAMP(3),
WATERMARK FOR ingest_time AS ingest_time - INTERVAL '0.5' SECOND
) WITH (
'connector' = 'datagen',
'rows-per-second' = '1',
'number-of-rows' = '5',
'fields.id.kind' = 'sequence',
'fields.id.start' = '0',
'fields.id.end' = '14',
'fields.value.min' = '1000',
'fields.value.max' = '2000',
'fields.type.kind' = 'sequence',
'fields.type.start' = '0',
'fields.type.end' = '14'
);
Results Comparison
Flink 2.2.0 (without legacy option) - UNEXPECTED:
id | value | type | subtype | message
---|-------|------|---------|------------------
0 | 1983 | 0 | NULL | [EMPTY] ← Should be "no error"
0 | 1983 | 0 | 100 | Internal Server Error
1 | 1992 | 1 | 200 | [EMPTY] ← Should be "no error"
2 | 1922 | 2 | NULL | Internal Server Error
3 | 1912 | 3 | NULL | [EMPTY] ← Should be "no error"
4 | 1685 | 4 | NULL | [EMPTY] ← Should be "no error"
Flink 2.1.1 or 2.2.0 (with legacy option) - EXPECTED:
id | value | type | subtype | message
---|-------|------|---------|------------------
0 | 1529 | 0 | NULL | no error
0 | 1529 | 0 | 100 | Internal Server Error
1 | 1675 | 1 | 200 | no error
2 | 1862 | 2 | NULL | Internal Server Error
3 | 1271 | 3 | NULL | no error
4 | 1629 | 4 | NULL | no error
Environment
- Flink Version: 2.2.0 (issue), 1.20.3 and 2.1.1 (working)
Questions
1. Is this a known issue in Flink 2.2.0?
2. Is there a recommended fix/workaround other than enabling `table.legacy-nested-row-nullability`?
3. Should I report this as a bug in Jira?
Any guidance would be appreciated!
Best regards,
Fred Teunissen
-----------------------------------------------------------------
ATTENTION:
The information in this e-mail is confidential and only meant for the intended recipient. If you are not the intended recipient, don't use or disclose it in any way. Please let the sender know and delete the message immediately.
-----------------------------------------------------------------
Re: [QUESTION] Nested Row Nullability Issue in Flink SQL 2.2.0 - COALESCE with NULL Nested Rows
Posted by Weiqing Yang <ya...@gmail.com>.
Hi Fred,
Thanks for providing the clear repro!
It looks like this is a known bug that has already been fixed on the master
branch: https://issues.apache.org/jira/browse/FLINK-39695 (worth
double-checking to make sure it matches your case).
It's targeted for 2.4.0 and hasn't been backported to 2.2.x/2.3.x, so on
2.2.0 you'll still need to stick with a workaround for now.
Best,
Weiqing
On Wed, Apr 1, 2026 at 6:23 AM Teunissen, F.G.J. (Fred) via user <
user@flink.apache.org> wrote:
> Hello Flink community,
>
> I'm experiencing an issue with nested row handling in **Flink SQL 2.2.0**
> that doesn't occur in earlier versions (1.20.3, 2.1.1).
>
> Problem Description
> When performing a LEFT JOIN with a lookup table containing nested ROW
> fields, the `COALESCE()` function returns empty/null values instead of the
> fallback value in Flink 2.2.0.
>
> *Expected Behavior (Flink 1.20.3, 2.1.1):*
> - When `lookup.httpcode500.error.message` is NULL, `COALESCE()` returns
> 'no error'
> - When `lookup.httpcode500.error.message` has a value, it returns that
> value
>
> *Actual Behavior (Flink 2.2.0):*
> - When `lookup.httpcode500.error.message` is NULL, `COALESCE()` returns
> empty string instead of 'no error'
>
> Workaround
> Enabling `table.legacy-nested-row-nullability' = 'true'` fixes the issue,
> but I'd prefer to find a solution compatible with 2.2.0's new nullability
> handling.
>
> Minimal Reproducible Example
> *Query:*
>
> SELECT
> id,
> `value`,
> i.type,
> subtype,
> COALESCE(lookup.httpcode500.error.message, 'no error') as message
> FROM input AS i
> LEFT JOIN lookup
> ON MOD(i.type, 5) = lookup.type;
>
> *Lookup Table Schema:*
>
> CREATE TABLE lookup (
> type INTEGER,
> subtype BIGINT,
> httpcode500 ROW<
> `error` ROW<
> code BIGINT NOT NULL,
> message STRING NOT NULL
> > NOT NULL
> >,
> PRIMARY KEY (type) NOT ENFORCED
> ) WITH (
> 'connector' = 'filesystem',
> 'path' = 'file:///data/lookup',
> 'format' = 'avro'
> );
>
>
> *Test Data:*
>
> type | subtype | httpcode500
> -----|---------|-------------------------------------------
> 0 | 100 | {"error": {"code": 500, "message": "Internal Server
> Error"}}
> 1 | 200 | NULL
> 2 | NULL | {"error": {"code": 500, "message": "Internal Server
> Error"}}
> 3 | NULL | NULL
>
>
> *Input Table Schema:*
>
> CREATE TABLE input (
> id STRING,
> `value` BIGINT,
> type INTEGER,
> ingest_time TIMESTAMP(3),
> WATERMARK FOR ingest_time AS ingest_time - INTERVAL '0.5' SECOND
> ) WITH (
> 'connector' = 'datagen',
> 'rows-per-second' = '1',
> 'number-of-rows' = '5',
> 'fields.id.kind' = 'sequence',
> 'fields.id.start' = '0',
> 'fields.id.end' = '14',
> 'fields.value.min' = '1000',
> 'fields.value.max' = '2000',
> 'fields.type.kind' = 'sequence',
> 'fields.type.start' = '0',
> 'fields.type.end' = '14'
> );
>
>
> Results Comparison
> *Flink 2.2.0 (without legacy option) - UNEXPECTED:*
>
> id | value | type | subtype | message
> ---|-------|------|---------|------------------
> 0 | 1983 | 0 | NULL | [EMPTY] ← Should be "no error"
> 0 | 1983 | 0 | 100 | Internal Server Error
> 1 | 1992 | 1 | 200 | [EMPTY] ← Should be "no error"
> 2 | 1922 | 2 | NULL | Internal Server Error
> 3 | 1912 | 3 | NULL | [EMPTY] ← Should be "no error"
> 4 | 1685 | 4 | NULL | [EMPTY] ← Should be "no error"
>
> *Flink 2.1.1 or 2.2.0 (with legacy option) - EXPECTED*:
>
> id | value | type | subtype | message
> ---|-------|------|---------|------------------
> 0 | 1529 | 0 | NULL | no error
> 0 | 1529 | 0 | 100 | Internal Server Error
> 1 | 1675 | 1 | 200 | no error
> 2 | 1862 | 2 | NULL | Internal Server Error
> 3 | 1271 | 3 | NULL | no error
> 4 | 1629 | 4 | NULL | no error
>
>
> Environment
> - *Flink Version*: 2.2.0 (issue), 1.20.3 and 2.1.1 (working)
>
>
> Questions
> 1. Is this a known issue in Flink 2.2.0?
> 2. Is there a recommended fix/workaround other than enabling
> `table.legacy-nested-row-nullability`?
> 3. Should I report this as a bug in Jira?
>
> Any guidance would be appreciated!
>
> Best regards,
> Fred Teunissen
>
> -----------------------------------------------------------------
> ATTENTION:
> The information in this e-mail is confidential and only meant for the intended recipient. If you are not the intended recipient, don't use or disclose it in any way. Please let the sender know and delete the message immediately.
> -----------------------------------------------------------------
>
>