Bug #4097 JOIN with subquery causes entire column to report NULL

• Submitted: 10 Jun 2004 21:39
• Modified: 16 Jun 2004 17:36
• Reporter: Tim Herzog
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.3
• Assigned to: Michael Widenius
• CPU Architecture: Any

[10 Jun 2004 21:39] Tim Herzog

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 |
-- +------+---------+------+-------+

[10 Jun 2004 22:06] Dean Ellis

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

[16 Jun 2004 17:36] Michael Widenius

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