Обсуждение: 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],
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.