[ 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)