Moritz Manner created FLINK-40901:
-------------------------------------
Summary: JSON functions return the result of a previous row when
JSON_TYPE or JSON_LENGTH is skipped
Key: FLINK-40901
URL: https://issues.apache.org/jira/browse/FLINK-40901
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Reporter: Moritz Manner
JSON_TYPE and JSON_LENGTH share the parsed input if they are called on the same
column, but only the first call in the generated code parses it. If that call
isn't evaluated for a row, the following calls use the input that was parsed
for an earlier row. This happens, for example, when the first call is in a CASE
branch that isn't taken, or behind an OR that short-circuits. If there is no
earlier row, they return NULL.
JSON_VALUE and JSON_QUERY on the same column are affected as well if a
JSON_TYPE or JSON_LENGTH call comes before them.
The result is wrong, and there is no error.
h3. Reproduce
JSON_LENGTH returns the length of the previous row:
{code:sql}
SELECT id, CASE WHEN id > 1 THEN JSON_TYPE(j) END, JSON_LENGTH(j)
FROM (VALUES (2, '[1,2,3]'), (1, '[1]')) AS t(id, j);
{code}
||id||EXPR$1||EXPR$2||
|2|array|3|
|1|NULL|3|
The JSON_LENGTH of the second row should be 1.
JSON_TYPE returns the type of the previous row:
{code:sql}
SELECT id, CASE WHEN id > 1 THEN JSON_LENGTH(j) END, JSON_TYPE(j)
FROM (VALUES (2, '[1,2,3]'), (1, '{"a":1}')) AS t(id, j);
{code}
||id||EXPR$1||EXPR$2||
|2|3|array|
|1|NULL|array|
The JSON_TYPE of the second row should be object.
JSON_VALUE returns the value of the previous row:
{code:sql}
SELECT id, CASE WHEN id > 1 THEN JSON_TYPE(j) END, JSON_VALUE(j, '$.a')
FROM (VALUES (2, '{"a":"x"}'), (1, '{"a":"y"}')) AS t(id, j);
{code}
||id||EXPR$1||EXPR$2||
|2|object|x|
|1|NULL|x|
The JSON_VALUE of the second row should be y.
JSON_QUERY returns the value of the previous row:
{code:sql}
SELECT id, CASE WHEN id > 1 THEN JSON_TYPE(j) END, JSON_QUERY(j, '$.a')
FROM (VALUES (2, '{"a":[1]}'), (1, '{"a":[2]}')) AS t(id, j);
{code}
||id||EXPR$1||EXPR$2||
|2|object|[1]|
|1|NULL|[1]|
The JSON_QUERY of the second row should be [2].
The same happens in a WHERE clause:
{code:sql}
SELECT id, JSON_LENGTH(j)
FROM (VALUES (2, '[1,2,3]'), (1, '[1]')) AS t(id, j)
WHERE id > 1 OR JSON_TYPE(j) = 'array';
{code}
This returns (2, NULL) and (1, 1). It should return (2, 3) and (1, 1).
h3. Notes
* Only the first JSON function call on a column decides this. If it is
JSON_VALUE or JSON_QUERY, the results are correct, even if that call is skipped.
* If the call outside the CASE comes before the CASE, the result is correct.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
