[jira] [Commented] (DRILL-4944) incorrect results - case expression

2016-11-02 Thread Zelaine Fong (JIRA) 

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

Zelaine Fong commented on DRILL-4944:
-

[~khfaraaz] - given that Drill treats literal floating point values as DOUBLE 
rather than FLOAT, changing this would break the existing behavior.  I don't 
see a good way to fix this without breaking the current behavior.  If the user 
wants the value to be treated as a float, they can do an explicit cast.

> incorrect results - case expression
> ---
>
> Key: DRILL-4944
> URL: https://issues.apache.org/jira/browse/DRILL-4944
> Project: Apache Drill
>  Issue Type: Bug
>  Components: Execution - Flow
>Affects Versions: 1.9.0
>Reporter: Khurram Faraaz
>Assignee: Serhii Harnyk
>Priority: Critical
> Fix For: 1.9.0
>
>
> Drill 1.9.0 (git commit id: 4edabe7a) returns null, which is wrong.
>  {noformat}
>  0: jdbc:drill:schema=dfs.tmp> SELECT res2, case res2 WHEN 0.1 THEN 0. 
> ELSE null END
> . . . . . . . . . . . . . . > FROM
> . . . . . . . . . . . . . . > (
> . . . . . . . . . . . . . . > SELECT
> . . . . . . . . . . . . . . > (CASE WHEN (false) THEN null ELSE CAST(0.1 
> as float) end) res2
> . . . . . . . . . . . . . . > FROM (values(1)) foo
> . . . . . . . . . . . . . . > ) foobar ;
> +---+-+
> | res2  | EXPR$1  |
> +---+-+
> | 0.1   | null|
> +---+-+
> 1 row selected (0.106 seconds)
>  {noformat}
>  
>  Postgres returns correct results
>   {noformat}
>  postgres=# SELECT res2, case res2 WHEN 0.1 THEN 0. ELSE null END
> postgres-# FROM
> postgres-# (
> postgres(# SELECT
> postgres(# (CASE WHEN (false) THEN null ELSE CAST(0.1 as float) end) res2
> postgres(# FROM (values(1)) foo
> postgres(# ) foobar ;
>  res2 |  case
> --+
>   0.1 | 0.
> (1 row)
>  {noformat}
>  
>  Calcite also returns correct results
>   {noformat}
>  0: jdbc:calcite:model=target/test-classes/mod> SELECT res2, case res2 WHEN 
> 0.1 THEN 0. ELSE null END
> . . . . . . . . . . . . . . . . . . . . . . .> FROM
> . . . . . . . . . . . . . . . . . . . . . . .> (
> . . . . . . . . . . . . . . . . . . . . . . .>   SELECT
> . . . . . . . . . . . . . . . . . . . . . . .>  (CASE WHEN (false) 
> THEN null ELSE CAST(0.1 as float) end) res2
> . . . . . . . . . . . . . . . . . . . . . . .>  FROM (values(1)) foo
> . . . . . . . . . . . . . . . . . . . . . . .> ) foobar ;
> +-++
> |  RES2   | EXPR$1 |
> +-++
> | 0.1 | 0. |
> +-++
> 1 row selected (1.277 seconds)
>  {noformat}
>  
>  Details of explain plan from Drill 1.9.0
>   {noformat}
>  0: jdbc:drill:schema=dfs.tmp> explain plan for SELECT res2, case res2 WHEN 
> 0.1 THEN 0. ELSE null END
> . . . . . . . . . . . . . . > FROM
> . . . . . . . . . . . . . . > (
> . . . . . . . . . . . . . . >   SELECT
> . . . . . . . . . . . . . . >  (CASE WHEN (false) THEN null ELSE 
> CAST(0.1 as float) end) res2
> . . . . . . . . . . . . . . >  FROM (values(1)) foo
> . . . . . . . . . . . . . . > ) foobar ;
> +--+--+
> | text | json |
> +--+--+
> | 00-00Screen
> 00-01  Project(res2=[$0], EXPR$1=[$1])
> 00-02Project(res2=[CASE(false, null, 0.1)], 
> EXPR$1=[CASE(=(CASE(false, null, 0.1), 0.1), 0., null)])
> 00-03  Values
>  | {
>   "head" : {
> "version" : 1,
> "generator" : {
>   "type" : "ExplainHandler",
>   "info" : ""
> },
> "type" : "APACHE_DRILL_PHYSICAL",
> "options" : [ ],
> "queue" : 0,
> "resultMode" : "EXEC"
>   },
>   "graph" : [ {
> "pop" : "Values",
> "@id" : 3,
> "content" : [ {
>   "EXPR$0" : {
> "$numberLong" : 1
>   }
> } ],
> "initialAllocation" : 100,
> "maxAllocation" : 100,
> "cost" : 1.0
>   }, {
> "pop" : "project",
> "@id" : 2,
> "exprs" : [ {
>   "ref" : "`res2`",
>   "expr" : " ( if (false ) then (NULL )  else (0.1 )  end  ) "
> }, {
>   "ref" : "`EXPR$1`",
>   "expr" : " ( if (equal( ( if (false ) then (NULL )  else (0.1 )  end  ) 
> , 0.1)  ) then (0. )  else (NULL )  end  ) "
> } ],
> "child" : 3,
> "initialAllocation" : 100,
> "maxAllocation" : 100,
> "cost" : 1.0
>   }, {
> "pop" : "project",
> "@id" : 1,
> "exprs" : [ {
>   "ref" : "`res2`",
>   "expr" : "`res2`"
> }, {
>   "ref" : "`EXPR$1`",
>   "expr" : "`EXPR$1`"
> } ],
> "child" : 2,
> "initialAllocation" : 100,
> "maxAllocation" : 100,
> "cost" : 1.0
>   }, {
> "pop" : "screen",
> "@id" : 0,
> "child" : 1,
> "initialAllocation" : 100,
>

[jira] [Commented] (DRILL-4944) incorrect results - case expression

2016-11-02 Thread Khurram Faraaz (JIRA) 

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

Khurram Faraaz commented on DRILL-4944:
---

I understand that part. What is the plan to handle such scenarios in Drill, 
going forward ?

> incorrect results - case expression
> ---
>
> Key: DRILL-4944
> URL: https://issues.apache.org/jira/browse/DRILL-4944
> Project: Apache Drill
>  Issue Type: Bug
>  Components: Execution - Flow
>Affects Versions: 1.9.0
>Reporter: Khurram Faraaz
>Assignee: Serhii Harnyk
>Priority: Critical
> Fix For: 1.9.0
>
>
> Drill 1.9.0 (git commit id: 4edabe7a) returns null, which is wrong.
>  {noformat}
>  0: jdbc:drill:schema=dfs.tmp> SELECT res2, case res2 WHEN 0.1 THEN 0. 
> ELSE null END
> . . . . . . . . . . . . . . > FROM
> . . . . . . . . . . . . . . > (
> . . . . . . . . . . . . . . > SELECT
> . . . . . . . . . . . . . . > (CASE WHEN (false) THEN null ELSE CAST(0.1 
> as float) end) res2
> . . . . . . . . . . . . . . > FROM (values(1)) foo
> . . . . . . . . . . . . . . > ) foobar ;
> +---+-+
> | res2  | EXPR$1  |
> +---+-+
> | 0.1   | null|
> +---+-+
> 1 row selected (0.106 seconds)
>  {noformat}
>  
>  Postgres returns correct results
>   {noformat}
>  postgres=# SELECT res2, case res2 WHEN 0.1 THEN 0. ELSE null END
> postgres-# FROM
> postgres-# (
> postgres(# SELECT
> postgres(# (CASE WHEN (false) THEN null ELSE CAST(0.1 as float) end) res2
> postgres(# FROM (values(1)) foo
> postgres(# ) foobar ;
>  res2 |  case
> --+
>   0.1 | 0.
> (1 row)
>  {noformat}
>  
>  Calcite also returns correct results
>   {noformat}
>  0: jdbc:calcite:model=target/test-classes/mod> SELECT res2, case res2 WHEN 
> 0.1 THEN 0. ELSE null END
> . . . . . . . . . . . . . . . . . . . . . . .> FROM
> . . . . . . . . . . . . . . . . . . . . . . .> (
> . . . . . . . . . . . . . . . . . . . . . . .>   SELECT
> . . . . . . . . . . . . . . . . . . . . . . .>  (CASE WHEN (false) 
> THEN null ELSE CAST(0.1 as float) end) res2
> . . . . . . . . . . . . . . . . . . . . . . .>  FROM (values(1)) foo
> . . . . . . . . . . . . . . . . . . . . . . .> ) foobar ;
> +-++
> |  RES2   | EXPR$1 |
> +-++
> | 0.1 | 0. |
> +-++
> 1 row selected (1.277 seconds)
>  {noformat}
>  
>  Details of explain plan from Drill 1.9.0
>   {noformat}
>  0: jdbc:drill:schema=dfs.tmp> explain plan for SELECT res2, case res2 WHEN 
> 0.1 THEN 0. ELSE null END
> . . . . . . . . . . . . . . > FROM
> . . . . . . . . . . . . . . > (
> . . . . . . . . . . . . . . >   SELECT
> . . . . . . . . . . . . . . >  (CASE WHEN (false) THEN null ELSE 
> CAST(0.1 as float) end) res2
> . . . . . . . . . . . . . . >  FROM (values(1)) foo
> . . . . . . . . . . . . . . > ) foobar ;
> +--+--+
> | text | json |
> +--+--+
> | 00-00Screen
> 00-01  Project(res2=[$0], EXPR$1=[$1])
> 00-02Project(res2=[CASE(false, null, 0.1)], 
> EXPR$1=[CASE(=(CASE(false, null, 0.1), 0.1), 0., null)])
> 00-03  Values
>  | {
>   "head" : {
> "version" : 1,
> "generator" : {
>   "type" : "ExplainHandler",
>   "info" : ""
> },
> "type" : "APACHE_DRILL_PHYSICAL",
> "options" : [ ],
> "queue" : 0,
> "resultMode" : "EXEC"
>   },
>   "graph" : [ {
> "pop" : "Values",
> "@id" : 3,
> "content" : [ {
>   "EXPR$0" : {
> "$numberLong" : 1
>   }
> } ],
> "initialAllocation" : 100,
> "maxAllocation" : 100,
> "cost" : 1.0
>   }, {
> "pop" : "project",
> "@id" : 2,
> "exprs" : [ {
>   "ref" : "`res2`",
>   "expr" : " ( if (false ) then (NULL )  else (0.1 )  end  ) "
> }, {
>   "ref" : "`EXPR$1`",
>   "expr" : " ( if (equal( ( if (false ) then (NULL )  else (0.1 )  end  ) 
> , 0.1)  ) then (0. )  else (NULL )  end  ) "
> } ],
> "child" : 3,
> "initialAllocation" : 100,
> "maxAllocation" : 100,
> "cost" : 1.0
>   }, {
> "pop" : "project",
> "@id" : 1,
> "exprs" : [ {
>   "ref" : "`res2`",
>   "expr" : "`res2`"
> }, {
>   "ref" : "`EXPR$1`",
>   "expr" : "`EXPR$1`"
> } ],
> "child" : 2,
> "initialAllocation" : 100,
> "maxAllocation" : 100,
> "cost" : 1.0
>   }, {
> "pop" : "screen",
> "@id" : 0,
> "child" : 1,
> "initialAllocation" : 100,
> "maxAllocation" : 100,
> "cost" : 1.0
>   } ]
> } |
>  {noformat}



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

[jira] [Commented] (DRILL-4944) incorrect results - case expression

2016-10-31 Thread Zelaine Fong (JIRA) 

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

Zelaine Fong commented on DRILL-4944:
-

OK, I see.  So, the following query will return 1 as expected:

0: jdbc:drill:zk=local> select 1 from (values(1)) where 0.1 = cast (0.1 as 
double);
+-+
| EXPR$0  |
+-+
| 1   |
+-+
1 row selected (0.177 seconds)

You can also see that this in the following query:

0: jdbc:drill:zk=local> select 0.1 - cast(0.1 as float) from (values(1));
+-+
| EXPR$0  |
+-+
| -1.4901161138336505E-9  |
+-+
1 row selected (0.217 seconds)

Generally, it's not a good idea to compare floating point values, due to the 
imprecision in how floating point values are represented.

As [~sharnyk] has noted, changing this behavior has huge implications on 
existing behavior.

> incorrect results - case expression
> ---
>
> Key: DRILL-4944
> URL: https://issues.apache.org/jira/browse/DRILL-4944
> Project: Apache Drill
>  Issue Type: Bug
>  Components: Execution - Flow
>Affects Versions: 1.9.0
>Reporter: Khurram Faraaz
>Assignee: Serhii Harnyk
>Priority: Critical
> Fix For: 1.9.0
>
>
> Drill 1.9.0 (git commit id: 4edabe7a) returns null, which is wrong.
>  {noformat}
>  0: jdbc:drill:schema=dfs.tmp> SELECT res2, case res2 WHEN 0.1 THEN 0. 
> ELSE null END
> . . . . . . . . . . . . . . > FROM
> . . . . . . . . . . . . . . > (
> . . . . . . . . . . . . . . > SELECT
> . . . . . . . . . . . . . . > (CASE WHEN (false) THEN null ELSE CAST(0.1 
> as float) end) res2
> . . . . . . . . . . . . . . > FROM (values(1)) foo
> . . . . . . . . . . . . . . > ) foobar ;
> +---+-+
> | res2  | EXPR$1  |
> +---+-+
> | 0.1   | null|
> +---+-+
> 1 row selected (0.106 seconds)
>  {noformat}
>  
>  Postgres returns correct results
>   {noformat}
>  postgres=# SELECT res2, case res2 WHEN 0.1 THEN 0. ELSE null END
> postgres-# FROM
> postgres-# (
> postgres(# SELECT
> postgres(# (CASE WHEN (false) THEN null ELSE CAST(0.1 as float) end) res2
> postgres(# FROM (values(1)) foo
> postgres(# ) foobar ;
>  res2 |  case
> --+
>   0.1 | 0.
> (1 row)
>  {noformat}
>  
>  Calcite also returns correct results
>   {noformat}
>  0: jdbc:calcite:model=target/test-classes/mod> SELECT res2, case res2 WHEN 
> 0.1 THEN 0. ELSE null END
> . . . . . . . . . . . . . . . . . . . . . . .> FROM
> . . . . . . . . . . . . . . . . . . . . . . .> (
> . . . . . . . . . . . . . . . . . . . . . . .>   SELECT
> . . . . . . . . . . . . . . . . . . . . . . .>  (CASE WHEN (false) 
> THEN null ELSE CAST(0.1 as float) end) res2
> . . . . . . . . . . . . . . . . . . . . . . .>  FROM (values(1)) foo
> . . . . . . . . . . . . . . . . . . . . . . .> ) foobar ;
> +-++
> |  RES2   | EXPR$1 |
> +-++
> | 0.1 | 0. |
> +-++
> 1 row selected (1.277 seconds)
>  {noformat}
>  
>  Details of explain plan from Drill 1.9.0
>   {noformat}
>  0: jdbc:drill:schema=dfs.tmp> explain plan for SELECT res2, case res2 WHEN 
> 0.1 THEN 0. ELSE null END
> . . . . . . . . . . . . . . > FROM
> . . . . . . . . . . . . . . > (
> . . . . . . . . . . . . . . >   SELECT
> . . . . . . . . . . . . . . >  (CASE WHEN (false) THEN null ELSE 
> CAST(0.1 as float) end) res2
> . . . . . . . . . . . . . . >  FROM (values(1)) foo
> . . . . . . . . . . . . . . > ) foobar ;
> +--+--+
> | text | json |
> +--+--+
> | 00-00Screen
> 00-01  Project(res2=[$0], EXPR$1=[$1])
> 00-02Project(res2=[CASE(false, null, 0.1)], 
> EXPR$1=[CASE(=(CASE(false, null, 0.1), 0.1), 0., null)])
> 00-03  Values
>  | {
>   "head" : {
> "version" : 1,
> "generator" : {
>   "type" : "ExplainHandler",
>   "info" : ""
> },
> "type" : "APACHE_DRILL_PHYSICAL",
> "options" : [ ],
> "queue" : 0,
> "resultMode" : "EXEC"
>   },
>   "graph" : [ {
> "pop" : "Values",
> "@id" : 3,
> "content" : [ {
>   "EXPR$0" : {
> "$numberLong" : 1
>   }
> } ],
> "initialAllocation" : 100,
> "maxAllocation" : 100,
> "cost" : 1.0
>   }, {
> "pop" : "project",
> "@id" : 2,
> "exprs" : [ {
>   "ref" : "`res2`",
>   "expr" : " ( if (false ) then (NULL )  else (0.1 )  end  ) "
> }, {
>   "ref" : "`EXPR$1`",
>   "expr" : " ( if (equal( ( if (false ) then (NULL )  else (0.1 )  end  ) 
> , 0.1)  ) then (0. )  else (NULL )  end  ) "
> } ],
> "child" : 3,
> "initialAllocation" : 100,
> "maxAllocation" :

[jira] [Commented] (DRILL-4944) incorrect results - case expression

2016-10-31 Thread Serhii Harnyk (JIRA) 

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

Serhii Harnyk commented on DRILL-4944:
--

Result of SELECT 0.1 FROM (values(1)) where 1=1

1 row(s):
-
| EXPR$0
-
| 0.1 
-

Result of SELECT CAST(0.1 as float) FROM (values(1)) where 1=1
-
| EXPR$0  |
-
| 0.1   |
-

As you can see default type for 0.1 is FLOAT8 - "double", not "float". 
So the query like SELECT 1 FROM (values(1)) where 0.1 = CAST(0.1 as float)
will have a result:
0 row(s):
Total record count: 0

The problem is that Drill when does comparison considers type of argument, not 
only value. So we need to decide - is it bug or feature.

There are two ways for fixing this "bug".
1. Set default type floats - float4.
2. Don't consider type in comparisons. 
Both ways can cause huge effects for the system.

> incorrect results - case expression
> ---
>
> Key: DRILL-4944
> URL: https://issues.apache.org/jira/browse/DRILL-4944
> Project: Apache Drill
>  Issue Type: Bug
>  Components: Execution - Flow
>Affects Versions: 1.9.0
>Reporter: Khurram Faraaz
>Assignee: Serhii Harnyk
>Priority: Critical
> Fix For: 1.9.0
>
>
> Drill 1.9.0 (git commit id: 4edabe7a) returns null, which is wrong.
>  {noformat}
>  0: jdbc:drill:schema=dfs.tmp> SELECT res2, case res2 WHEN 0.1 THEN 0. 
> ELSE null END
> . . . . . . . . . . . . . . > FROM
> . . . . . . . . . . . . . . > (
> . . . . . . . . . . . . . . > SELECT
> . . . . . . . . . . . . . . > (CASE WHEN (false) THEN null ELSE CAST(0.1 
> as float) end) res2
> . . . . . . . . . . . . . . > FROM (values(1)) foo
> . . . . . . . . . . . . . . > ) foobar ;
> +---+-+
> | res2  | EXPR$1  |
> +---+-+
> | 0.1   | null|
> +---+-+
> 1 row selected (0.106 seconds)
>  {noformat}
>  
>  Postgres returns correct results
>   {noformat}
>  postgres=# SELECT res2, case res2 WHEN 0.1 THEN 0. ELSE null END
> postgres-# FROM
> postgres-# (
> postgres(# SELECT
> postgres(# (CASE WHEN (false) THEN null ELSE CAST(0.1 as float) end) res2
> postgres(# FROM (values(1)) foo
> postgres(# ) foobar ;
>  res2 |  case
> --+
>   0.1 | 0.
> (1 row)
>  {noformat}
>  
>  Calcite also returns correct results
>   {noformat}
>  0: jdbc:calcite:model=target/test-classes/mod> SELECT res2, case res2 WHEN 
> 0.1 THEN 0. ELSE null END
> . . . . . . . . . . . . . . . . . . . . . . .> FROM
> . . . . . . . . . . . . . . . . . . . . . . .> (
> . . . . . . . . . . . . . . . . . . . . . . .>   SELECT
> . . . . . . . . . . . . . . . . . . . . . . .>  (CASE WHEN (false) 
> THEN null ELSE CAST(0.1 as float) end) res2
> . . . . . . . . . . . . . . . . . . . . . . .>  FROM (values(1)) foo
> . . . . . . . . . . . . . . . . . . . . . . .> ) foobar ;
> +-++
> |  RES2   | EXPR$1 |
> +-++
> | 0.1 | 0. |
> +-++
> 1 row selected (1.277 seconds)
>  {noformat}
>  
>  Details of explain plan from Drill 1.9.0
>   {noformat}
>  0: jdbc:drill:schema=dfs.tmp> explain plan for SELECT res2, case res2 WHEN 
> 0.1 THEN 0. ELSE null END
> . . . . . . . . . . . . . . > FROM
> . . . . . . . . . . . . . . > (
> . . . . . . . . . . . . . . >   SELECT
> . . . . . . . . . . . . . . >  (CASE WHEN (false) THEN null ELSE 
> CAST(0.1 as float) end) res2
> . . . . . . . . . . . . . . >  FROM (values(1)) foo
> . . . . . . . . . . . . . . > ) foobar ;
> +--+--+
> | text | json |
> +--+--+
> | 00-00Screen
> 00-01  Project(res2=[$0], EXPR$1=[$1])
> 00-02Project(res2=[CASE(false, null, 0.1)], 
> EXPR$1=[CASE(=(CASE(false, null, 0.1), 0.1), 0., null)])
> 00-03  Values
>  | {
>   "head" : {
> "version" : 1,
> "generator" : {
>   "type" : "ExplainHandler",
>   "info" : ""
> },
> "type" : "APACHE_DRILL_PHYSICAL",
> "options" : [ ],
> "queue" : 0,
> "resultMode" : "EXEC"
>   },
>   "graph" : [ {
> "pop" : "Values",
> "@id" : 3,
> "content" : [ {
>   "EXPR$0" : {
> "$numberLong" : 1
>   }
> } ],
> "initialAllocation" : 100,
> "maxAllocation" : 100,
> "cost" : 1.0
>   }, {
> "pop" : "project",
> "@id" : 2,
> "exprs" : [ {
>   "ref" : "`res2`",
>