BUG #16013: Unexpected results from bit field query
The following bug has been logged on the website:
Bug reference: 16013
Logged by: Daryl Waycott
Email address: daryl.waycott@edgeintelligence.com
PostgreSQL version: 9.6.9
Operating system: Ubuntu 18.04.2 LTS
Description:
A query using a right bit shift and an equality on a bit field seems to
return incorrect results. See the following snippet:
DROP TABLE IF EXISTS table1;
CREATE TABLE table1(col_bit_10_1 bit(10) NULL);
INSERT INTO table1 VALUES (B'0000000000');
INSERT INTO table1 VALUES (B'0000000001');
select col_bit_10_1>>1 from table1;
select col_bit_10_1 from table1 where col_bit_10_1>>1 = B'0000000000';
Results:
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
?column?
------------
0000000000
0000000000
(2 rows)
col_bit_10_1
--------------
0000000000
(1 row)
The second result set should include both rows.
PG Bug reporting form <noreply@postgresql.org> writes:
A query using a right bit shift and an equality on a bit field seems to
return incorrect results.
Ugh. bit_cmp expects that any unused bits in the last byte
are guaranteed zero, but bitshiftright isn't ensuring that.
Didn't look to see if the same bug exists anywhere else ...
regards, tom lane
I wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
A query using a right bit shift and an equality on a bit field seems to
return incorrect results.
Ugh. bit_cmp expects that any unused bits in the last byte
are guaranteed zero, but bitshiftright isn't ensuring that.
Didn't look to see if the same bug exists anywhere else ...
After digging around, it seems that no other bit functions have
this issue. I've pushed a fix --- thanks for the report!
regards, tom lane