[IMPALA-1821] Casting scenarios with invalid/inconsistent results

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: Impala 2.1.1
• Component/s: Frontend
• Labels: correctness downgraded incompatibility
• Epic Link: Numeric Datatype Conversions
• Target Version: Product Backlog
• Assignee: Unassigned
• Reporter: Grant Henke
• Created: 26/Feb/15 04:02
• Updated: 12/Feb/20 23:27

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