[DRILL-524] Drill returns float for MOD function that contains decimal number & divident not returned if divider is 0

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.0.0
• Fix Version/s: 0.4.0
• Component/s: Functions - Drill
• Assignee: DrillCommitter
• Reporter: Krystal
• Created: 15/Apr/14 21:09
• Updated: 22/Jul/14 16:29
• Resolved: 21/Jul/14 03:15

Description

Ran the following mod function in drill:
0: jdbc:drill:schema=dfs> select mod(12.4,5) from dfs.`voter` where rownum=10;
------------

EXPR$0

------------

2.4000000000000004

------------

The result is a float which looks messy. The other db platforms return result based on the highest number of significant digits either from the divident or divider. For example from oracle:
SQL> select mod(12.4,5) from voter where voter_id=10;

MOD(12.4,5)
-----------
2.4

SQL> select mod(12.45,5.1) from voter where voter_id=10;

MOD(12.45,5.1)
--------------
2.25

Another issue is when the divider is 0. Here is the spec for MOD from http://users.atw.hu/sqlnut/sqlnut2-chp-4-sect-4.html

...returns the dividend if the divider is 0

Drill returns "NaN" if the divider is 0:
0: jdbc:drill:schema=dfs> select mod(age,0) from dfs.`voter` where rownum=11;
------------

EXPR$0

------------

NaN

------------

Based on postgres, mysql and oracle databases, only oracle adheres to this rule:
SQL> select mod(12.45,0) from voter where voter_id=10;

MOD(12.45,0)
------------
12.45