BUG #14986: -2147483648 is minimum value of integer but -2147483648::integer fails (out of range).

On Wed, Dec 20, 2017 at 1:20 PM, PG Bug reporting form <
noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 14986
Logged by: Indrek Loolaid
Email address: binoternary@gmail.com
PostgreSQL version: 10.1
Operating system: Ubuntu 16.04.3 LTS
Description:

Documentation in
https://www.postgresql.org/docs/current/static/datatype-numeric.html
states
that the range for integer type is -2147483648 to +2147483647.

However casting the minimum value literal with :: syntax to integer fails
(ERROR: integer out of range).

postgres=# select version();
version

------------------------------------------------------------
----------------------------------------------------
PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

postgres=# select -2147483648::integer;
ERROR: integer out of range

postgres=# select cast(-2147483648 as integer);
int4
-------------
-2147483648
(1 row)

postgres=# select (select -2147483648)::integer;
?column?
-------------
-2147483648
(1 row)

The expected outome is that the first query returns the same result as the
other two.
Bigint and smallint types have the same issue.

In the first query, you are casting 2147483648 to integer, and then
applying the minus. So it overflows the positive integer. You need ()
around it:

postgres=# select (-2147483648)::integer;
int4
-------------
-2147483648
(1 row)

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On 20 December 2017 at 12:25, Magnus Hagander <magnus@hagander.net> wrote:

In the first query, you are casting 2147483648 to integer, and then
applying the minus. So it overflows the positive integer. You need () around
it:

postgres=# select (-2147483648)::integer;
int4
-------------
-2147483648
(1 row)

Note that -2147483648 is already an integer constant so the ::integer
doesn't actually do anything here. It gets removed during query
preparation (parse analysis? Not sure). It doesn't appear in the final
plan at all.

But even if you do arrange for the cast to be called (as it is in the
original query) it happens during constant folding preparing the plan
(again parse analysis?):

=# explain select '-2147483648'::bigint::integer;
LOG: 00000: plan:
DETAIL: {PLANNEDSTMT
...
:targetlist (
{TARGETENTRY
:expr
{CONST
:consttype 23

You can see this by running EXPLAIN on the original query. It
overflows even though the query is never run:

=# explain select -2147483648::integer;
ERROR: 22003: integer out of range
LOCATION: int84, int8.c:1298
Time: 0.393 ms

The LOCATION line even gives a hint what's going on. 2147483648 was
read as an int8 constant and the - operator returned an int8 and then
the cast (being run during explain as part of preparing the plan)
overflowed.

I wonder why the "out of range" error doesn't print the actual value
it's trying to cast. That would help the user here...

--
greg

Hi,

On 2017-12-21 14:05:07 +0000, Greg Stark wrote:

I wonder why the "out of range" error doesn't print the actual value
it's trying to cast. That would help the user here...

We'd have to mark it as non-leakproof in that case.

Greetings,

Andres Freund

Greg Stark wrote:

The LOCATION line even gives a hint what's going on. 2147483648 was
read as an int8 constant and the - operator returned an int8 and then
the cast (being run during explain as part of preparing the plan)
overflowed.

So this is because this is tokenized as four tokens -- minus, the
numeric value, :: and the identifier type name. This whole thing be
made simpler by turning the literal as one of the unknown type, with the
minus sign inside it. This ends up as two tokens:

select integer '-2147483648';

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 21 December 2017 at 14:13, Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2017-12-21 14:05:07 +0000, Greg Stark wrote:

I wonder why the "out of range" error doesn't print the actual value
it's trying to cast. That would help the user here...

We'd have to mark it as non-leakproof in that case.

Damn that's annoying.

But..... uh, isn't it already leaking that the value is not in 99.99999998% of
the bigint range?

--
greg

On December 21, 2017 10:18:05 PM GMT+01:00, Greg Stark <stark@mit.edu> wrote:

On 21 December 2017 at 14:13, Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2017-12-21 14:05:07 +0000, Greg Stark wrote:

I wonder why the "out of range" error doesn't print the actual value
it's trying to cast. That would help the user here...

We'd have to mark it as non-leakproof in that case.

Damn that's annoying.

But..... uh, isn't it already leaking that the value is not in
99.99999998% of
the bigint range?

Most of the relevant operations have more than one operand, or are aggregates. Especially for actually relevant data ranges. But yes, this is a way to analyze data, we knew that when adding RLS.

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Andres, all,

* Andres Freund (andres@anarazel.de) wrote:

On December 21, 2017 10:18:05 PM GMT+01:00, Greg Stark <stark@mit.edu> wrote:

On 21 December 2017 at 14:13, Andres Freund <andres@anarazel.de> wrote:

On 2017-12-21 14:05:07 +0000, Greg Stark wrote:

I wonder why the "out of range" error doesn't print the actual value
it's trying to cast. That would help the user here...

We'd have to mark it as non-leakproof in that case.

Damn that's annoying.

But..... uh, isn't it already leaking that the value is not in
99.99999998% of
the bigint range?

Most of the relevant operations have more than one operand, or are aggregates. Especially for actually relevant data ranges. But yes, this is a way to analyze data, we knew that when adding RLS.

Leakproof functions actually were introduced with security barrier
views, which pre-dated RLS and is what RLS is built on top of.

This doesn't change anything wrt this, of course, just figured I'd
clarify for anyone following the thread.

Thanks!

Stephen