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: | |
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 |
[15 Jan 2008 18:33]
Clifton Kilby III
[16 Jan 2008 5:00]
Valeriy Kravchuk
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.
[27 Apr 2009 0:30]
Douglas Yoder
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.