[jira] [Resolved] (IMPALA-4513) ABS(n) where n is the lowest bound for the int types returns negative values

["Tim Armstrong (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/IMPALA-4513?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Tim Armstrong resolved IMPALA-4513.
-----------------------------------
       Resolution: Fixed
    Fix Version/s:     (was: Product Backlog)
                   Impala 2.11.0

> ABS(n) where n is the lowest bound for the int types returns negative values
> ----------------------------------------------------------------------------
>
>                 Key: IMPALA-4513
>                 URL: https://issues.apache.org/jira/browse/IMPALA-4513
>             Project: IMPALA
>          Issue Type: Bug
>          Components: Frontend
>    Affects Versions: Impala 3.0
>            Reporter: Michael Brown
>            Assignee: Zach Amsden
>            Priority: Major
>              Labels: correctness, incompatibility, query_generator
>             Fix For: Impala 2.11.0
>
>
> The query generator found this on this query:
> {noformat}
> SELECT
> ABS(a1.tinyint_col_31) AS int_col
> FROM table_6 a1
> WHERE
> (a1.smallint_col_8) != (a1.tinyint_col_31)
> {noformat}
> In the database the query generator is using, tinyint_col_31 has all possible tinyint values, from -128 to 127, and also null. One of the rows returned was -128.
> The general problem seems to be we're not willing to "promote" the value to another type to fit the correct answer in the literal case, and we don't error in other cases where postgres does.  See below.
> Simpler examples just using literals:
> {noformat}
> [localhost:21000] > select abs(-128) --- lowest tinyint;
> +-----------+
> | abs(-128) |
> +-----------+
> | -128      |
> +-----------+
> Fetched 1 row(s) in 0.02s
> [localhost:21000] > select abs(-32768) --- lowest smallint;
> +-------------+
> | abs(-32768) |
> +-------------+
> | -32768      |
> +-------------+
> Fetched 1 row(s) in 0.02s
> [localhost:21000] > select abs(-2147483648) --- lowest int;
> +------------------+
> | abs(-2147483648) |
> +------------------+
> | -2147483648      |
> +------------------+
> Fetched 1 row(s) in 0.02s
> [localhost:21000] > select abs(-9223372036854775808) --- lowest bigint;
> +---------------------------+
> | abs(-9223372036854775808) |
> +---------------------------+
> | -9223372036854775808      |
> +---------------------------+
> Fetched 1 row(s) in 0.02s
> [localhost:21000] >
> {noformat}
> These work though:
> {noformat}
> [localhost:21000] > select abs(0-128);
> +--------------+
> | abs(0 - 128) |
> +--------------+
> | 128          |
> +--------------+
> Fetched 1 row(s) in 0.02s
> [localhost:21000] > select abs(-1*128);
> +-------------------+
> | abs(-1 * 1 * 128) |
> +-------------------+
> | 128               |
> +-------------------+
> Fetched 1 row(s) in 0.02s
> {noformat}
> Note that postgres does not have tinyint, only smallint.
> Here's some noteworthy behavior at the boundary for the smallest postgres smallint:
> {noformat}
> randomness=# select abs(-32768);
>   abs
> -------
>  32768
> (1 row)
> randomness=# select abs(cast(-32768 as smallint));
> ERROR:  smallint out of range
> randomness=#
> {noformat}
> Last, this seems to work the same if I have some values stored in a smallint column: Impala returns a negative value for the row, and postgres will error:
> Impala:
> {noformat}
> [localhost:21000] > select i from foo;
> +--------+
> | i      |
> +--------+
> | -32768 |
> | -32767 |
> | -1     |
> | 0      |
> | 1      |
> | 32767  |
> +--------+
> Fetched 6 row(s) in 0.15s
> [localhost:21000] > select abs(i) from foo;
> +--------+
> | abs(i) |
> +--------+
> | -32768 |
> | 32767  |
> | 1      |
> | 0      |
> | 1      |
> | 32767  |
> +--------+
> Fetched 6 row(s) in 0.15s
> [localhost:21000]
> {noformat}
> Postgres:
> {noformat}
> postgres=# select i from foo;
>    i
> --------
>  -32768
>  -32767
>      -1
>       0
>       1
>   32767
> (6 rows)
> postgres=# select abs(i) from foo;
> ERROR:  smallint out of range
> postgres=#
> {noformat}



--
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