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

From: Satoshi Nagayasu <[email-masked]>
Date: Tue, 22 Jun 2010 17:27:47 +0900

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=#
---------------------------------------------------------------------

-- 
NAGAYASU Satoshi <[email-masked]>

---

From: Thom Brown <[email-masked]>
Date: Tue, 22 Jun 2010 09:36:30 +0100

> Is this correct? Any suggestions?

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

---

From: Magnus Hagander <[email-masked]>
Date: Tue, 22 Jun 2010 10:44:42 +0200

> 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/

---

From: Thom Brown <[email-masked]>
Date: Tue, 22 Jun 2010 09:48:49 +0100

On 22 June 2010 09:44, Magnus Hagander <[email-masked]> wrote:
> 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

---

From: Satoshi Nagayasu <[email-masked]>
Date: Tue, 22 Jun 2010 17:59:55 +0900

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<[email-masked]>  wrote:

---

From: Thom Brown <[email-masked]>
Date: Tue, 22 Jun 2010 10:46:30 +0100

> so I hope that the message could be more understandable

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

---

From: Thom Brown <[email-masked]>
Date: Tue, 22 Jun 2010 10:57:34 +0100

> postgres=# SELECT -2147483648;

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

Thom

---

From: Satoshi Nagayasu <[email-masked]>
Date: Tue, 22 Jun 2010 22:37:45 +0900

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<[email-masked]>  wrote:
>> On 22 June 2010 09:59, Satoshi Nagayasu<[email-masked]>  wrote:
>>> Magnus,
>>>

---

From: David Fetter <[email-masked]>
Date: Tue, 22 Jun 2010 16:07:54 -0700

> 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 <[email-masked]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: [email-masked]
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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

---

From: Thom Brown <[email-masked]>
Date: Wed, 23 Jun 2010 00:16:06 +0100

> SELECT (-9223372036854775808)::bigint;

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

---

From: Tom Lane <[email-masked]>
Date: Tue, 22 Jun 2010 21:49:37 -0400

Thom Brown <[email-masked]> writes:
> 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

---

From: Thom Brown <[email-masked]>
Date: Wed, 23 Jun 2010 09:17:58 +0100

> 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

---

From: Mike Toews <[email-masked]>
Date: Wed, 23 Jun 2010 07:29:49 -0700

> 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

---

From: Robert Haas <[email-masked]>
Date: Wed, 23 Jun 2010 11:06:07 -0400

> 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