Bug #42058 Bit index fails to return all matching rows

Description:
Bit fields with values of zero are not returned in a WHERE expression that compares them to a negative value, even though they are properly returned on an expression that compares them to zero.

How to repeat:
create table t1 (f1 bit, key(f1));
insert into t1 values (b'1'),(b'0'),(b'1'),(b'0'),(b'1'),(b'0'),(b'1'),(b'0'),(b'1'),(b'0');
select f1 + 0 from t1 where f1 >= 0;
select f1 + 0 from t1 where f1 > -1;
drop table t1;

The two SELECTs should return the same results since they are mathematically equivalent. For all storage engines, the first SELECT returns all rows.

In Innodb and Falcon , the "> -1" select returns only rows where f1 = 1;
In MyISAM and Maria , the "> -1" select returns zero rows.

Suggested fix:
Even though bit fields are weird and having an index on them is even weirder, there is no reason ">= 0" to return a different result from "> -1".

The Maria/MyISAM behavior may be a separate bug.

Thank you for the report.

Verified as described with exception: Falcon returns correct results and InnoDB returns no results with version 5.1

Posted by developer:
 
Closing as a duplicate of Bug#11751809 (public Bug #42803), “Field_bit does not have unsigned_flag field, can lead to bad memory access.”

The fix explicitly treats BIT fields as unsigned when constructing ranges against negative constants, addressing the reported difference between f1 >= 0 and f1 > -1.

Verification of the original testcase confirms the correction in 5.0.88 compared with 5.0.87 on both InnoDB and MyISAM, and in 5.1.41 compared with 5.1.40 on InnoDB. Both queries return the expected ten rows in current MySQL 26.10.0 with both the traditional and hypergraph optimizers, on both engines.