[
https://issues.apache.org/jira/browse/IMPALA-1821?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Tim Armstrong reassigned IMPALA-1821:
-------------------------------------
Assignee: (was: Alexander Behm)
> Casting scenarios with invalid/inconsistent results
> ---------------------------------------------------
>
> Key: IMPALA-1821
> URL: https://issues.apache.org/jira/browse/IMPALA-1821
> Project: IMPALA
> Issue Type: Bug
> Components: Frontend
> Affects Versions: Impala 2.1.1
> Reporter: Grant Henke
> Priority: Critical
> Labels: correctness, downgraded, incompatibility
>
> 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:
> {code}
> 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
> {code}
> INF/NAN Examples Summary:
> {code}
> 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.
> {code}
> INF/NAN Examples Full:
> {code}
> 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
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
