[jira] [Resolved] (DRILL-2546) Implicit Cast : Joining 2 tables on columns which are float and double should succeed when the values are equal

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

     [ https://issues.apache.org/jira/browse/DRILL-2546?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mehant Baid resolved DRILL-2546.
--------------------------------
    Resolution: Invalid

Certain floating point values cannot be represented accurately in binary, hence their imprecise representations are used while storing it as float and double. However the imprecise representations of float and double are not the same. So when float is implicitly promoted to double the equality comparison will fail for the same initial value. This behavior is consistent with postgres and in general its not a good idea to be joining on columns with float and double. Here is a stackoverflow link talking about a similar issue: http://stackoverflow.com/questions/16627813/why-is-comparing-a-float-to-a-double-inconsistent-in-java

> Implicit Cast : Joining 2 tables on columns which are float and double should succeed when the values are equal
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: DRILL-2546
>                 URL: https://issues.apache.org/jira/browse/DRILL-2546
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Execution - Data Types, Execution - Relational Operators
>            Reporter: Rahul Challapalli
>            Assignee: Mehant Baid
>             Fix For: 0.9.0
>
>         Attachments: fewtypes_null.json, fewtypes_null.parquet
>
>
> git.commit.id.abbrev=f1b59ed
> I attached 2 files which contains the same values. 
> The below query is not doing an implicit cast between double and float
> {code}
> select count(*) from dfs.`cross-sources`.`fewtypes_null.parquet` p
> . . . . . . . . . . . . . . > inner join dfs.`cross-sources`.`fewtypes_null.json` o
> . . . . . . . . . . . . . . >     on p.float_col=o.float_col;
> +------------+
> |   EXPR$0   |
> +------------+
> | 1          |
> +------------+
> 1 row selected (0.148 seconds)
> {code}
> However if we do an explicit cast, we get the right result
> {code}
> select count(*) from dfs.`cross-sources`.`fewtypes_null.parquet` p
> . . . . . . . . . . . . . . > inner join dfs.`cross-sources`.`fewtypes_null.json` o
> . . . . . . . . . . . . . . >     on p.float_col=cast(o.float_col as float);
> +------------+
> |   EXPR$0   |
> +------------+
> | 17         |
> +------------+
> {code}



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