BUG #15071: Error in PostgreSQL-specific :: type cast
The following bug has been logged on the website:
Bug reference: 15071
Logged by: Yuriy Beliy
Email address: whiteman.kr@gmail.com
PostgreSQL version: 10.1
Operating system: Windows 10
Description:
PostgreSQL-specific :: type casts for lowest values of integer types are
produce "Out of range" errors:
SELECT -32768::smallint
^ERROR: smallint out of range
SELECT -2147483648::integer
^ERROR: integer out of range
SELECT -9223372036854775808::bigint
^ERROR: bigint out of range
Standard-syntax type casts are workes correctly:
SELECT cast(-32768 as smallint)
^Ok
SELECT cast(-2147483648 as integer)
^Ok
SELECT cast(-9223372036854775808 as bigint)
^Ok
For highest values (+32767, +2147483647, +9223372036854775807) - :: type
cast works correctly.
On 16 Feb 2018, at 12:57, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 15071
Logged by: Yuriy Beliy
Email address: whiteman.kr@gmail.com
PostgreSQL version: 10.1
Operating system: Windows 10
Description:PostgreSQL-specific :: type casts for lowest values of integer types are
produce "Out of range" errors:SELECT -32768::smallint
^ERROR: smallint out of range
The parser will represents this as the numeric portion, 32768, and a “-“ in an
expression of type AEXPR_OP. When passed to i4toi2(), only the numeric part is
passed and the out of range is due to 32768 > SHRT_MAX (same type of issue for
the other types). Casting from non-numeric, like text for example, to smallint
works since it isn’t parsed into an “-“ expr: '-32768'::smallint;
Applying the “-“ op before passing to the typecast function seems the easy
answer, or passing the “-“ separately (which seems kludgier) This is quite old
code though, so I don’t know how much else that would break?
cheers ./daniel
Daniel Gustafsson <daniel@yesql.se> writes:
On 16 Feb 2018, at 12:57, PG Bug reporting form <noreply@postgresql.org> wrote:
PostgreSQL-specific :: type casts for lowest values of integer types are
produce "Out of range" errors:SELECT -32768::smallint
^ERROR: smallint out of range
The parser will represents this as the numeric portion, 32768, and a “-“ in an
expression of type AEXPR_OP.
Right. The point is that :: binds more tightly than unary minus[1]https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-PRECEDENCE,
so this is read as "-(32768::smallint)", and the out-of-range failure
is entirely appropriate. You can avoid the problem with
(-32768)::smallint, or as you mentioned with '-32768'::smallint.
While we hear complaints about this regularly, I do not think that
changing the precedence order would be a good idea. It's at least
conceivable that the behavior of the unary-minus operator would be
different for different datatypes, so enforcing the datatype selection
before applying the operator seems like the right decision from an
abstract-data-type standpoint. (This argument explains why :: has
such a high precedence to begin with.) You can even construct cases,
admittedly lame, where changing the precedence would result in failures
where there had been none before.
regards, tom lane
[1]: https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-PRECEDENCE
On Fri, Feb 16, 2018 at 12:57 PM, PG Bug reporting form
<noreply@postgresql.org> wrote:
...
SELECT -32768::smallint
^ERROR: smallint out of range
This is interpreted as -(32768::smallint)
...
Standard-syntax type casts are workes correctly:
SELECT cast(-32768 as smallint)
This is not.
When in doubt, put parenthesis
postgres=# select (-32768)::smallint;
int2
--------
-32768
(1 row)
postgres=# select -(32768::smallint);
ERROR: smallint out of range
postgres=# select -32768::smallint;
ERROR: smallint out of range
And IIRC, expression with implicit casts ( like when comparing
constnat to columns) work because they use the conversion from
text/unknown...
postgres=# select '-32768'::smallint;
int2
--------
-32768
(1 row)
Francisco Olarte.