Обсуждение: BUG #16247: Cast error on integer
The following bug has been logged on the website: Bug reference: 16247 Logged by: Dilip Tripathy Email address: ddtripathy@yahoo.com PostgreSQL version: 12.1 Operating system: Windows 7 professional Description: I have defined a procedure with the following signature: create or replace procedure eventattributes_getadd( _cust varchar(128), _site varchar(128), _devctrluid int, _dateid int, _fiveminprdid smallint, _metric varchar(128), _sd varchar(256), _value varchar(32)) language 'plpgsql' When I execute the following call it succeeds. call eventattributes_getadd( _cust => 'cust'::varchar(128), _site => 'site'::varchar(128), _devctrluid => cast (-2147483648 as integer), _dateid => 20200206::int, _fiveminprdid => 1700::smallint, _metric => 'somebogusmetric'::varchar(128), _sd => '>>>ST<<<=mc4;>>>DLN<<<=scanner_1;>>>DG<<<=scanner_1;>>>DGT<<<=scanner;>>>DT<<<=scanner'::varchar(256), _value => '28'::varchar(32)); However, when I execute the following call I get: ERROR: integer out of range SQL state: 22003 call eventattributes_getadd( _cust => 'cust'::varchar(128), _site => 'site'::varchar(128), _devctrluid => -2147483648::int, _dateid => 20200206::int, _fiveminprdid => 1700::smallint, _metric => 'somebogusmetric'::varchar(128), _sd => '>>>ST<<<=mc4;>>>DLN<<<=scanner_1;>>>DG<<<=scanner_1;>>>DGT<<<=scanner;>>>DT<<<=scanner'::varchar(256), _value => '28'::varchar(32)); The only difference between the two calls is how I'm casting the integer. The second call is choking on "_devctrluid => -2147483648::int". But, if I change that value to "-2147483647" the second call succeeds.
On Thu, Feb 6, 2020 at 2:44 PM PG Bug reporting form <noreply@postgresql.org> wrote:
The only difference between the two calls is how I'm casting the integer.
Not a bug - operator precedence:
David J.
Hi, On 2020-02-06 21:43:41 +0000, PG Bug reporting form wrote: > The only difference between the two calls is how I'm casting the integer. > The second call is choking on "_devctrluid => -2147483648::int". But, if I > change that value to "-2147483647" the second call succeeds. I don't think that's a bug. -2147483648::int is parsed as -((2147483648)::int). 2147483648 gets parsed as an int8 (due to its width), but then you're casting the result to an int4. And 2147483648 is not representable as a signed 32bit integer. It fails before getting to negating the result of the cast. For precedence see: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE Whereas '-2147483648'::int4 gets read directly as int4, including the sign. And wheras 2147483648 is not representable as an int4, -2147483648 is. Greetings, Andres Freund