Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-1821

Casting scenarios with invalid/inconsistent results

    XMLWordPrintableJSON

Details

    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
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            ghenke_impala_d87e Grant Henke
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated: