Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
Impala 2.1.1
-
None
Description
When casting values such as INF/NAN or down-casting values into smaller types Impala's behavior is inconsistent and unfavorable. Below are some sample queries and results that show how Impala and other databases handle these scenarios.
Downcast Examples:
Impala: SELECT CAST(223372036854775808 as INT); = 494665728 Hive: SELECT CAST(223372036854775808 as INT); = 494665728 Teradata: SELECT CAST(223372036854775808 as INT); = SELECT Failed. 2616: Numeric overflow occurred during computation. Postgres: postgres=# SELECT CAST(223372036854775808 as INT); ERROR: integer out of range
INF/NAN Examples Summary:
Impala = Smallest value of type (Division by 0 results in null if DECIMAL type is used) Hive = NULL MySQL = NULL Teradata = Error -> Invalid calculation: division by zero.
INF/NAN Examples Full:
Impala: SELECT inf, cast(inf as INTEGER), cast(inf AS BIGINT), cast(inf AS DECIMAL) ,infDec, infDec2 ,nan, cast(nan as INTEGER), cast(nan AS BIGINT), cast(nan AS DECIMAL) FROM ( SELECT (1/0) as inf, (CAST(1 as DECIMAL)/CAST(0 as DECIMAL)) as infDec, (1.0/0.0) as infDec2, (0/0) as nan ) t; +----------+------------------+----------------------+---------------------------+--------+---------+-----+------------------+----------------------+---------------------------+ | inf | cast(inf as int) | cast(inf as bigint) | cast(inf as decimal(9,0)) | infdec | infdec2 | nan | cast(nan as int) | cast(nan as bigint) | cast(nan as decimal(9,0)) | +----------+------------------+----------------------+---------------------------+--------+---------+-----+------------------+----------------------+---------------------------+ | Infinity | -2147483648 | -9223372036854775808 | -2147483648 | NULL | NULL | NaN | -2147483648 | -9223372036854775808 | NULL | +----------+------------------+----------------------+---------------------------+--------+---------+-----+------------------+----------------------+---------------------------+ WARNINGS: UDF WARNING: Expression overflowed, returning NULL Hive: SELECT inf, cast(inf as INT), cast(inf AS BIGINT), cast(inf AS DECIMAL) ,infDec, infDec2 ,nan, cast(nan as INT), cast(nan AS BIGINT), cast(nan AS DECIMAL) FROM ( SELECT (1/0) as inf, (CAST(1 as DECIMAL)/CAST(0 as DECIMAL)) as infDec, (1.0/0.0) as infDec2, (0/0) as nan FROM dual ) t; +-------+-------+-------+-------+---------+----------+-------+-------+-------+-------+--+ | inf | _c1 | _c2 | _c3 | infdec | infdec2 | nan | _c7 | _c8 | _c9 | +-------+-------+-------+-------+---------+----------+-------+-------+-------+-------+--+ | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-------+-------+-------+-------+---------+----------+-------+-------+-------+-------+--+ 1 row selected (32.346 seconds) MySQL: SELECT inf, cast(inf as SIGNED), cast(inf AS DECIMAL) ,infDec, infDec2 ,nan, cast(nan as SIGNED), cast(nan AS DECIMAL) FROM ( SELECT (1/0) as inf, (CAST(1 as DECIMAL)/CAST(0 as DECIMAL)) as infDec, (1.0/0.0) as infDec2, (0/0) as nan ) t; +------+---------------------+----------------------+--------+---------+------+---------------------+----------------------+ | inf | cast(inf as SIGNED) | cast(inf AS DECIMAL) | infDec | infDec2 | nan | cast(nan as SIGNED) | cast(nan AS DECIMAL) | +------+---------------------+----------------------+--------+---------+------+---------------------+----------------------+ | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------+---------------------+----------------------+--------+---------+------+---------------------+----------------------+ 1 row in set (0.00 sec) Teradata: SELECT inf, cast(inf as INTEGER), cast(inf AS BIGINT), cast(inf AS DECIMAL) ,infDec, infDec2 ,nan, cast(nan as INTEGER), cast(nan AS BIGINT), cast(nan AS DECIMAL) FROM ( SELECT (1/0) as inf, (CAST(1 as DECIMAL)/CAST(0 as DECIMAL)) as infDec, (1.0/0.0) as infDec2, (0/0) as nan ) t; SELECT Failed. 2618: Invalid calculation: division by zero. Oracle: SQL> SELECT (1/0) as inf, (CAST(1 as DECIMAL)/CAST(0 as DECIMAL)) as infDec, (1.0/0.0) as infDec2, (0/0) as nan from dual; SELECT (1/0) as inf, (CAST(1 as DECIMAL)/CAST(0 as DECIMAL)) as infDec, (1.0/0.0) as infDec2, (0/0) as nan from dual * ERROR at line 1: ORA-01476: divisor is equal to zero Postgres: postgres=# SELECT (1/0) AS inf; ERROR: division by zero postgres=# SELECT (1.0/0.0) AS inf; ERROR: division by zero