Khurram Faraaz created DRILL-4944:
-------------------------------------
Summary: 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
Priority: Critical
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.9999 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.9999 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.9999
(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.9999 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.9999 |
+-----------------+--------+
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.9999 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-00 Screen
00-01 Project(res2=[$0], EXPR$1=[$1])
00-02 Project(res2=[CASE(false, null, 0.1)], EXPR$1=[CASE(=(CASE(false,
null, 0.1), 0.1), 0.9999, 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" : 1000000,
"maxAllocation" : 10000000000,
"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.9999 ) else (NULL ) end ) "
} ],
"child" : 3,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1.0
}, {
"pop" : "project",
"@id" : 1,
"exprs" : [ {
"ref" : "`res2`",
"expr" : "`res2`"
}, {
"ref" : "`EXPR$1`",
"expr" : "`EXPR$1`"
} ],
"child" : 2,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1.0
}, {
"pop" : "screen",
"@id" : 0,
"child" : 1,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1.0
} ]
} |
{noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
