Bug #42058 Bit index fails to return all matching rows
Submitted: 12 Jan 2009 19:00 Modified: 12 Jan 2009 21:49
Reporter: Philip Stoev Email Updates:
Status: Verified Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0, 5.1, 6.0 bzr OS:Any
Assigned to: CPU Architecture:Any

[12 Jan 2009 19:00] Philip Stoev
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.
[12 Jan 2009 21:49] Sveta Smirnova
Thank you for the report.

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