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

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:
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
"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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend