Bug #5967 Stored procedure declared variable used instead of column
Submitted: 8 Oct 2004 0:31 Modified: 4 Nov 2007 22:02
Reporter: Peter Gulutzan
Status: Verified
Category:Server: Parser Severity:S1 (Critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Konstantin Osipov Target Version:
Triage: Triaged: D2 (Serious)

[8 Oct 2004 0: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 13:34] Victoria Reznichenko 
Verified with 5.0.2-alpha-debug-log
[20 Apr 2005 12: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 16: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 16: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 18:59] Konstantin Osipov 
Bug#13771 has been marked as a duplicate of this bug.
[2 Dec 2005 20: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 22: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 22: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 12:31] Per-Erik Martin 
BUG#16888 is marked as a duplicate of this one.
[31 Jan 2006 14:58] Per-Erik Martin 
BUG#16052 is marked as a duplicate of this one.
[29 Jun 2006 20:16] Konstantin Osipov 
Bug#17878 has been marked as a duplicate of this bug.
[13 Jul 2006 16:29] Konstantin Osipov 
Bug#20834 has been marked as a duplicate of this bug.
[22 Sep 2006 18: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 19:43] Konstantin Osipov 
Bug#29779 was marked a duplicate of this bug.
[7 Aug 2007 20:51] Konstantin Osipov 
Bug #30163 Name confusion in Fetch Into in a Function defintion was marked a duplicate of
this bug.
[2 Jan 2008 20:33] Trudy Pelzer 
Workaround: use distinct names.
[5 Sep 2008 12:46] Konstantin Osipov 
Depends on WL#4179
[14 May 15:55] Konstantin Osipov 
Bug#28227 was marked a duplicate of this bug.
[17 Sep 11:56] Konstantin Osipov 
Bug#47086 was marked a duplicate of this bug.