You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "Flink Jira Bot (Jira)" <ji...@apache.org> on 2021/10/28 10:41:01 UTC
[jira] [Updated] (FLINK-21584) Support UNNEST in LEFT JOINs
[ https://issues.apache.org/jira/browse/FLINK-21584?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Flink Jira Bot updated FLINK-21584:
-----------------------------------
Labels: auto-deprioritized-major stale-minor (was: auto-deprioritized-major)
I am the [Flink Jira Bot|https://github.com/apache/flink-jira-bot/] and I help the community manage its development. I see this issues has been marked as Minor but is unassigned and neither itself nor its Sub-Tasks have been updated for 180 days. I have gone ahead and marked it "stale-minor". If this ticket is still Minor, please either assign yourself or give an update. Afterwards, please remove the label or in 7 days the issue will be deprioritized.
> 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: Minor
> Labels: auto-deprioritized-major, stale-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.3.4#803005)