INTEGER range ("-2147483648" is not accepted.)

2010/6/22 Satoshi Nagayasu <satoshi.nagayasu@gmail.com>:

Hi all,

I've found a bit strange thing on the INTEGER range in the official manual.

http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html

According to the official manual, the INTEGER range is "-2147483648 to +2147483647".
However, my example in below shows that "-2147483648" is not accepted.

Is this correct? Any suggestions?

Regards,

---------------------------------------------------------------------
template1=# SELECT version();
                                                 version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 32-bit
(1 row)

template1=# SELECT -2147483647::integer;
 ?column?
-------------
 -2147483647
(1 row)

template1=# SELECT -2147483648::integer;
ERROR:  integer out of range
template1=# SELECT +2147483648::integer;
ERROR:  integer out of range
template1=# SELECT +2147483647::integer;
 ?column?
------------
 2147483647
(1 row)

template1=#
---------------------------------------------------------------------

Hmm... yes, that's not what I'd expect either:

postgres=# SELECT -32768::smallint;
ERROR: smallint out of range
postgres=# SELECT -9223372036854775808::bigint;
ERROR: bigint out of range

I think those min values are all out by 1.

Thom

On Tue, Jun 22, 2010 at 10:27 AM, Satoshi Nagayasu
<satoshi.nagayasu@gmail.com> wrote:

Hi all,

I've found a bit strange thing on the INTEGER range in the official manual.

http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html

According to the official manual, the INTEGER range is "-2147483648 to +2147483647".
However, my example in below shows that "-2147483648" is not accepted.

Is this correct? Any suggestions?

template1=# SELECT -2147483648::integer;
ERROR:  integer out of range

This gets parsed as "cast 2147483648 to integer, then take it
negative". Which overflows, because it can only go up to 2147483647.
What you want is:

postgres=# select (-2147483648)::integer;
int4
-------------
-2147483648
(1 row)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On 22 June 2010 09:44, Magnus Hagander <magnus@hagander.net> wrote:

On Tue, Jun 22, 2010 at 10:27 AM, Satoshi Nagayasu
<satoshi.nagayasu@gmail.com> wrote:

Hi all,

I've found a bit strange thing on the INTEGER range in the official manual.

http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html

According to the official manual, the INTEGER range is "-2147483648 to +2147483647".
However, my example in below shows that "-2147483648" is not accepted.

Is this correct? Any suggestions?

template1=# SELECT -2147483648::integer;
ERROR:  integer out of range

This gets parsed as "cast 2147483648 to integer

Why? And if so, it would probably be more useful if the error message
was something more like:
ERROR: integer 2147483648 out of range

That would at least show the user what the value was seen as by the parser.

Thom

Magnus,

Thanks for your advice. I've understood how it happens.

However, it looks tricky and difficult to understand,
so I hope that the message could be more understandable
as Thom mentioned.

Regards,

On 2010/06/22 17:48, Thom Brown wrote:

On 22 June 2010 09:44, Magnus Hagander<magnus@hagander.net> wrote:

On Tue, Jun 22, 2010 at 10:27 AM, Satoshi Nagayasu
<satoshi.nagayasu@gmail.com> wrote:

Hi all,

I've found a bit strange thing on the INTEGER range in the official manual.

http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html

According to the official manual, the INTEGER range is "-2147483648 to +2147483647".
However, my example in below shows that "-2147483648" is not accepted.

Is this correct? Any suggestions?

template1=# SELECT -2147483648::integer;
ERROR: integer out of range

This gets parsed as "cast 2147483648 to integer

Why? And if so, it would probably be more useful if the error message
was something more like:
ERROR: integer 2147483648 out of range

That would at least show the user what the value was seen as by the parser.

Thom

--
NAGAYASU Satoshi <satoshi.nagayasu@gmail.com>

On 22 June 2010 09:59, Satoshi Nagayasu <satoshi.nagayasu@gmail.com> wrote:

Magnus,

Thanks for your advice. I've understood how it happens.

However, it looks tricky and difficult to understand,
so I hope that the message could be more understandable
as Thom mentioned.

Regards,

This does appear to be a gotcha, as the following returns a negative
integer as expected:

postgres=# SELECT -2147483648;
?column?
-------------
-2147483648
(1 row)

postgres=# SELECT pg_typeof(-2147483648);
pg_typeof
-----------
integer
(1 row)

And just in case...

postgres=# SELECT pg_typeof(test.my_num) FROM (SELECT -2147483648) AS
test(my_num);
pg_typeof
-----------
integer
(1 row)

So it's affected by the cast operator?

Thom

On 22 June 2010 10:46, Thom Brown <thombrown@gmail.com> wrote:

On 22 June 2010 09:59, Satoshi Nagayasu <satoshi.nagayasu@gmail.com> wrote:

Magnus,

Thanks for your advice. I've understood how it happens.

However, it looks tricky and difficult to understand,
so I hope that the message could be more understandable
as Thom mentioned.

Regards,

This does appear to be a gotcha, as the following returns a negative
integer as expected:

postgres=# SELECT -2147483648;
 ?column?
-------------
 -2147483648
(1 row)

postgres=# SELECT pg_typeof(-2147483648);
 pg_typeof
-----------
 integer
(1 row)

And just in case...

postgres=# SELECT pg_typeof(test.my_num) FROM (SELECT -2147483648) AS
test(my_num);
 pg_typeof
-----------
 integer
(1 row)

So it's affected by the cast operator?

Thom

Actually, come to think of it, shouldn't we have a gotchas page on the wiki?

Thom

Thom,

Actually, come to think of it, shouldn't we have a gotchas page on the wiki?

I agree with that it should be described in some tech document,
but I don't have any good idea where/how it should be written.

Basically, it's a parser issue, but app developers may meet it
on their type casting (my guess), and it's a bit tricky.

Regards,

On 2010/06/22 18:57, Thom Brown wrote:

On 22 June 2010 10:46, Thom Brown<thombrown@gmail.com> wrote:

On 22 June 2010 09:59, Satoshi Nagayasu<satoshi.nagayasu@gmail.com> wrote:

Magnus,

Thanks for your advice. I've understood how it happens.

However, it looks tricky and difficult to understand,
so I hope that the message could be more understandable
as Thom mentioned.

Regards,

This does appear to be a gotcha, as the following returns a negative
integer as expected:

postgres=# SELECT -2147483648;
?column?
-------------
-2147483648
(1 row)

postgres=# SELECT pg_typeof(-2147483648);
pg_typeof
-----------
integer
(1 row)

And just in case...

postgres=# SELECT pg_typeof(test.my_num) FROM (SELECT -2147483648) AS
test(my_num);
pg_typeof
-----------
integer
(1 row)

So it's affected by the cast operator?

Thom

Actually, come to think of it, shouldn't we have a gotchas page on the wiki?

Thom

--
NAGAYASU Satoshi <satoshi.nagayasu@gmail.com>

On Tue, Jun 22, 2010 at 09:36:30AM +0100, Thom Brown wrote:

2010/6/22 Satoshi Nagayasu <satoshi.nagayasu@gmail.com>:

Hi all,

I've found a bit strange thing on the INTEGER range in the official manual.

http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html

According to the official manual, the INTEGER range is "-2147483648 to +2147483647".
However, my example in below shows that "-2147483648" is not accepted.

Is this correct? Any suggestions?

Regards,

---------------------------------------------------------------------
template1=# SELECT version();
� � � � � � � � � � � � � � � � � � � � � � � � �version
------------------------------------------------------------------------------------------------------------
�PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 32-bit
(1 row)

template1=# SELECT -2147483647::integer;
�?column?
-------------
�-2147483647
(1 row)

template1=# SELECT -2147483648::integer;
ERROR: �integer out of range
template1=# SELECT +2147483648::integer;
ERROR: �integer out of range
template1=# SELECT +2147483647::integer;
�?column?
------------
�2147483647
(1 row)

template1=#
---------------------------------------------------------------------

Hmm... yes, that's not what I'd expect either:

postgres=# SELECT -32768::smallint;
ERROR: smallint out of range
postgres=# SELECT -9223372036854775808::bigint;
ERROR: bigint out of range

I think those min values are all out by 1.

Nope. Same problem.

SELECT (-32768)::smallint;
-32768

SELECT (-9223372036854775808)::bigint;
-9223372036854775808

I agree that the appropriate error message should complain about the
actual error, which is that 32768, or 2147483648, or
9223372036854775808, as the case may be, is out of range in the
positive direction. Possibly the "hint" might mention that :: binds
tighter than - does.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 23 June 2010 00:07, David Fetter <david@fetter.org> wrote:

On Tue, Jun 22, 2010 at 09:36:30AM +0100, Thom Brown wrote:

2010/6/22 Satoshi Nagayasu <satoshi.nagayasu@gmail.com>:

Hi all,

I've found a bit strange thing on the INTEGER range in the official manual.

http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html

According to the official manual, the INTEGER range is "-2147483648 to +2147483647".
However, my example in below shows that "-2147483648" is not accepted.

Is this correct? Any suggestions?

Regards,

---------------------------------------------------------------------
template1=# SELECT version();
                                                 version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 32-bit
(1 row)

template1=# SELECT -2147483647::integer;
 ?column?
-------------
 -2147483647
(1 row)

template1=# SELECT -2147483648::integer;
ERROR:  integer out of range
template1=# SELECT +2147483648::integer;
ERROR:  integer out of range
template1=# SELECT +2147483647::integer;
 ?column?
------------
 2147483647
(1 row)

template1=#
---------------------------------------------------------------------

Hmm... yes, that's not what I'd expect either:

postgres=# SELECT -32768::smallint;
ERROR:  smallint out of range
postgres=# SELECT -9223372036854775808::bigint;
ERROR:  bigint out of range

I think those min values are all out by 1.

Nope.  Same problem.

SELECT (-32768)::smallint;
 -32768

SELECT (-9223372036854775808)::bigint;
 -9223372036854775808

I agree that the appropriate error message should complain about the
actual error, which is that 32768, or 2147483648, or
9223372036854775808, as the case may be, is out of range in the
positive direction.  Possibly the "hint" might mention that :: binds
tighter than - does.

Is that the right behaviour though? Shouldn't the signed value reach
the cast step rather than the absolute value? Or maybe Postgres could
implicitly accept -12345::integer to be (-12345)::integer. Is there a
blocking reason as to why it must work this way? Am I asking too many
questions? Was that last question necessary?

Thom

Thom Brown <thombrown@gmail.com> writes:

Is that the right behaviour though? Shouldn't the signed value reach
the cast step rather than the absolute value? Or maybe Postgres could
implicitly accept -12345::integer to be (-12345)::integer. Is there a
blocking reason as to why it must work this way?

Yes. There is no reason to assume that - means the same thing for every
datatype. In general, :: should (and does) bind tighter than *every*
operator, to ensure that the appropriately typed operator is applied.

regards, tom lane

On 23 June 2010 02:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thom Brown <thombrown@gmail.com> writes:

Is that the right behaviour though?  Shouldn't the signed value reach
the cast step rather than the absolute value?  Or maybe Postgres could
implicitly accept -12345::integer to be (-12345)::integer.  Is there a
blocking reason as to why it must work this way?

Yes.  There is no reason to assume that - means the same thing for every
datatype.  In general, :: should (and does) bind tighter than *every*
operator, to ensure that the appropriately typed operator is applied.

Okay. I'll admit that this won't be a common case, but could the
error message make reference to the value it took?

Thom

On 22 June 2010 18:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thom Brown <thombrown@gmail.com> writes:

Is that the right behaviour though?  Shouldn't the signed value reach
the cast step rather than the absolute value?  Or maybe Postgres could
implicitly accept -12345::integer to be (-12345)::integer.  Is there a
blocking reason as to why it must work this way?

Yes.  There is no reason to assume that - means the same thing for every
datatype.  In general, :: should (and does) bind tighter than *every*
operator, to ensure that the appropriately typed operator is applied.

Sorry for adding to the non-DOC drift, but why is - assumed to be a
unary operator on an unsigned integer, rather than parsed as part of
an integer? Integers have digits with an optional - or + prefix (not
unary operators). E.g., ([+\-]?[0-9]+)

-Mike

On Wed, Jun 23, 2010 at 10:29 AM, Mike Toews <mwtoews@gmail.com> wrote:

On 22 June 2010 18:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thom Brown <thombrown@gmail.com> writes:

Is that the right behaviour though?  Shouldn't the signed value reach
the cast step rather than the absolute value?  Or maybe Postgres could
implicitly accept -12345::integer to be (-12345)::integer.  Is there a
blocking reason as to why it must work this way?

Yes.  There is no reason to assume that - means the same thing for every
datatype.  In general, :: should (and does) bind tighter than *every*
operator, to ensure that the appropriately typed operator is applied.

Sorry for adding to the non-DOC drift, but why is - assumed to be a
unary operator on an unsigned integer, rather than parsed as part of
an integer? Integers have digits with an optional - or + prefix (not
unary operators). E.g., ([+\-]?[0-9]+)

You can't assume that a dash followed by digits is always a negative
number. Consider:

SELECT 10-4;

If you we interpret this as "10" followed by "-4", it's a syntax
error. You have to treat it as a separate token and work out later
whether it's a binary operator or a prefix operator.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company