You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "Martijn Visser (Jira)" <ji...@apache.org> on 2023/04/11 09:29:00 UTC
[jira] [Updated] (FLINK-31760) COALESCE() with NULL arguments throws error
[ https://issues.apache.org/jira/browse/FLINK-31760?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Martijn Visser updated FLINK-31760:
-----------------------------------
Fix Version/s: (was: 1.6.4)
> COALESCE() with NULL arguments throws error
> -------------------------------------------
>
> Key: FLINK-31760
> URL: https://issues.apache.org/jira/browse/FLINK-31760
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / API
> Affects Versions: 1.17.0, 1.16.1, 1.18.0
> Environment: Flink 1.16.1
> Reporter: Mohsen Rezaei
> Priority: Major
> Fix For: 1.18.0, 1.17.1
>
>
> All arguments may not be nullable:
> {code}
> SELECT COALESCE(NULL, NULL) FROM UnnamedTable$0
> Exception in thread "main" org.apache.flink.table.api.ValidationException: SQL validation failed. From line 1, column 17 to line 1, column 20: Illegal use of 'NULL'
> at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:186)
> at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:113)
> at org.apache.flink.table.planner.operations.SqlToOperationConverter.convert(SqlToOperationConverter.java:261)
> at org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:106)
> at org.apache.flink.table.api.internal.TableEnvironmentImpl.sqlQuery(TableEnvironmentImpl.java:703)
> at CoalesceTest.main(CoalesceTest.java:58)
> Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 17 to line 1, column 20: Illegal use of 'NULL'
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:883)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:868)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4867)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1837)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1912)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:419)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4061)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3347)
> at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:997)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:975)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:952)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:704)
> at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:182)
> ... 5 more
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Illegal use of 'NULL'
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:560)
> ... 21 more
> {code}
> As [documented|https://nightlies.apache.org/flink/flink-docs-release-1.17/docs/dev/table/functions/systemfunctions/#conditional-functions], supports all nullable arguments:
> {quote}
> Returns the first argument that is not NULL.
> If all arguments are NULL, it returns NULL as well. The return type is the least restrictive, common type of all of its arguments. The return type is nullable if all arguments are nullable as well.
> {quote}
> Spark SQL supports as documented:
> {code}
> Welcome to
> ____ __
> / __/__ ___ _____/ /__
> _\ \/ _ \/ _ `/ __/ '_/
> /___/ .__/\_,_/_/ /_/\_\ version 3.3.2
> /_/
>
> Using Scala version 2.12.15 (OpenJDK 64-Bit Server VM, Java 11.0.16)
> Type in expressions to have them evaluated.
> Type :help for more information.
> scala> spark.sql("select coalesce(null, null)").show
> +--------------------+
> |coalesce(NULL, NULL)|
> +--------------------+
> | null|
> +--------------------+
> {code}
> Since this is coming back from Calcite, do we want to support all nullable arguments? If no, then this will become a simple documentation fix.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)