Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-4513

ABS(n) where n is the lowest bound for the int types returns negative values

    XMLWordPrintableJSON

    Details

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

        Attachments

          Activity

            People

            • Assignee:
              zamsden Zach Amsden
              Reporter:
              mikeb Michael Brown
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: