[jira] [Commented] (DRILL-7288) IndexOutOfBoundsException when coalesce(dir0,'')

["benj (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-7288?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16858714#comment-16858714 ] 

benj commented on DRILL-7288:
-----------------------------

Please note that when using a column instead constant there is no problem:
{code:java}
SELECT 1 AS col1
,CAST(CASE WHEN COALESCE(dir0,'abc')=COALESCE(dir0,'abc') THEN `columnDate` END AS DATE) AS record_date
FROM ....
LIMIT 2;
=> OK
{code}
unless adding * in the request:
{code:java}
SELECT 1 AS col1
,CAST(CASE WHEN COALESCE(dir0,'abc')=COALESCE(dir0,'abc') THEN `columnDate` END AS DATE) AS record_date
, * /* ADDING all the fields */
FROM ....
LIMIT 2;
=> Error: SYSTEM ERROR: NumberFormatException: abc
{code}
...

 

> IndexOutOfBoundsException when coalesce(dir0,'')
> ------------------------------------------------
>
>                 Key: DRILL-7288
>                 URL: https://issues.apache.org/jira/browse/DRILL-7288
>             Project: Apache Drill
>          Issue Type: Bug
>    Affects Versions: 1.16.0
>            Reporter: benj
>            Priority: Minor
>
> Example of request running in 1.15 and not in 1.16:
> {code:java}
> SELECT 1 AS col1
> ,CAST(CASE WHEN COALESCE(dir0,'')=COALESCE(dir0,'') THEN '2017-03-31' END AS DATE) AS record_date
> FROM ....
> LIMIT 2;
> {code}
> in 1.15 (it's OK):
> {code:java}
> +--------+-------------+
> | col1   | record_date |
> +--------+-------------+
> | 1      | 2017-03-31  |
> | 1      | 2017-03-31  |
> +--------+-------------+
> {code}
> in 1.16 (it's NOK):
> {code:java}
> Error: SYSTEM ERROR: IndexOutOfBoundsException: Index 1 out of bounds for length 0
> {code}
> Surprisingly, by removing at least one of the _coalesce_, it works (but the request won't produce expected result if dir0 is null)
> {code:java}
> SELECT 1 AS col1
> ,CAST(CASE WHEN dir0=dir0 THEN '2017-03-31' END AS DATE) AS record_date
> FROM ....
> LIMIT 2;
> => OK{code}
> Note that trick was used to force the mode to be NULLABLE. Fortunately, it is also possible to use more simply NULLIF (see below)
> {code:java}
> SELECT 1 AS col1
> ,CAST(NULLIF('2017-03-31','')AS DATE) AS record_date
> FROM ....
> LIMIT 2;
> {code}
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)