Bug #8761 Using LEFT JOIN creates a resultset with NULL values when there is no ID common

• Submitted: 24 Feb 2005 0:25
• Modified: 25 Feb 2005 19:50
• Reporter: Disha
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2
• OS: Windows (Windows 2003)
• CPU Architecture: Any

[24 Feb 2005 0:25] Disha

Description:
When there is NO common ID LEFT JOIN creates a result set with NULL values. Instead empty result set should be returned.

How to repeat:
Test Setup  
1. Create database say 'Test'
2. Create table t1 and t2
3. Populate t1 
+---------+----------+----------+----------+
| fname  | lname   | income  | children |
+---------+----------+----------+----------+
| A1       | M1        |  10000  |       10   |
| M2      | K2         |  20000  |       10   |
| F3       | G3         |  30000  |        0   |
| C4       | G4         |  30000  |        2   |
| V5       | P5         |  30000  |     NULL |
+---------+----------+--------+------------+
5 rows in set (0.00 sec)

4. Populate t2
+-------+---------+  
| fname | count |
+-------+---------+
| Ash   |    23    |
| Ash   |    35    |
+-------+---------+
2 rows in set (0.00 sec)
	

Repro Steps:

mysql> select asht2.fname,asht2.count from asht LEFT JOIN asht2 ON asht.fname=asht2.fname//

Actual Results:
+-------+-------+
| fname | count |
+-------+-------+
| NULL  |  NULL |
| NULL  |  NULL |
| NULL  |  NULL |
| NULL  |  NULL |
| NULL  |  NULL |
+-------+-------+
5 rows in set (0.00 sec)

Expected Results
Empty result set.

[24 Feb 2005 5:41] MySQL Verification Team

Couldf you please send a test case with a dump of the tables
and the query using the same tables names.

Thanks.

[25 Feb 2005 19:50] Trudy Pelzer

This is not a bug. First, tables asht1 and asht2 do not exist. But if
one changes the query to:

mysql> select t1.fname, t2.fname, t2.count from t1 LEFT JOIN t2 ON t1.fname=t2.fname;
+-------+-------+-------+
| fname | fname  | count |
+-------+-------+-------+
|      A1 | NULL  |  NULL |
|      M2 | NULL  |  NULL |
|      F3 | NULL  |  NULL |
|      C4 | NULL  |  NULL |
|      V5| NULL  |  NULL |
+-------+-------+-------+
5 rows in set (0.00 sec)

One gets the correct result: every value of fname in table t1,
with NULLs for the non-matching values in table t2. Adding the
extra column makes it clear the result is correct, but even without
the extra column, MySQL is returning the correct result.