[DRILL-4683] Overflow and out of range handling in Drill

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.7.0
• Component/s: Execution - Flow
• Assignee: Unassigned
• Reporter: Khurram Faraaz
• Created: 17/May/16 16:40
• Updated: 17/May/16 16:40

Description

DBMS like MySQL, Postgres and IBM DB2 etc handle out of range and overflow errors, when there are any in the input data. However, Drill returns incorrect results (with some data loss). Drill should handle such error scenarios like integer overflow and report proper error message to user.

postgres=# SELECT CAST(id AS INTEGER) FROM ( VALUES(1),(11111111111111)) tbl(id);

ERROR:  integer out of range

Results for same query from Drill 1.6.0 (incorrect result)

0: jdbc:drill:schema=dfs.tmp> SELECT CAST(id AS INTEGER) FROM ( VALUES(1),(11111111111111)) tbl(id);

+-----------+

|  EXPR$0   |

+-----------+

| 1         |

| 30716359  |

+-----------+

2 rows selected (0.168 seconds)

MySQL behavior

When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time:

If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.

If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.

mysql> SELECT 9223372036854775807 + 1;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

IBM DB2 reports and Exception with the below Exception types

FIXED POINT OVERFLOW
DECIMAL OVERFLOW
ZERO DIVIDE
DIVIDE EXCEPTION
EXPONENT OVERFLOW
INVALID OPERATION
SUBNORMAL
UNDERFLOW
OVERFLOW
OUT OF RANGE