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