Bug #33770 Failure to execute the Stored Procedures with the parameters passed by name but

Description:
I have tried preparing a callable statement in JAVA and tried passing the variables non-positionally. The procedure is not considering the passed IN parameter values as NULL.

con = getConnection();
sp  = con.prepareCall("{ call get_movies( @title = ?, @genere = ? )}");
sp.setString(1,'t');
sp.setString(2,'a');
ResultSet res = sp.executeQuery();

The data base had the following records.
+------+----------------------------------+------------+
| id   | title                            | genere     |
+------+----------------------------------+------------+
|    1 | Couching Tiger and Hidden Dragon | Action     |
|    2 | Titanic                          | Romantic   |
|    3 | The Huin                         | Historical |
+------+----------------------------------+------------+

And the stored procedure is returning the proper results if the statement is 
   con.prepareCall("{ call get_movies( ?, ? )}");
So I think this is a bug in this version of MYSQL that its not allowing the users to pass the parameter values by name.

How to repeat:
delimiter //
DROP PROCEDURE IF EXISTS dynamic_sql_builder//
CREATE PROCEDURE dynamic_sql_builder(IN query_string VARCHAR(1000), INOUT variable text(100000000))
BEGIN
   SET variable = CONCAT(variable,query_string);
END;

DELIMITER $$

DROP PROCEDURE IF EXISTS `mein`.`get_movies` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_movies`(IN title VARCHAR(100),IN genere VARCHAR(100))
    READS SQL DATA
BEGIN

  SET @qry = '';

  call dynamic_sql_builder ( ' SELECT '          ,@qry );
  call dynamic_sql_builder ( '         title'    ,@qry );
  call dynamic_sql_builder ( '         ,genere'  ,@qry );
  call dynamic_sql_builder ( '   FROM'           ,@qry );
  call dynamic_sql_builder ( '         movie m'  ,@qry );
  call dynamic_sql_builder ( '  WHERE'           ,@qry );
  call dynamic_sql_builder ( '         1 = 1 '   ,@qry );
  IF( title IS NOT Null AND RTRIM(LTRIM(title)) <> '')
  THEN
      call dynamic_sql_builder ( '       AND lower(title) LIKE \'' ,@qry);
      call dynamic_sql_builder (                                   LOWER(LTRIM(RTRIM(title))),@qry);
      call dynamic_sql_builder (                                                             '%\'',@qry);
  END IF;
  IF( genere IS NOT Null AND RTRIM(LTRIM(genere)) <> '')
  THEN
      call dynamic_sql_builder ( '       AND lower(genere) = \'',@qry);
      call dynamic_sql_builder (                                 LOWER(LTRIM(RTRIM(genere))),@qry);
      call dynamic_sql_builder (                                                            '\'' ,@qry );
  END IF;

 PREPARE stmt FROM @qry;
 EXECUTE stmt;

END $$

DELIMITER ;

The above are my stored procedures

When I have executed the stored procedure like the below.

call get_movies(@title = 't', @genere = 'a');

Its returing me all the rows in the database;

However, when I execute call get_movies( 't', 'a');
Its giving me proper results.

Suggested fix:
Allow the parameters to be passed by name but not by the position;

This effects all the stored procedure users.

That's not the syntax for passing values by name, at least not for JDBC. It *should* look like this:

sp  = con.prepareCall("{ call get_movies( ?, ? )}");
sp.setString("title",'t');
sp.setString("genere",'a');
ResultSet res = sp.executeQuery();

(Even not using Java, I don't see what the benefit of the way you posted would be, since you're putting the names in their ordinal positions anyway!?)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

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/85797

2887 Guilhem Bichot	2009-10-05
      Backport of the fix for BUG#33770 "Full table scan instead selected partitions for query more than 10 partitions"
      from 6.0, made in sergefp@mysql.com-20090205190644-q8632sniogedhtsu