MySQL Bugs: #33877: BIT(1) in Where clause causes Impossible WHERE

Bug #33877	BIT(1) in Where clause causes Impossible WHERE
Submitted:	15 Jan 2008 18:33	Modified:	16 Jan 2008 5:00
Reporter:	Clifton Kilby III	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.54, 5.0.45-community	OS:	Windows (XP SP2 Pro)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
In certian cases having a SELECT with a WHERE bitColName=0 returns an empty set, and the EXPLAIN EXTENDED shows an Impossible WHERE.

How to repeat:
Using the table:
CREATE TABLE  `test`.`test` (
  `id` bigint(20) NOT NULL auto_increment,
  `correct` bit(1) NOT NULL,
  `question` bigint(20) NOT NULL,
  `answerText` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

And data:
insert into test.test (correct, question, answerText) values (0,1,"answer1"),(0,1,"answer2"),(0,1,"answer3"),(1,1,"answer4");

select * from test.test; returns 4 rows;
select * from test.test where correct=0 and question=1; returns empty set;
select * from test.test where correct=b'0' and question=1; returns 3 rows;
select * from test.test where correct='0' and question=1; returns 3 rows;
select * from test.test where correct=1 and question=1; returns 1 row;
select * from test.test where correct=false and question=1; returns 0 rows;
select * from test.test where correct=true and question=1; returns 1 row.
select * from test.test where correct+0=false and question=1; returns 3 rows;

Changing the table backing store to InnoDB causes correct=0|false queries to return 3 rows.

Suggested fix:
I am not sure of the reason for this. I would suggest that either comparing bits to numbers fail across the board (I.E. correct=1 also returns 0 rows), or that when comparing a number to a bit field, internally parse the number as its bit equivalent (I.E. correct=0 parses as correct=0b0 and correct=7 parses as correct=0b111). 
I am not a fan of having bit to number comparison fail. It is assumed in many places that 0=0b0=false, and 1=0b1=true. This could cause breaking changes in many production environments.
As this behavior works in other storage engines. The best solution is probably the latter.

Thank you for a bug report. Verified just as described also on 5.0.54:

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.54-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE  `test`.`test` (
    ->   `id` bigint(20) NOT NULL auto_increment,
    ->   `correct` bit(1) NOT NULL,
    ->   `question` bigint(20) NOT NULL,
    ->   `answerText` text NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test.test (correct, question, answerText) values
    -> (0,1,"answer1"),(0,1,"answer2"),(0,1,"answer3"),(1,1,"answer4");
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test.test;
+----+---------+----------+------------+
| id | correct | question | answerText |
+----+---------+----------+------------+
|  5 |         |        1 | answer1    |
|  6 |         |        1 | answer2    |
|  7 |         |        1 | answer3    |
|  8 | ☺       |        1 | answer4    |
+----+---------+----------+------------+
4 rows in set (0.00 sec)

mysql> select * from test.test where correct=0 and question=1;
Empty set (0.00 sec)

mysql> select * from test.test where correct=b'0' and question=1;
+----+---------+----------+------------+
| id | correct | question | answerText |
+----+---------+----------+------------+
|  5 |         |        1 | answer1    |
|  6 |         |        1 | answer2    |
|  7 |         |        1 | answer3    |
+----+---------+----------+------------+
3 rows in set (0.00 sec)

mysql> select * from test.test where correct='0' and question=1;
+----+---------+----------+------------+
| id | correct | question | answerText |
+----+---------+----------+------------+
|  5 |         |        1 | answer1    |
|  6 |         |        1 | answer2    |
|  7 |         |        1 | answer3    |
+----+---------+----------+------------+
3 rows in set (0.00 sec)

mysql> select * from test.test where correct=1 and question=1;
+----+---------+----------+------------+
| id | correct | question | answerText |
+----+---------+----------+------------+
|  8 | ☺       |        1 | answer4    |
+----+---------+----------+------------+
1 row in set (0.00 sec)

mysql> select * from test.test where correct=false and question=1;
Empty set (0.00 sec)

mysql> select * from test.test where correct=true and question=1;
+----+---------+----------+------------+
| id | correct | question | answerText |
+----+---------+----------+------------+
|  8 | ☺       |        1 | answer4    |
+----+---------+----------+------------+
1 row in set (0.00 sec)

mysql> select * from test.test where correct+0=false and question=1;
+----+---------+----------+------------+
| id | correct | question | answerText |
+----+---------+----------+------------+
|  5 |         |        1 | answer1    |
|  6 |         |        1 | answer2    |
|  7 |         |        1 | answer3    |
+----+---------+----------+------------+
3 rows in set (0.00 sec)

mysql> alter table test engine=InnoDB;
Query OK, 4 rows affected (0.23 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test.test where correct=0 and question=1;
+----+---------+----------+------------+
| id | correct | question | answerText |
+----+---------+----------+------------+
|  5 |         |        1 | answer1    |
|  6 |         |        1 | answer2    |
|  7 |         |        1 | answer3    |
+----+---------+----------+------------+
3 rows in set (0.00 sec)

mysql> select * from test.test where correct=false and question=1;
+----+---------+----------+------------+
| id | correct | question | answerText |
+----+---------+----------+------------+
|  5 |         |        1 | answer1    |
|  6 |         |        1 | answer2    |
|  7 |         |        1 | answer3    |
+----+---------+----------+------------+
3 rows in set (0.00 sec)

So, InnoDB shows different results than MyISAM, and this inconsistency is surely a bug, whatever the intended behaviour is.

I've had luck with bit(1) fields by doing this:

WHERE myBitField

Rather than 

WHERE myBitField = 1

But good luck writing a stored proc to do this.  Only seems to work with ad-hoc SQL.