[jira] [Commented] (DRILL-4944) incorrect results - case expression
[ 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
[ 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
[ 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
[ 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`", >