[DRILL-2355] TRUNC function returns incorrect result when second parameter is specified

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 0.8.0
• Fix Version/s: Future
• Component/s: Functions - Drill
• Labels: document_if_not_fixed
• Assignee: Unassigned
• Reporter: Victoria Markman
• Created: 02/Mar/15 18:06
• Updated: 08/Oct/15 22:19

Description

I believe our TRUNC function is modeled on Postgres TRUNC function.
Semandics of Postgres TRUNC function:

postgres=# select trunc(1234.1234);
 trunc
-------
  1234
(1 row)

postgres=# select trunc(1234.1234, 0);
 trunc
-------
  1234
(1 row)

postgres=# select trunc(1234.1234, 2);
  trunc
---------
 1234.12
(1 row)

postgres=# select trunc(1234.1234, -1);
 trunc 
-------
  1230
(1 row)

postgres=# select trunc(1234.1234, -3);
 trunc
-------
  1000
(1 row)

This is incorrect, I can't truncate to zero decimal places:

0: jdbc:drill:schema=dfs> select trunc(1234.1234) from sys.options limit 1;
+------------+
|   EXPR$0   |
+------------+
| 1234.0     |
+------------+
1 row selected (0.133 seconds)

0: jdbc:drill:schema=dfs> select trunc(1234.1234,0) from sys.options limit 1;
+------------+
|   EXPR$0   |
+------------+
| 1234.0     |
+------------+
1 row selected (0.065 seconds)

Second negative parameter, does not do what it is supposed to do as well:

0: jdbc:drill:schema=dfs> select trunc(1234.1234, -1) from sys.options limit 1;
+------------+
|   EXPR$0   |
+------------+
| 1230.0     |
+------------+
1 row selected (0.068 seconds)

0: jdbc:drill:schema=dfs> select trunc(1234.1234, -3) from sys.options limit 1;
+------------+
|   EXPR$0   |
+------------+
| 1000.0     |
+------------+
1 row selected (0.072 seconds)