MySQL Bugs: #42058: Bit index fails to return all matching rows

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:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0, 5.1, 6.0 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

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