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
• Assigned to: Michael Widenius

[10 Jun 2004 23: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 |
-- +------+---------+------+-------+

[11 Jun 2004 0:06] Dean Ellis

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

[16 Jun 2004 19: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