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

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: Impala 3.0
• Fix Version/s: Impala 2.11.0
• Component/s: Frontend
• Labels: correctness incompatibility query_generator
• Epic Link: Numeric Datatype Conversions
• Assignee: Zach Amsden
• Reporter: Michael Brown
• Created: 20/Nov/16 00:08
• Updated: 21/Nov/18 23:30
• Resolved: 21/Nov/18 23:30

Description

The query generator found this on this query:

SELECT
ABS(a1.tinyint_col_31) AS int_col
FROM table_6 a1
WHERE
(a1.smallint_col_8) != (a1.tinyint_col_31)

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:

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

These work though:

[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

Note that postgres does not have tinyint, only smallint.

Here's some noteworthy behavior at the boundary for the smallest postgres smallint:

randomness=# select abs(-32768);
  abs
-------
 32768
(1 row)

randomness=# select abs(cast(-32768 as smallint));
ERROR:  smallint out of range
randomness=#

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:

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

Postgres:

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