I am facing this strange but basic problem with apache-drill SQL, a simple division by operation is giving wrong result as shown below:
0: jdbc:drill:drillbit=localhost> SELECT 'Test' FROM (VALUES(1));
+---------+
| EXPR$0 |
+---------+
| Test |
+---------+
1 row selected (1.027 seconds)
0: jdbc:drill:drillbit=localhost> select 10/100 from (VALUES(1));
+---------+
| EXPR$0 |
+---------+
| 0 |
+---------+
1 row selected (0.67 seconds)
0: jdbc:drill:drillbit=localhost> select 100/10 from (VALUES(1));
+---------+
| EXPR$0 |
+---------+
| 10 |
+---------+
1 row selected (0.662 seconds)
0: jdbc:drill:drillbit=localhost> select 10/11 from (VALUES(1));
+---------+
| EXPR$0 |
+---------+
| 0 |
+---------+
1 row selected (0.649 seconds)
0: jdbc:drill:drillbit=localhost> select cast(10/11 as float) from (VALUES(1));
+---------+
| EXPR$0 |
+---------+
| 0.0 |
+---------+
1 row selected (0.74 seconds)
0: jdbc:drill:drillbit=localhost> select cast(10/11 as double) from (VALUES(1));
+---------+
| EXPR$0 |
+---------+
| 0.0 |
+---------+
1 row selected (0.691 seconds)
0: jdbc:drill:drillbit=localhost> select cast(10*100/11 as double) from (VALUES(1));
+---------+
| EXPR$0 |
+---------+
| 90.0 |
+---------+
1 row selected (0.72 seconds)
0: jdbc:drill:drillbit=localhost> select cast(10*100/11 as double)/100 from (VALUES(1));
+---------+
| EXPR$0 |
+---------+
| 0.9 |
+---------+
Drill version is 1.6 and 1.8 (tried in both). Even round is not helping. Is there any work around?
This is integer arithmetic.
11/4 = 2 (and reminder = 3)
You have to convert one of the values to the requested type.
select cast(x as float)/y