Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
Impala 3.0
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=#