MySQL Stored Procedure Not Using IN variable

Hi,

I came across a trivial problem in MySQL today. I hope this helps someone (should apply to Oracle and MS SQL as well).

When defining a MySQL stored procedure and executing it, instead of the correct result set I got all rows of the table.

DELIMITER //
CREATE PROCEDURE testproc
(
  IN id INT
)
BEGIN
  SELECT *
  FROM `Test`
  WHERE `ID` = id;
  END //
DELIMITER ;

On closer inspection in phpMyAdmin and fooling around with the definition of the stored procedure, I discovered the issue. The column is called id, and of course id is always equal to id. It doesn't resolve to the IN variable id.

On further thought, this makes sense. Scope should be the smallest possible, and if one defines an IN or OUT or INOUT variable the same as a column name, there be dragons.

See the following for more information:
https://dev.mysql.com/doc/refman/5.7/en/local-variable-scope.html (local variables)
https://dev.mysql.com/doc/refman/5.1/en/set-statement.html (session variables)