Thread: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

Is this expected behavior ?

This works:

    ts=# SELECT x'0000000F'::int;
    int4|15

.. but an additional leading zero causes it to fail:

    ts=# SELECT x'00000000F'::int;
    ERROR:  22003: integer out of range
    LOCATION:  bittoint4, varbit.c:1575


|/* Check that the bit string is not too long */
|if (VARBITLEN(arg) > sizeof(result) * BITS_PER_BYTE)
|    ereport(ERROR,
|            (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
|             errmsg("integer out of range")));

Justin

Justin Pryzby <pryzby@telsasoft.com> writes:
> Is this expected behavior ?
>     ts=# SELECT x'00000000F'::int;
>     ERROR:  22003: integer out of range
>     LOCATION:  bittoint4, varbit.c:1575

Yes.  The provided operation is "convert a bitstring of up to 32 bits
to an integer".  It's not "guess whether it's okay to throw away some
bits to make an integer".

As an example, even if you think it's obvious that it would be okay
to convert that bitstring to "15", would it be all right to convert
x'FFFFFFFFF' to "-1" ?

            regards, tom lane

Justin Pryzby <pryzby@telsasoft.com> writes:
> Is this expected behavior ?
>       ts=# SELECT x'00000000F'::int;
>       ERROR:  22003: integer out of range
>       LOCATION:  bittoint4, varbit.c:1575

Yes.  The provided operation is "convert a bitstring of up to 32 bits
to an integer".  It's not "guess whether it's okay to throw away some
bits to make an integer".

On 25/02/17 08:39, John McKown wrote:
> On Fri, Feb 24, 2017 at 1:25 PM, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>wrote:
>
>     On Friday, February 24, 2017, Tom Lane <tgl@sss.pgh.pa.us
>     <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>         Justin Pryzby <pryzby@telsasoft.com> writes:
>         > Is this expected behavior ?
>         >       ts=# SELECT x'00000000F'::int;
>         >       ERROR:  22003: integer out of range
>         >       LOCATION:  bittoint4, varbit.c:1575
>
>         Yes.  The provided operation is "convert a bitstring of up to
>         32 bits
>         to an integer".  It's not "guess whether it's okay to throw
>         away some
>         bits to make an integer".
>
>
>     IME The error message itself is to blame here - we are checking
>     for a malformed (too many characters) integer
>     varbit representation but then reporting that the we somehow got a
>     valid integer but that it is "out of range".
>
>
> ​A better reply would be good. Another possibility is for the parser
> to remove unneeded leading zeros.​
>
[...]

I think the latter would be a good idea!

Cheers,
Gavin

On 2/25/17 at 6:56 AM, Gavin Flower wrote:

>On 25/02/17 08:39, John McKown wrote:
>>On Fri, Feb 24, 2017 at 1:25 PM, David G. Johnston
>>On Friday, February 24, 2017, Tom Lane wrote:
>>
>>Justin Pryzby writes:
>>>Is this expected behavior ?
>>>ts=# SELECT x'00000000F'::int;
>>>ERROR:  22003: integer out of range
>>>LOCATION:  bittoint4, varbit.c:1575
>>
>>Yes.  The provided operation is "convert a bitstring of up to
>>32 bits to an integer".  It's not "guess whether it's okay to
>>throw away some bits to make an integer".
>>
>>IME The error message itself is to blame here - we are checking
>>for a malformed (too many characters) integer varbit
>>representation but then reporting that the we somehow got a valid
>>integer but that it is "out of range".
>>
>>​A better reply would be good. Another possibility is for the parser
>>to remove unneeded leading zeros.​
>>
>[...]
>
>I think the latter would be a good idea!
>
This is interesting in that the views expressed range from
something close to "every bit is sacred" through to something
resembling "drop what's needed to make it work".

My take is PostgreSQL is already pragmatic:

     pendari=# select ((x'FFFFFFFFFFFFFFFF')::bigint)::int;
      int4
     ------
        -1
     (1 row)

Clearly we've quietly dropped a lot of bits moving across this line.

The problem posed in the OP example happens when the bit pattern
is under specifying a long value (or over specifying a short
value), and, in an ideal world, the correct behaviour should be
close to what all well behaved CPUs are already doing:

Opclass             Operand         Action (MSB=most significant bit)
==================|===============|==================================

logical/bitwise     Small->Large    Zero fill most significant, but
                     Large->Small    check which "standard" applies

arthmetic/signed    Small->Large    Propagate sign bit to left
                     Large->Small    Truncate sign bits, error
                                     if sign bits are not all equal,
                                     and not equal to MSB of result

arithmetic/unsig    Small->Large    Zero fill most significant part
                     Large->Small    Truncate from MSB, error if
                                     any truncated bit is not zero

To my mind Tom's reply resembles the bitwise case but I think
the OP's example should ideally have been interpreted in an
arithmetic manner (i.e., treating the extra bits as representing
the sign and nothing more) since the desired result was to be a
signed integer.

But! This gets problematic for something like:  x'FFF67'::bigint

My analogy would have this interpreted as
x'FFFFFFFFFFFFFF67'::bigint whereas the current behaviour is
equivalent to x'00000000000FFF67'::bigint, and I doubt anyone
has the appetite to change this. (Of course we have always known
using bit masks across architectures with different word sizes
was never an easy or safe activity. :)

So, getting back to the OP problem… what's a good parser to do?

I suggest:
1. the error message might be better (i.e., help get the focus
onto the real problem); and/or,
2. consider dropping excess leading zeros when building an
integer value. (I don't think this breaks anything.)

Other than that there really isn't a realisable consistent
behaviour beyond the current strict bitwise interpretation.
Specifically any behaviour which tries to promote or truncate
some "sign" bits in an arithmetically consistent manner is going
to break existing behaviour.

Regards
Gavan Schneider