[
https://issues.apache.org/jira/browse/FLINK-21584?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17924913#comment-17924913
]
Gustavo de Morais commented on FLINK-21584:
-------------------------------------------
Reading the logs reported by [~nkruber], the issue for the example query is
something else. LEFT JOINS require a condition (ON ...) which was missing in
the query and thus the error reported. This is expected and correct (y)
> 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)
