[jira] [Commented] (DRILL-2753) Implicit cast fails when comparing a double column and a varchar literal

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

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

Mehant Baid commented on DRILL-2753:
------------------------------------

Comparison functions were implemented before we had implicit casting in place, as a result we have a cross product of function implementation across all types (eg compare(bigint, float), compare(int, double) and so on). This violates the basic assumption of implicit cast that all function would operate on similar types and hence would be upward promoted. 

Consider the case where we are comparing varchar and double. Since we have a cross product of implementations we have the following possible functions to pick from

compare(int, double)
compare(bigint, double)
compare(double, double) 

Implicit cast costs converting from varchar to int cheaper that converting from varchar to double hence the chosen function is compare(int, double) which is incorrect. 

Instead if we had only comparison functions operating on similar types like below

compare(int, int)
compare(bigint, bigint)
compare(double, double)

In this case the only viable option to pick is compare(double, double) since double cannot be implicitly casted down to int or bigint. Varchar is casted to double which is the expected behavior. As part of this JIRA I am planning to remove the cross data type implementations for numeric comparisons. 

> Implicit cast fails when comparing a double column and a varchar literal
> ------------------------------------------------------------------------
>
>                 Key: DRILL-2753
>                 URL: https://issues.apache.org/jira/browse/DRILL-2753
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Functions - Drill
>            Reporter: Abhishek Girish
>            Assignee: Mehant Baid
>            Priority: Critical
>             Fix For: 1.0.0
>
>
> Query fails when an implicit cast is used between a column of double data type and a double literal. 
> *Drill:*
> {code:sql}
> > select ss_customer_sk, ss_ticket_number from store_sales  where ss_promo_sk = '50' order by ss_promo_sk limit 1;
> +----------------+------------------+
> | ss_customer_sk | ss_ticket_number |
> +----------------+------------------+
> | 53792          | 44               |
> +----------------+------------------+
> 1 row selected (1.045 seconds)
> > select ss_customer_sk, ss_ticket_number from store_sales  where  ss_wholesale_cost = '38.19'  order by ss_promo_sk limit 1;
> Query failed: RemoteRpcException: Failure while running fragment., 38.19 [ d8f86a4f-226a-4e30-bb23-5b20ae5294e0 on abhi7.qa.lab:31010 ]
> [ d8f86a4f-226a-4e30-bb23-5b20ae5294e0 on abhi7.qa.lab:31010 ]
> Error: exception while executing query: Failure while executing query. (state=,code=0)
> {code}
> *Postgres:*
> {code:sql}
> # select ss_customer_sk, ss_ticket_number from store_sales  where  ss_wholesale_cost = '38.19'  order by ss_promo_sk limit 1;
>  ss_customer_sk | ss_ticket_number
> ----------------+------------------
>           44923 |           148425
> (1 row)
> {code}



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