MySQL Bugs: #36313: BIT result is lost in the left outer join

Bug #36313	BIT result is lost in the left outer join
Submitted:	24 Apr 2008 9:55	Modified:	8 Jul 2008 15:06
Reporter:	Fabio Bologna	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.0,5.1,5.2	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	bit, left, NET, null

Description:
I have two table, tbl_Main and tbl_Child.
The tbl_Main is LEFT JOINED with tbl_Child that contains one INTEGER value and one BIT value.
THE BUG
If I execute that SQL

  SELECT m.Descr, c.Value, c.Enabled
  FROM tbl_main m
  LEFT OUTER JOIN tbl_child c ON m.Id=c.MainId
  ORDER BY m.Descr

the QueryBrowser return this

  'AAA', NULL, NULL
  'BBB', 12345, b'1'

that is the espected return.
The .NET connector returns

  'AAA', NULL, NULL
  'BBB', 12345, NULL

that is wrong.
THE WORKAROUND
If I execute that SQL

  SELECT m.Descr, c.Value, c.Enabled
  FROM tbl_main m
  LEFT OUTER JOIN tbl_child c ON m.Id=c.MainId
  ORDER BY m.Enabled DESC, m.Descr

the .NET connector returns

  'BBB', 12345, TRUE
  'AAA', NULL, NULL

that is ok.

How to repeat:
Use this database tables and values to check:

DROP TABLE IF EXISTS `tbl_main`;
CREATE TABLE `tbl_main` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Descr` varchar(45) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `tbl_main` (`Id`,`Descr`) VALUES 
 (1,'AAA'),
 (2,'BBB');
DROP TABLE IF EXISTS `tbl_child`;
CREATE TABLE `tbl_child` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `MainId` int(10) unsigned NOT NULL,
  `Value` int(10) unsigned NOT NULL,
  `Enabled` bit(1) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `tbl_child` (`Id`,`MainId`,`Value`,`Enabled`) VALUES 
 (1,2,12345,0x01);

Suggested fix:
Probably you use a variable that must be reset..

The VB project and the schema file

Attachment: MySqlBitLeftJoinBug.zip (application/zip, text), 131.81 KiB.

Hi Fabio and thanks for your report.

Please do attach test case *and* try all of your queries in command line client like this:
mysql -uUser -p -T database
Please paste output here so I can check what metadata server returned.

mysql>   SELECT m.Descr, c.Value, c.Enabled
    ->   FROM tbl_main m
    ->   LEFT OUTER JOIN tbl_child c ON m.Id=c.MainId
    ->   ORDER BY m.Descr
    ->
    -> ;
+-------+-------+---------+
| Descr | Value | Enabled |
+-------+-------+---------+
| AAA   |  NULL | NULL    |
| BBB   | 12345 | ☺       |
+-------+-------+---------+
2 rows in set (0.02 sec)

mysql>   SELECT m.Descr, c.Value, c.Enabled
    ->   FROM tbl_main m
    ->   LEFT OUTER JOIN tbl_child c ON m.Id=c.MainId
    ->   ORDER BY c.Enabled DESC, m.Descr
    -> ;
+-------+-------+---------+
| Descr | Value | Enabled |
+-------+-------+---------+
| BBB   | 12345 | ☺       |
| AAA   |  NULL | NULL    |
+-------+-------+---------+
2 rows in set (0.00 sec)

How you can see, the output of the query is ok.

The connector seem to be able to returns correctly only the second query.

There an error in my bug summission.
The problem is not related to left join.
It seem that the data reader don't is capable to return a correct bit value after find a null value.
Try with this:

DROP TABLE IF EXISTS `test`.`tbl_bug_bool`;
CREATE TABLE  `test`.`tbl_bug_bool` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Active` bit(1) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

And insert two value

INSERT INTO tbl_bug_bool(Active) VALUES(1)
INSERT INTO tbl_bug_bool(Active) VALUES(NULL)
INSERT INTO tbl_bug_bool(Active) VALUES(1)

The console returns:

mysql> SELECT * FROM test.tbl_bug_bool t;
+----+--------+
| Id | Active |
+----+--------+
|  1 | ☺      |
|  2 | NULL   |
|  3 | ☺      |
+----+--------+
3 rows in set (0.02 sec)

Use this SQL in your application:

SELECT * FROM test.tbl_bug_bool t

and you'll see that don't work: the true value is present only in the first row and not in the third.
This query demostrates that the value is reachable.

SELECT * FROM test.tbl_bug_bool t ORDER BY t.Active DESC

You can reproduce it?
I'll attach a new project.

New project and new schema

Attachment: MySqlBitLeftJoinBug.zip (application/zip, text), 148.61 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45951

Fixed in 5.1.6 and 5.2.2+

Thanks for your very fast answer.

A note has been added to 5.1.6 and 5.2.2 changelogs: 

An incorrect value for a bit field would returned in a multi-row query if a preceding value for the field returned NULL.

Closing as can confirm that entry has been added to the changelog as specified.

Closing as can confirm that entry has been added to the changelog as specified.