Mailing-List: contact issues-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
Date: Tue, 8 Dec 2015 00:13:11 +0000 (UTC)
From: "Victoria Markman (JIRA)" <jira@apache.org>
To: issues@drill.apache.org
Message-ID: <JIRA.12919886.1449530342000.295771.1449533591296@Atlassian.JIRA>
In-Reply-To: <JIRA.12919886.1449530342000@Atlassian.JIRA>
References: <JIRA.12919886.1449530342000@Atlassian.JIRA>
 <JIRA.12919886.1449530342655@arcas>
Subject: [jira] [Commented] (DRILL-4166) Inconsistent return type from
 COALESCE function with views and parquet data sources
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Victoria Markman commented on DRILL-4166:
-----------------------------------------

Docs say: "COALESCE Usage Notes: if all expressions evaluate to null, then the COALESCE function returns null. *Expressions have to be of the same type.*" 

> Inconsistent return type from COALESCE function with views and parquet data sources 
> ------------------------------------------------------------------------------------
>
>                 Key: DRILL-4166
>                 URL: https://issues.apache.org/jira/browse/DRILL-4166
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Execution - Data Types
>    Affects Versions: 1.4.0
>            Reporter: Victoria Markman
>            Priority: Critical
>
> j1 is a parquet file that is defined as following:
> {code}
> [Mon Dec 07 15:06:58] # ~/parquet-tools/parquet-schema 0_0_0.parquet                                                              
> message root {
>   optional binary c_varchar (UTF8);
>   optional int32 c_integer;
>   optional int64 c_bigint;
>   optional float c_float;
>   optional double c_double;
>   optional int32 c_date (DATE);
>   optional int32 c_time (TIME_MILLIS);
>   optional int64 c_timestamp (TIMESTAMP_MILLIS);
>   optional boolean c_boolean;
>   optional double d9;
>   optional double d18;
>   optional double d28;
>   optional double d38;
> }
> {code}
> COALESCE function with input arguments date and timestamp returns timestamp data type as  expected:
> {code}
> 0: jdbc:drill:schema=dfs> create table x as select coalesce(c_date, c_timestamp) from j1 limit 1;
> +-----------+----------------------------+
> | Fragment  | Number of records written  |
> +-----------+----------------------------+
> | 0_0       | 1                          |
> +-----------+----------------------------+
> 1 row selected (0.685 seconds)
> [Mon Dec 07 15:06:14] # ~/parquet-tools/parquet-schema 0_0_0.parquet                                                             
> message root {
>   optional int64 EXPR$0 (TIMESTAMP_MILLIS);
> }
> {code}
> However, if you define a view on top of this parquet file and run the same query, return type is going to be DATE and not TIMESTAMP:
> {code}
> 0: jdbc:drill:schema=dfs> create view v1 as select cast(c_date as date) as c_date, cast(c_timestamp as timestamp) as c_timestamp from j1 limit 1;
> +-------+------------------------------------------------------------+
> |  ok   |                          summary                           |
> +-------+------------------------------------------------------------+
> | true  | View 'v1' created successfully in 'dfs.subqueries' schema  |
> +-------+------------------------------------------------------------+
> 1 row selected (0.368 seconds)
> 0: jdbc:drill:schema=dfs> describe v1;
> +--------------+------------+--------------+
> | COLUMN_NAME  | DATA_TYPE  | IS_NULLABLE  |
> +--------------+------------+--------------+
> | c_date       | DATE       | YES          |
> | c_timestamp  | TIMESTAMP  | YES          |
> +--------------+------------+--------------+
> 2 rows selected (0.518 seconds)
> [Mon Dec 07 15:11:50] # ~/parquet-tools/parquet-schema 0_0_0.parquet                                                    
> message root {
>   optional int32 EXPR$0 (DATE);
> }
> {code}


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)