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
