Bug #47541 MIN/MAX does not work when applined on a subquery with two left outer join

• Submitted: 23 Sep 2009 8:19
• Modified: 24 Dec 2012 8:44
• Reporter: Bernt Marius Johnsen
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.0, 5.1, next
• OS: Any
• CPU Architecture: Any

[23 Sep 2009 8:19] Bernt Marius Johnsen

Description:
MIN/MAX does not work when applined on a subquery with two left outer joins. 

The first SELECT should have the result 'a', not NULL:

mysql> SELECT MIN(t1.varchar_key) FROM (CC AS t1 LEFT JOIN (A AS t2 LEFT JOIN C AS t3 ON (t3.int_key = t2.pk)) ON (t3.int_key = t2.int_key));
+---------------------+
| MIN(t1.varchar_key) |
+---------------------+
| NULL                |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT t1.varchar_key FROM (CC AS t1 LEFT JOIN (A AS t2 LEFT JOIN C AS t3 ON (t3.int_key = t2.pk)) ON (t3.int_key = t2.int_key));
+-------------+
| varchar_key |
+-------------+
| a           |
| b           |
| c           |
| d           |
| e           |
| e           |
| e           |
| f           |
| h           |
| j           |
| j           |
| m           |
| m           |
| r           |
| s           |
| v           |
| v           |
| x           |
| y           |
| z           |
+-------------+
20 rows in set (0.00 sec)

How to repeat:
Run the attached mysqldump file bugdump.sql, and then 

SELECT MIN(t1.varchar_key) FROM (CC AS t1 LEFT JOIN (A AS t2 LEFT JOIN C AS t3 ON (t3.int_key = t2.pk)) ON (t3.int_key = t2.int_key));

[23 Sep 2009 8:20] Bernt Marius Johnsen

mysqldump file to create the schema

Attachment: bugdump.sql (text/x-sql), 8.96 KiB.

[23 Sep 2009 9:06] Sveta Smirnova

Thank you for the report.

Verified as described.

[23 Sep 2009 9:06] Sveta Smirnova

test case for the testsuite

Attachment: bug47541.test (application/octet-stream, text), 6.87 KiB.

[24 Dec 2012 8:44] Erlend Dahl

This is a duplicate of bug#52051, which was fixed in 5.1.48