You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "Gustavo de Morais (Jira)" <ji...@apache.org> on 2025/02/07 14:31:00 UTC
[jira] [Commented] (FLINK-21584) Support UNNEST in LEFT JOINs
[ https://issues.apache.org/jira/browse/FLINK-21584?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17924911#comment-17924911 ]
Gustavo de Morais commented on FLINK-21584:
-------------------------------------------
Unnest has support for left join: [https://github.com/apache/flink/pull/8578/files#diff-f7077699468e08abe805cedcdeac9f5f814801adc9d2d86168672fefa63bff28R147.] This can be closed?
> 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
> Priority: Not a Priority
> Labels: auto-deprioritized-major, auto-deprioritized-minor
>
> 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}
> This currently results in the following exception:
> {code}
> org.apache.flink.table.api.ValidationException: SQL validation failed. From line 1, column 26 to line 1, column 29: INNER, LEFT, RIGHT or FULL join requires a condition (NATURAL keyword or ON or USING clause)
> at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:152) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:111) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.operations.SqlToOperationConverter.convert(SqlToOperationConverter.java:189) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:77) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.gateway.local.LocalExecutor$1.lambda$parse$0(LocalExecutor.java:336) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.gateway.local.ExecutionContext.wrapClassLoader(ExecutionContext.java:256) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.gateway.local.LocalExecutor$1.parse(LocalExecutor.java:336) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.cli.SqlCommandParser.parseBySqlParser(SqlCommandParser.java:99) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> ... 6 more
> Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 26 to line 1, column 29: INNER, LEFT, RIGHT or FULL join requires a condition (NATURAL keyword or ON or USING clause)
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_282]
> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_282]
> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_282]
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_282]
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:883) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:868) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5043) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3333) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.calcite.FlinkCalciteSqlValidator.validateJoin(FlinkCalciteSqlValidator.java:100) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3196) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3461) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1067) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1041) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1016) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:724) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:147) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:111) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.operations.SqlToOperationConverter.convert(SqlToOperationConverter.java:189) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:77) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.gateway.local.LocalExecutor$1.lambda$parse$0(LocalExecutor.java:336) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.gateway.local.ExecutionContext.wrapClassLoader(ExecutionContext.java:256) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.gateway.local.LocalExecutor$1.parse(LocalExecutor.java:336) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.cli.SqlCommandParser.parseBySqlParser(SqlCommandParser.java:99) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> ... 6 more
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: INNER, LEFT, RIGHT or FULL join requires a condition (NATURAL keyword or ON or USING clause)
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_282]
> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_282]
> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_282]
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_282]
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:560) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:883) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:868) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5043) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3333) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.calcite.FlinkCalciteSqlValidator.validateJoin(FlinkCalciteSqlValidator.java:100) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3196) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3461) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1067) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1041) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1016) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:724) ~[flink-table_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:147) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:111) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.operations.SqlToOperationConverter.convert(SqlToOperationConverter.java:189) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:77) ~[flink-table-blink_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.gateway.local.LocalExecutor$1.lambda$parse$0(LocalExecutor.java:336) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.gateway.local.ExecutionContext.wrapClassLoader(ExecutionContext.java:256) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.gateway.local.LocalExecutor$1.parse(LocalExecutor.java:336) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> at org.apache.flink.table.client.cli.SqlCommandParser.parseBySqlParser(SqlCommandParser.java:99) ~[flink-sql-client_2.12-1.12.1.jar:1.12.1]
> ... 6 more
> {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.20.10#820010)