[jira] [Created] (FLINK-31760) COALESCE() with NULL arguments throws error

["Mohsen Rezaei (Jira)" <ji...@apache.org>]

Mohsen Rezaei created FLINK-31760:
-------------------------------------

             Summary: 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.16.1, 1.17.0, 1.18.0
         Environment: Flink 1.16.1
            Reporter: Mohsen Rezaei
             Fix For: 1.18.0, 1.17.1, 1.6.4


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)