[
https://issues.apache.org/jira/browse/DRILL-4970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16068463#comment-16068463
]
Volodymyr Vysotskyi commented on DRILL-4970:
--------------------------------------------
This bug can be reproduced using a simplified query:
{code:sql}
select count(*) as c from (values(-255.0)) as t(double_id) where
(cast(double_id as int) >= -255 and double_id <= -5) or (double_id <= -256)
{code}
Correct result is 1 but Drill returns 0.
This bug is caused by changing the value of input holder in the cast function
when input holder has negative value.
Method {{doEval()}} in the generated class {{FiltererGen0}} containes code:
{code:java}
63: //---- start of eval portion of castINT function.
----//
64: NullableIntHolder out10 = new NullableIntHolder();
65: {
66: if (out4 .isSet == 0) {
67: out10 .isSet = 0;
68: } else {
69: final NullableIntHolder out = new
NullableIntHolder();
70: NullableFloat8Holder in = out4;
71:
72: CastFloat8Int_eval: {
73: boolean sign = (in.value < 0);
74:
75: in.value = java.lang.Math.abs(in.value);
76:
77: double fractional = in.value % 1;
78: int digit = ((int) (fractional * 10));
79: int carry = 0;
80:
81: if (digit > 4) {
82: carry = 1;
83: }
84: out.value = ((int) in.value) + carry;
85: if (sign == true) {
86: out.value *= -1;
87: }
88: }
89:
90: out.isSet = 1;
91: out10 = out;
92: out.isSet = 1;
93: }
94: }
95: //---- end of eval portion of castINT function.
----//
{code}
At line 70 {{NullableFloat8Holder in}} references to the same object as
{{out4}} and at line 75 {{in.value}} is changed if it was negative.
Object with changed value (line 75) is used in the code below at line 128.
{code:java}
121: //---- start of eval portion of
less_than_or_equal_to function. ----//
122: NullableBitHolder out17 = new NullableBitHolder();
123: {
124: if (out4 .isSet == 0) {
125: out17 .isSet = 0;
126: } else {
127: final NullableBitHolder out = new
NullableBitHolder();
128: NullableFloat8Holder left = out4;
129: Float8Holder right = constant16;
130:
131: GCompareFloat8VsFloat8$LessThanEqFloat8VsFloat8_eval: {
132: out.value = left.value <= right.value ? 1 : 0;
133: }
134:
135: out.isSet = 1;
136: out17 = out;
137: out.isSet = 1;
138: }
139: }
140: //---- end of eval portion of less_than_or_equal_to
function. ----//
{code}
Therefore check {{double_id <= -5}} from the query does not pass and query
returns wrong result.
Similar problem appears when casting decimal9 and decimal18 types to varchar.
> Wrong results when casting double to bigint or int
> --------------------------------------------------
>
> Key: DRILL-4970
> URL: https://issues.apache.org/jira/browse/DRILL-4970
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Data Types
> Affects Versions: 1.8.0
> Reporter: Robert Hou
> Assignee: Volodymyr Vysotskyi
> Attachments: test_table
>
>
> This query returns the wrong result
> {code:sql}
> 0: jdbc:drill:zk=10.10.100.186:5181/drill/rho> select count(\*) from
> test_table where (int_id > -3025 and bigint_id <= -256) or (cast(double_id as
> bigint) >= -255 and double_id <= -5);
> +---------+
> | EXPR$0 |
> +---------+
> | 2769 |
> +---------+
> {code}
> Without the cast, it returns the correct result:
> {code:sql}
> 0: jdbc:drill:zk=10.10.100.186:5181/drill/rho> select count(\*) from
> test_table where (int_id > -3025 and bigint_id <= -256) or (double_id >= -255
> and double_id <= -5);
> +---------+
> | EXPR$0 |
> +---------+
> | 3020 |
> +---------+
> {code}
> By itself, the result is also correct:
> {code:sql}
> 0: jdbc:drill:zk=10.10.100.186:5181/drill/rho> select count(\*) from
> test_table where (cast(double_id as bigint) >= -255 and double_id <= -5);
> +---------+
> | EXPR$0 |
> +---------+
> | 251 |
> +---------+
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
