Code sample for http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/

reproduce-bit-bug.sql

drop temporary table if exists left1;
drop temporary table if exists left2;
drop temporary table if exists right1;
drop temporary table if exists right2;
drop temporary table if exists right3;

create temporary table left1 (
    col1 int unsigned    not null auto_increment primary key,
    col2 bit             not null default 0,
    col3 bit             not null default 0
) type=MyISAM;

-- Exactly the same as left1, but will only have one row.
create temporary table left2 (
    col1 int unsigned    not null auto_increment primary key,
    col2 bit             not null default 0,
    col3 bit             not null default 0
) type=MyISAM;

create temporary table right1 (
    col1 int unsigned    not null auto_increment primary key,
    col2 bit             not null default 0,
    col3 int unsigned    not null,
    col4 varchar(50)
) type=MyISAM;

-- Same as right1, except has no col4
create temporary table right2 (
    col1 int unsigned    not null auto_increment primary key,
    col2 bit             not null default 0,
    col3 int unsigned    not null
) type=MyISAM;

-- Same as right1, except col4 comes before col3
create temporary table right3 (
    col1 int unsigned    not null auto_increment primary key,
    col2 bit             not null default 0,
    col4 varchar(50),
    col3 int unsigned    not null
) type=MyISAM;

insert into left1 (col3) values (1), (1);
insert into left2 (col3) values (1);

insert into right1 (col3) select 1;
insert into right2 (col3) select 1;
insert into right3 (col3) select 1;

select * from left1;
select * from left2;
select * from right1;

-- Row 1 should succeed.  Row 2 should fail.  Both fail.
select left1.col1 as col1,
	left1.col2 + 0 as col2,
	left1.col3 + 0 as col3,
	right1.col1 as col1,
	right1.col2 + 0 as col2,
	right1.col3 as col3,
	right1.col4 as col4
from left1
    left outer join right1 on left1.col1 = right1.col3
        and right1.col2 <> 1
where left1.col2 <> 1
order by left1.col1;

-- There is only one row and it should succeed.  It does.
-- The only difference from above is one less row in the left-hand table.
-- Uses left2 instead of left1.
select *
from left2
    left outer join right1 on left2.col1 = right1.col3
        and right1.col2 <> 1
where left2.col2 <> 1;

-- Row 1 should and does succeed.  Row 2 should and does fail.
-- Part of the join clause is commented out.
select *
from left1
    left outer join right1 on left1.col1 = right1.col3
        -- and right1.col2 <> 1
where left1.col2 <> 1;

-- Row 1 should and does succeed.  Row 2 should and does fail.
-- Uses right2 instead of right1.
select *
from left1
    left outer join right2 on left1.col1 = right2.col3
        and right2.col2 <> 1
where left1.col2 <> 1;

-- Row 1 should and does succeed.  Row 2 should and does fail.
-- Uses right3 instead of right1.
select *
from left1
    left outer join right3 on left1.col1 = right3.col3
        and right3.col2 <> 1
where left1.col2 <> 1;