[jira] [Created] (FLINK-21584) Support UNNEST in LEFT JOINs

[Shang Yuanchun (Jira)]

Nico Kruber created FLINK-21584:
-----------------------------------

             Summary: Support UNNEST in LEFT JOINs
                 Key: FLINK-21584
                 URL: https://issues.apache.org/jira/browse/FLINK-21584
             Project: Flink
          Issue Type: New Feature
          Components: Table SQL / API
    Affects Versions: 1.12.1
            Reporter: Nico Kruber


Currently, UNNEST (for arrays and maps) is only supported in CROSS JOIN operations, but you may actually also want this in a LEFT JOIN fashion in which case you would get {{NULL}} values for the expanded fields.

h1. Example
{code:sql}
CREATE TEMPORARY VIEW input ( f1, f2 )
AS VALUES ('A', STR_TO_MAP('')), ('B', STR_TO_MAP('1, 2'));

SELECT * FROM input LEFT JOIN UNNEST(f2);
{code}

h1. Current workaround
{code:sql}
CREATE TEMPORARY VIEW input ( f1, f2 )
AS VALUES ('A', STR_TO_MAP('')), ('B', STR_TO_MAP('1, 2'));

SELECT * FROM input CROSS JOIN UNNEST(f2)
UNION ALL SELECT *, NULLIF('1', '1') AS `KEY`, NULLIF('1', '1') as `VALUE` FROM input WHERE f2 IS NULL OR CARDINALITY(f2) = 0;
{code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)