You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues-all@impala.apache.org by "Tim Armstrong (JIRA)" <ji...@apache.org> on 2018/10/23 23:29:00 UTC
[jira] [Assigned] (IMPALA-1821) Casting scenarios with
invalid/inconsistent results
[ 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: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org