[
https://issues.apache.org/jira/browse/IMPALA-4513?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Tim Armstrong reopened IMPALA-4513:
-----------------------------------
> 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: 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
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
