Bug #5967 Stored procedure declared variable used instead of column
Submitted: 7 Oct 2004 22:31 Modified: 4 Nov 2007 21:02
Reporter: Peter Gulutzan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S1 (Critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: CPU Architecture:Any

[7 Oct 2004 22:31] Peter Gulutzan 
Description:
Suppose I have a column named X and I have a variable named X (the same name). 
Suppose I refer to X in an SQL statement, e.g. "SELECT X ...". MySQL thinks I am 
referring to the variable. But DB2 would think I am referring to the column. From the DB2 
manual: 
 
"SQL variable names should not be the same as column names. If an SQL statement 
contains an identifier with the same name as an SQL variable and a column reference, 
DB2 interprets the identifier as a column." 
 
 
 

How to repeat:
mysql> create table t (v int);// 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> insert into t values (5);// 
Query OK, 1 row affected (0.00 sec) 
 
mysql> create procedure p () begin declare v int default 6; declare v2 int; declare x cursor 
for select v from t; open x; fetch x into v2; select v2; close x; end;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p()// 
+----+ 
| v2 | 
+----+ 
|  6 | 
+----+ 
1 row in set (0.00 sec)
[8 Oct 2004 11:34] MySQL Verification Team 
Verified with 5.0.2-alpha-debug-log
[20 Apr 2005 10:24] Per-Erik Martin 
This is a design issue. The problem is that the requested behaviour is
    context dependent; identifiers should be column names when possible,
    local variables otherwise. This might not be easy to do in the parser.
    (The other way around, which do now, is easy though, we can always see
     if a local variable is declared.)
    It also requires all used tables to exist before the SP is created
    (or the column/variable issue must be resolved at run time).
[1 Jun 2005 14:08] 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/internals/25457
[1 Jun 2005 14:32] Per-Erik Martin 
No, a patch has not been submitted. This was the result of a confused automatic response to another bug fix where this bug was only mentioned.
[17 Nov 2005 17:59] Konstantin Osipov 
Bug#13771 has been marked as a duplicate of this bug.
[2 Dec 2005 19:43] 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/internals/32978
[7 Dec 2005 21:46] 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/31
[8 Dec 2005 21:58] 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/50
[30 Jan 2006 11:31] Per-Erik Martin 
BUG#16888 is marked as a duplicate of this one.
[31 Jan 2006 13:58] Per-Erik Martin 
BUG#16052 is marked as a duplicate of this one.
[29 Jun 2006 18:16] Konstantin Osipov 
Bug#17878 has been marked as a duplicate of this bug.
[13 Jul 2006 14:29] Konstantin Osipov 
Bug#20834 has been marked as a duplicate of this bug.
[22 Sep 2006 16:48] Konstantin Osipov 
A log of IRC chat with Peter Gulutzan, 2006-09-22:

20:41 < kostja> peter: Imagine we have a case of name shadowing: there is an SP
                variable and a table field with the same name.
                But we don't have privileges to see the field.
                Shall we fall back to the SP variable in this case?
                Or return an error?
20:44 < peter> kostja: i believe "return an error" is correct. the fact that 
               you don't have the privilege to select/update/insert the column 
               doesn't mean the column isn't found. and it sounds better that 
               two users executing the same statement are accessing the same 
               object.
20:45 < kostja> thanks, I will amend the bug report now
[16 Jul 2007 17:43] Konstantin Osipov 
Bug#29779 was marked a duplicate of this bug.
[7 Aug 2007 18:51] Konstantin Osipov 
Bug #30163 Name confusion in Fetch Into in a Function defintion was marked a duplicate of this bug.
[2 Jan 2008 19:33] Trudy Pelzer 
Workaround: use distinct names.
[5 Sep 2008 10:46] Konstantin Osipov 
Depends on WL#4179
[14 May 2009 13:55] Konstantin Osipov 
Bug#28227 was marked a duplicate of this bug.
[17 Sep 2009 9:56] Konstantin Osipov 
Bug#47086 was marked a duplicate of this bug.
[5 May 2010 17:50] Sveta Smirnova 
Bug #53441 was marked as duplicate of this one.
[13 Nov 2012 19:38] Sveta Smirnova 
Bug #67425 was marked as duplicate of this one.
[11 Nov 2017 18:40] Federico Razzoli 
Identical in 8.0.3:

mysql> create table t (v int);
Query OK, 0 rows affected (0.27 sec)

mysql> insert into t values (5);
Query OK, 1 row affected (0.05 sec)

mysql> delimiter //
mysql> create procedure p () begin declare v int default 6; declare v2 int; declare x cursor 
    -> for select v from t; open x; fetch x into v2; select v2; close x; end;//
Query OK, 0 rows affected (0.05 sec)

mysql> call p()//
+------+
| v2   |
+------+
|    6 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)