MySQL: @variable vs. variable. Whats the difference? (Part2)

Ok, building off of the last question I asked, How does Mysql handle the where statment in the following code:

DELIMITER ;//
DROP PROCEDURE IF EXISTS `test`;//
CREATE PROCEDURE `test`
(
  id INT
)
BEGIN
  SELECT *
    FROM some_table
   WHERE id = id;
END;//

What does MySQL do in this case? Does it treat the where clause as

some_table.id = id

or does it treat it like

some_table.id = some_table.id

Right now I am doing something like

WHERE id = @id

because I didn't know that there were session variables in MySQL and it didn't complain and I thought that it was an explicit way of saying "where this column equals this variable".

Some might say "duh.. of course it treats it as column = variable" but I could easily have said where "variable = column." So how does it handle this?

Solution

MySQL's variable naming schema is a bit weird, when having the first look into it. Generally MySQL differentiates between three types of variables:

system variables (global or session scoped): @@varname
user defined variables (they are session scoped): @varname
local variables in stored programs: varname

So naming conflicts, such as those you mentioned above, only arise within stored programs. Therefore you first should try to avoid these naming conflicts by assigning unambiguous parameter names, e.g. by prefxing the parameters with p such as pId. If MySQL encounters an ambiguity it will interpret the reference as the name of a variable (see here):

[...] Local variable names should not be the same as column names. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable. [...]

The wording currently somehow gives the impression that this behaviour could change in future versions.