Unnamed

-- Bug seems to exist with MyISAM query re-writer Where a bit
-- field is moved from the WHERE clause to the condition of a
-- LEFT JOIN
-- Is this the same as Bug #31399 ?

DROP TABLE IF EXISTS transact_test;
CREATE TABLE transact_test (
  transactid INT AUTO_INCREMENT,
  productcode VARCHAR(16) NOT NULL,
  issued BIT(1) NOT NULL,
  userid INT NOT NULL,
  PRIMARY KEY(transactid) 
) ENGINE=myisam;

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  userid INT AUTO_INCREMENT,
  username VARCHAR(20) NOT NULL,
  PRIMARY KEY(userid),
  UNIQUE(username)
) ENGINE=myisam;

INSERT INTO users (userid,username) VALUES(1,'test 1');
INSERT INTO users (userid,username) VALUES(2,'test 2');

INSERT INTO transact_test (productcode,issued,userid) VALUES('Product 1',false,1);
INSERT INTO transact_test (productcode,issued,userid) VALUES('Product 2',false,2);

EXPLAIN EXTENDED SELECT t1.productcode,t1.issued,t2.username FROM transact_test t1 LEFT JOIN users t2 ON t1.userid=t2.userid WHERE t1.issued = false;

SHOW WARNINGS;


-- Query is re-written to :
select `test`.`t1`.`productcode` AS `productcode`,`test`.`t1`.`issued` AS `issued`,`test`.`t2`.`username` AS `username` from `test`
.`transact_test` `t1` left join `test`.`users` `t2` on(((`test`.`t1`.`issued` = 0) and (`test`.`t2`.`userid` = 0))) where (`test`.`t1`.`issued` = 0) 

-- notice LEFT JOIN t1.issued = 0 AND t2.userid = 0 WHERE t1.issued = 0


-- Query works when false is replaced with b'0'

EXPLAIN EXTENDED SELECT t1.productcode,t1.issued,t2.username FROM transact_test t1 LEFT JOIN users t2 ON t1.userid=t2.userid WHERE t1.issued = b'0';


-- An equality test is true for false = b'0'

SELECT false = b'0';



-- Codex`@Undernet