MySQL Bugs: #16250: BIT value with index cannot be compared with stringified number

Bug #16250	BIT value with index cannot be compared with stringified number
Submitted:	6 Jan 2006 13:17	Modified:	17 Dec 2007 15:56
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.17-BK, 5.0.19-BK, 5.0.17	OS:	Linux (Linux, freebsd)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
comparing a column of typ BIT(1) to a string like '1' works only if the column does not have an index.

if the column is indexed, no results are returned.
If the column is not null and indexed, an additional warning is issued.

How to repeat:
create table t1 (b bit(1)); insert into t1 values (0),(1),(1);

select * from t1 where b='1';
#+---+
#| b |
#+---+
#|  |
#|  |
#+---+

alter table t1 add index (b);
select * from t1 where b='1';
#Empty set (0.00 sec)

drop table t1;

create table t1 (b bit(1) not null); insert into t1 values (0),(1),(1);

select * from t1 where b='1';
#+---+
#| b |
#+---+
#|  |
#|  |
#+---+

alter table t1 add index (b);
select * from t1 where b='1';
# Empty set, 1 warning (0.01 sec)
show warnings;
# Warning | 1264 | Out of range value adjusted for column 'b' at row 1 

drop table t1;

Suggested fix:
-

Thank you for a bug report. Verified just as described also on 5.0.19-BK (ChangeSet@1.2020, 2006-01-06 08:34:55-08:00) on Linux. Exactly the same results as in your test (with MyISAM table).

On 4.1.17-BK (ChangeSet@1.2471, 2006-01-04 17:35:30+03:00) I've got a crash:

Cannot determine thread, fp=0xb9755a2c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81227fd
0x64ef18
0x9a91c68
0x81392b7
0x81330a1
0x8132d72
0x813256d
0x64879c
0x49527a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x9ac32e8 = select * from t1 where b='1'
thd->thread_id=1

Stack trace resolved to the following:

0x81227fd handle_segfault + 565
0x64ef18 (?)
0x9a91c68 _end + 22823476
0x81392b7 _Z11mysql_parseP3THDPcj + 83
0x81330a1 _Z16dispatch_command19enum_server_commandP3THDPcj + 753
0x8132d72 _Z10do_commandP3THD + 122
0x813256d handle_one_connection + 789
0x64879c (?)
0x49527a (?)

Tried it with the latest version (4.1-bk and 5.0-bk). Both versions don't exhibit the problem anymore.
There were a lot of bugs fixed that touch on the subject (e.g. 31928).