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)