Bug #33811 Call to stored procedure with SELECT * / RIGHT JOIN fails after the first time
Submitted: 10 Jan 2008 23:07 Modified: 11 Nov 2008 1:35
Reporter: Trent Lloyd Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0/5.1/6.0 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R2 (Low) / E3 (Medium)

[10 Jan 2008 23:07] Trent Lloyd
Description:
When using a stored procedure with a RIGHT JOIN, the second execution always fails with an unknown column error when using SELECT * but not with specific column names.

How to repeat:
Test Case:
DROP DATABASE IF EXISTS test_f507e4;
CREATE DATABASE test_f507e4;
USE test_f507e4;

CREATE TABLE t1 (id INT PRIMARY KEY auto_increment);
CREATE TABLE t2 (id INT PRIMARY KEY auto_increment, t1_id INT);
DELIMITER $$

DROP PROCEDURE IF EXISTS `test_sp`$$

CREATE PROCEDURE `test_sp`()

BEGIN
        SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.t1_id;
END$$

DELIMITER ;

CALL test_sp();
CALL test_sp();
[27 Feb 2008 14:36] Konstantin Osipov
DML reexecution bug, reassigning to the right lead.
[9 Apr 2008 12:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45124

ChangeSet@1.2599, 2008-04-09 15:44:27+03:00, gkodinov@magare.gmz +4 -0
  Bug #33811: Call to stored procedure with SELECT * / RIGHT JOIN 
    fails after the first time
  
  Two separate problems : 
    1. When flattening joins the linked list used for name resolution 
    (next_name_resolution_table) was not updated.
    Fixed by updating the pointers when extending the table list
  
    2. The items created by expanding a * (star) as a column reference
    were marked as fixed, but no cached table was assigned to them 
    (unlike what Item_field::fix_fields does).
    Fixed by assigning a cached table (so the re-preparation is done
    faster).
  
  Note that the fix for #2 hides the fix for #1 in most cases
  (except when a table reference cannot be cached).
[17 Oct 2008 14:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56476

2707 Georgi Kodinov	2008-10-17
      Bug #33811: Call to stored procedure with SELECT * / RIGHT JOIN 
      fails after the first time
        
      Two separate problems : 
        1. When flattening joins the linked list used for name resolution 
        (next_name_resolution_table) was not updated.
        Fixed by updating the pointers when extending the table list
        
        2. The items created by expanding a * (star) as a column reference
        were marked as fixed, but no cached table was assigned to them 
        (unlike what Item_field::fix_fields does).
        Fixed by assigning a cached table (so the re-preparation is done
        faster).
        
      Note that the fix for #2 hides the fix for #1 in most cases
      (except when a table reference cannot be cached).
[10 Nov 2008 10:52] Bugs System
Pushed into 6.0.8-alpha  (revid:kgeorge@mysql.com-20081017145506-9artn23fsniuth4s) (version source revid:kgeorge@mysql.com-20081103121200-1zo5uxc2iw151dog) (pib:5)
[10 Nov 2008 10:59] Bugs System
Pushed into 5.0.72  (revid:kgeorge@mysql.com-20081017145506-9artn23fsniuth4s) (version source revid:kgeorge@mysql.com-20081017145506-9artn23fsniuth4s) (pib:5)
[10 Nov 2008 11:35] Bugs System
Pushed into 5.1.30  (revid:kgeorge@mysql.com-20081017145506-9artn23fsniuth4s) (version source revid:kgeorge@mysql.com-20081103104058-pesvlkcvypwj82nf) (pib:5)
[11 Nov 2008 1:35] Paul Dubois
Noted in 5.0.72, 5.1.31, 6.0.8 changelogs.

For a stored procedure containing a SELECT * ... RIGHT JOIN query,
execution failed for the second call.
[11 Nov 2008 16:24] Paul Dubois
6.0.9 changelog, not 6.0.8.
[18 Nov 2008 21:47] Joerg Bruehe
A push on Nov 10 never made it into 5.0.72 - that must be 5.0.74.
[19 Nov 2008 0:00] Paul Dubois
Noted in 5.0.74 changelog, not 5.0.72.
[19 Jan 2009 11:31] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:08] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:14] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)