Bug #19569 Left/Right joins not returning NULL for all fields of non-existent rows

• Submitted: 5 May 2006 19:54
• Modified: 26 Aug 2006 9:10
• Reporter: Erica Moss
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.19
• OS: Windows (win32)
• CPU Architecture: Any

[5 May 2006 19:54] Erica Moss

Description:
When a right or left join is done, the non-matching rows from the secondary table (left in the case of a right join, and right in the case of a left join) should return NULL for all fields.  They are only returning NULL for the column that was specified in the ON clause.  The others are blank.

If specifically asked to match for NULL in the other (blank) columns it will do so, so NULL is what is there.  It just isn't being returned for a these SELECTs.

How to repeat:
TESTCODE:

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;

CREATE TABLE t1 (v1 int, v2 char(10));
CREATE TABLE t2 (v1 int, v2 char(10), v3 char(1));

INSERT INTO t1 values (1, 'row1'), (2, 'row2'), (3, 'row3');
INSERT INTO t2 values (4, 'row1', 'x'), (2, 'row2', 'y'), (3, 'row3', 'z');

select * from t1 left join t2 on t1.v1=t2.v1;
select * from t1 left join t2 on t1.v1=t2.v1 where t2.v3 IS NULL;

******OUTPUT*********

+------+------+------+------+------+
| v1   | v2   | v1   | v2   | v3   |
+------+------+------+------+------+
| 1    | row1 | NULL |      |      |
| 2    | row2 | 2    | row2 | y    |
| 3    | row3 | 3    | row3 | z    |
+------+------+------+------+------+
3 rows in set (0.00 sec)

+------+------+------+------+------+
| v1   | v2   | v1   | v2   | v3   |
+------+------+------+------+------+
| 1    | row1 | NULL |      |      |
+------+------+------+------+------+
1 row in set (0.00 sec)

Suggested fix:
Should return NULL for all columns of unmatched row.  I believe this is the way it behaved in past versions.

[5 May 2006 20:39] MySQL Verification Team

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:

c:\mysql\bin>mysql -uroot db34
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> CREATE TABLE t1 (v1 int, v2 char(10));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t2 (v1 int, v2 char(10), v3 char(1));
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO t1 values (1, 'row1'), (2, 'row2'), (3, 'row3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 values (4, 'row1', 'x'), (2, 'row2', 'y'), (3, 'row3', 'z');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from t1 left join t2 on t1.v1=t2.v1;
+------+------+------+------+------+
| v1   | v2   | v1   | v2   | v3   |
+------+------+------+------+------+
|    1 | row1 | NULL | NULL | NULL |
|    2 | row2 |    2 | row2 | y    |
|    3 | row3 |    3 | row3 | z    |
+------+------+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on t1.v1=t2.v1 where t2.v3 IS NULL;
+------+------+------+------+------+
| v1   | v2   | v1   | v2   | v3   |
+------+------+------+------+------+
|    1 | row1 | NULL | NULL | NULL |
+------+------+------+------+------+
1 row in set (0.00 sec)

mysql>