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

[24 Apr 2008 9:55] Fabio Bologna
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..
[24 Apr 2008 10:00] Fabio Bologna
The VB project and the schema file

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

[24 Apr 2008 10:01] Tonci Grgin
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.
[24 Apr 2008 10:15] Fabio Bologna
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.
[24 Apr 2008 10:35] Fabio Bologna
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.
[24 Apr 2008 10:39] Fabio Bologna
New project and new schema

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

[24 Apr 2008 13:46] Bugs System
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
[24 Apr 2008 14:08] Reggie Burnett
Fixed in 5.1.6 and 5.2.2+
[24 Apr 2008 15:08] Fabio Bologna
Thanks for your very fast answer.
[16 May 2008 13:11] MC Brown
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.
[8 Jul 2008 15:06] Tony Bedford
Closing as can confirm that entry has been added to the changelog as specified.
[8 Jul 2008 15:13] Tony Bedford
Closing as can confirm that entry has been added to the changelog as specified.