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

["Zach Amsden (JIRA)" <ji...@apache.org>]

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

Zach Amsden closed IMPALA-4513.
-------------------------------
    Resolution: Fixed

IMPALA-4513: Promote integer types for ABS()

The internal representation of the most negative number
in two's complement requires 1 more bit to represent the
positive version.  This means ABS() must promote integer
types to the next highest width.

Change-Id: I86cc880e78258d5f90471bd8af4caeb4305eed77
Reviewed-on: http://gerrit.cloudera.org:8080/8004
Reviewed-by: Tim Armstrong <ta...@cloudera.com>
Tested-by: Impala Public Jenkins

> 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
>              Labels: correctness, incompatibility, query_generator
>             Fix For: Product Backlog
>
>
> 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
(v6.4.14#64029)