Thread: BUG #14986: -2147483648 is minimum value of integer but-2147483648::integer fails (out of range).
BUG #14986: -2147483648 is minimum value of integer but-2147483648::integer fails (out of range).
From
PG Bug reporting form
Date:
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.
Re: BUG #14986: -2147483648 is minimum value of integer but-2147483648::integer fails (out of range).
From
Magnus Hagander
Date:
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)
Re: BUG #14986: -2147483648 is minimum value of integer but-2147483648::integer fails (out of range).
From
Greg Stark
Date:
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
Re: BUG #14986: -2147483648 is minimum value of integer but-2147483648::integer fails (out of range).
From
Andres Freund
Date:
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
Re: BUG #14986: -2147483648 is minimum value of integer but-2147483648::integer fails (out of range).
From
Alvaro Herrera
Date:
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
Re: BUG #14986: -2147483648 is minimum value of integer but-2147483648::integer fails (out of range).
From
Greg Stark
Date:
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
Re: BUG #14986: -2147483648 is minimum value of integer but -2147483648::integer fails (out of range).
From
Andres Freund
Date:
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.
Re: BUG #14986: -2147483648 is minimum value of integer but-2147483648::integer fails (out of range).
From
Stephen Frost
Date:
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