BUG #1135: integer::bit returns incorrect results

The following bug has been logged online:

Bug reference: 1135
Logged by: elein

Email address: elein(at)varlena(dot)com

PostgreSQL version: 7.4

Operating system: SuSE

Description: integer::bit returns incorrect results

Details:

* There exists a cast utils/adt.c/bitfromint4()
in pg_casts listed to cast an integer to a bit.

This cast seems to always set the value to 0.
On cursory glance bitfromint4 seems right, but
the shortcut shifting could be wrong. (Why isn't
just the low bit set?).

If I have time, I'll look more carefully, and
perhaps submit a patch unless someone gets to
it before I do.

* There is no cast from an integer to a bit varying.

If there exists a cast from integer to bit (in question :-)
there should be one from integer to bit varying.
It should be a straight copy.

These all return 0.
select 1::bit;
select 2::bit;
select 111::bit;
select 101::bit;

elein=# select 1::bit varying;
ERROR: cannot cast type integer to bit varying

Side note: the text string cast to bit and bit varying
works fine and the digits are limited to 1s and 0s.
But that should not be how integer to bit/bit varying should
work.

elein=# select '101'::bit;
bit
-----
1
(1 row)

elein=# select '2'::bit;
ERROR: "2" is not a valid binary digit

It may be that the cast is taking the high bit and not
the low bit. I would have thought it went the other way.

On Sun, Apr 18, 2004 at 04:04:08PM -0300, PostgreSQL Bugs List wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1135
> Logged by: elein
>
> Email address: elein(at)varlena(dot)com
>
> PostgreSQL version: 7.4
>
> Operating system: SuSE
>
> Description: integer::bit returns incorrect results
>
> Details:
>
>
> * There exists a cast utils/adt.c/bitfromint4()
> in pg_casts listed to cast an integer to a bit.
>
> This cast seems to always set the value to 0.
> On cursory glance bitfromint4 seems right, but
> the shortcut shifting could be wrong. (Why isn't
> just the low bit set?).
>
> If I have time, I'll look more carefully, and
> perhaps submit a patch unless someone gets to
> it before I do.
>
> * There is no cast from an integer to a bit varying.
>
> If there exists a cast from integer to bit (in question :-)
> there should be one from integer to bit varying.
> It should be a straight copy.
>
> These all return 0.
> select 1::bit;
> select 2::bit;
> select 111::bit;
> select 101::bit;
>
> elein=# select 1::bit varying;
> ERROR: cannot cast type integer to bit varying
>
>
> Side note: the text string cast to bit and bit varying
> works fine and the digits are limited to 1s and 0s.
> But that should not be how integer to bit/bit varying should
> work.
>
> elein=# select '101'::bit;
> bit
> -----
> 1
> (1 row)
>
> elein=# select '2'::bit;
> ERROR: "2" is not a valid binary digit
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

"PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> writes:
> These all return 0.
> select 1::bit;
> select 2::bit;
> select 111::bit;
> select 101::bit;

"bit" means "bit(1)" per SQL spec, so only the first of these could
possibly act as you're expecting anyway.

The reason none of them do is that the coercion function actually yields
bit(32), with the MSB of the integer at the left, and then coercion to
bit(1) drops all but the sign bit. So for example

regression=# select (255)::bit;
bit
-----
0
(1 row)

regression=# select (255)::bit(32);
bit
----------------------------------
00000000000000000000000011111111
(1 row)

regression=# select (-255)::bit(32);
bit
----------------------------------
11111111111111111111111100000001
(1 row)

regression=# select (-255)::bit(1);
bit
-----
1
(1 row)

There was previous discussion of this a month or so ago, but nobody
could come up with a reasonable definition that didn't have large
implementability or backwards-compatibility issues ...

regards, tom lane