Khurram Faraaz created DRILL-4683:
-------------------------------------
Summary: Overflow and out of range handling in Drill
Key: DRILL-4683
URL: https://issues.apache.org/jira/browse/DRILL-4683
Project: Apache Drill
Issue Type: Bug
Components: Execution - Flow
Affects Versions: 1.7.0
Reporter: Khurram Faraaz
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.
{noformat}
postgres=# SELECT CAST(id AS INTEGER) FROM ( VALUES(1),(11111111111111))
tbl(id);
ERROR: integer out of range
{noformat}
Results for same query from Drill 1.6.0 (incorrect result)
{noformat}
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)
{noformat}
MySQL behavior
{noformat}
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)'
{noformat}
{noformat}
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
{noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
