MySQL Bugs: #4097: JOIN with subquery causes entire column to report NULL

Bug #4097	JOIN with subquery causes entire column to report NULL
Submitted:	10 Jun 2004 23:39	Modified:	16 Jun 2004 19:36
Reporter:	Tim Herzog
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	4.1.3	OS:
Assigned to:	Bugs System	Target Version:

Description:
When joining two tables, one the result of a subquery, in a SELECT, an entire column will
report NULL if the column value is NULL in the last row of the subquery.  So if my table
is:

+------+---------+
| id   | name    |
+------+---------+
|    1 | Tim     |
|    2 | Rebecca |
|    3 | NULL    |
+------+---------+

and I make a subquery based on this table and join it to another table, the entire NAME
column will report NULL.

How to repeat:

DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int(11) default NULL,
  `name` varchar(10) default NULL
);

INSERT INTO `a` VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);

DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id` int(11) default NULL,
  `pet` varchar(10) default NULL
);

INSERT INTO `b` VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');

-- Do a join query.  a.name will appear all null b/c last value in a is NULL
SELECT a.*, b.* FROM (SELECT * FROM a) AS a JOIN b on a.id=b.id;

-- Produces:
-- +------+------+------+-------+
-- | id   | name | id   | pet   |
-- +------+------+------+-------+
-- |    1 | NULL |    1 | Fido  |
-- |    2 | NULL |    2 | Spot  |
-- |    3 | NULL |    3 | Felix |
-- +------+------+------+-------+

-- Fix by setting last name field to empty string
UPDATE a SET name='' WHERE id=3;

-- Test again:  this should work
SELECT a.*, b.* FROM (SELECT * FROM a) AS a JOIN b on a.id=b.id;

-- Produces:
-- +------+---------+------+-------+
-- | id   | name    | id   | pet   |
-- +------+---------+------+-------+
-- |    1 | Tim     |    1 | Fido  |
-- |    2 | Rebecca |    2 | Spot  |
-- |    3 |         |    3 | Felix |
-- +------+---------+------+-------+

Verified against 4.1.3/Linux with your supplied test case.  Thank you.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 4.1.3