MySQL Bugs: #33390: Left join with same table using alias causes old cache result or NULL values

Bug #33390	Left join with same table using alias causes old cache result or NULL values
Submitted:	20 Dec 2007 5:27	Modified:	20 Dec 2007 9:29
Reporter:	Vijayakanth P	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.24	OS:	Windows (windows XP)
Assigned to:	Hartmut Holzgraefe	CPU Architecture:	Any
Tags:	alias same table, left join, old cache

Description:
When I use LEFT JOIN with same table using alias in stored procedure, i am getting old cache result or missing left joined table values. For the left joined table values are coming NULL.

How to repeat:
Table schema
-------------
category  CREATE TABLE `category`
(                        
            `CategoryID` int(11) NOT NULL auto_increment,  
            `CategoryName` varchar(50) NOT NULL,           
            `ParentCategoryID` int(11) default NULL,       
            PRIMARY KEY  (`CategoryID`)                    
) ENGINE=InnoDB DEFAULT CHARSET=latin1         

Stored procedure
----------------
CREATE PROCEDURE `GetCategoryDetails` (p_CategoryID int)
BEGIN
SELECT C.CategoryID, C.CategoryName, PC.CategoryID, PC.CategoryName as ParentCategoryName
FROM Category C LEFT JOIN Category PC
ON C.ParentCategoryID=PC.CategoryID
WHERE C.CategoryID=p_CategoryID;
END

Fill Category Table Values as below
+------------+--------------+------------------+
| CategoryID | CategoryName | ParentCategoryID |
+------------+--------------+------------------+
|          1 | Sugar        |             NULL |
|          2 | Diary        |             NULL |
|          3 | Milk         |                2 |
+------------+--------------+------------------+

Do the following stored procedure calling to repeat error

Step 1)
mysql> call GetCategoryDetails(3);
+------------+--------------+------------+--------------------+
| CategoryID | CategoryName | CategoryID | ParentCategoryName |
+------------+--------------+------------+--------------------+
|          3 | Milk         |          2 | Diary              |
+------------+--------------+------------+--------------------+

Step 2)
mysql> call GetCategoryDetails(2);
+------------+--------------+------------+--------------------+
| CategoryID | CategoryName | CategoryID | ParentCategoryName |
+------------+--------------+------------+--------------------+
|          2 | Diary        |       NULL | NULL               |
+------------+--------------+------------+--------------------+

Step 3)
mysql> call GetCategoryDetails(3);
+------------+--------------+------------+--------------------+
| CategoryID | CategoryName | CategoryID | ParentCategoryName |
+------------+--------------+------------+--------------------+
|          3 | Milk         |       NULL | NULL               |
+------------+--------------+------------+--------------------+

Compare the output values from step 1) and 3). LEFT JOINED tables values are NULL in step 3 even with same categoryID value.

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

I was not able to reproduce this with more recent versions like 5.0.41 and 5.0.51

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.

mysqltst test case

Attachment: bug33390.tgz (application/x-gtar, text), 977 bytes.