[
https://issues.apache.org/jira/browse/DRILL-6962?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16746340#comment-16746340
]
benj commented on DRILL-6962:
-----------------------------
Exact, the correct syntax (that works) is :
{code:java}
SELECT COALESCE(CAST(NULL AS int), CAST(NULL AS int)) ex;
| ex |
+-------+
| null |{code}
In reality, and in a strange way, only one of the NULL passed in the COALESCE
have to be CAST so
{code:java}
SELECT COALESCE(NULL, CAST(NULL AS int)); OK
SELECT COALESCE(CAST(NULL AS int), NULL); OK
{code}
I missed the point of the type cast because I was confused by the error message
that maybe should have been "+Illegal use of 'NULL'+" like in
{code:java}
SELECT NULL;
Error: VALIDATION ERROR: From line 1, column 8 to line 1, column 11: Illegal
use of 'NULL'
{code}
And in a last point for general knowledge :
* Why the version of COALESCE with non typed NULL works
* Why if the COALESCE version with only one defined type works, the next
version doesn't work
{code:java}
SELECT CAST(COALESCE(NULL, NULL) AS int);
Error: VALIDATION ERROR: From line 1, column 13 to line 1, column 32: ELSE
clause or at least one THEN clause must be non-NULL
{code}
> Function coalesce returns an Error when none of the columns in coalesce exist
> in a parquet file
> -----------------------------------------------------------------------------------------------
>
> Key: DRILL-6962
> URL: https://issues.apache.org/jira/browse/DRILL-6962
> Project: Apache Drill
> Issue Type: Improvement
> Affects Versions: 1.13.0
> Reporter: Bohdan Kazydub
> Assignee: Bohdan Kazydub
> Priority: Major
> Fix For: 1.16.0
>
>
> As Drill is schema-free, COALESCE function is expected to return a result and
> not error out even if none of the columns being referred to exists in files
> being queried.
> Here is an example for 2 columns, `unk_col` and `unk_col2`, which do not
> exist in the parquet files
> {code:java}
> select coalesce(unk_col, unk_col2) from dfs.`/tmp/parquetfiles`;
> java.lang.IndexOutOfBoundsException: index (0) must be less than size (0)
> at
> org.apache.drill.shaded.guava.com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:1196)
> Fragment 1:0
> [Error Id: 7b9193fb-289b-4fbf-a52a-2b93b01f0cd0 on dkvm2c:31010]
> (state=,code=0)
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
