[ https://issues.apache.org/jira/browse/DRILL-4944?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15624468#comment-15624468 ]
Serhii Harnyk commented on DRILL-4944: -------------------------------------- [~khfaraaz] Drill uses Calcite more like a query parser/planner/optimizator. Drill has its own types and type handling logic. > 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.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)