| Bug #4842 | Incorrect result in SP when parameter and column have the same name | ||
|---|---|---|---|
| Submitted: | 31 Jul 2004 18:39 | Modified: | 3 Aug 2004 11:44 |
| Reporter: | Georg Richter | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.1 | OS: | |
| Assigned to: | Per-Erik Martin | CPU Architecture: | Any |
[3 Aug 2004 11:44]
Per-Erik Martin
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: The problem is revealed if "SHOW WARNINGS" is used directly after CREATE PROCEDURE: mysql> show warnings; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1310 | Referring to uninitialized variable foo | +---------+------+-----------------------------------------+ 1 row in set (3 min 44.75 sec) The first "foo" in "SELECT foo ..." is resolved to be the variable, not the column. The solution is to use the table name explicitly: SELECT t1.foo INTO foo FROM t1 LIMIT 1 Note: This is not strictly according to the standard, where a conflicting name is defaulted to be the column name, but this would have other complication instead, so for the time being, this is the design. It's probably best to not rely on how conflicting names are parsed, and instead use explicit qualification. The issue about the warning count: This is possibly a bug, but probably not specific to stored procedures. It shows the total warning count, eventhough warnings has been reset during CALL.
Description: Not sure if it's a bug or a limitation: If the name of output variable is identical to the name of a column, it will contain NULL. First CALL p1(@a) returns a warning, but SHOW WARNINGS command doesn't show anything. How to repeat: [18:32] root@test> \d ! 5.0.1-alpha-debug-log [18:33] root@test> CREATE TABLE t1 (foo int)! Query OK, 0 rows affected (0.07 sec) [18:33] root@test> INSERT INTO t1 VALUES (1),(2),(3)! Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 5.0.1-alpha-debug-log [18:33] root@test> CREATE PROCEDURE p1 (OUT foo INT) -> BEGIN -> SELECT foo INTO foo FROM t1 LIMIT 1; -> END! Query OK, 0 rows affected, 1 warning (0.00 sec) 5.0.1-alpha-debug-log [18:34] root@test> call p1(@a)! Query OK, 0 rows affected, 1 warning (0.00 sec) 5.0.1-alpha-debug-log [18:34] root@test> SHOW WARNINGS! Empty set (0.00 sec) 5.0.1-alpha-debug-log [18:34] root@test> call p1(@a)! Query OK, 0 rows affected (0.00 sec) 5.0.1-alpha-debug-log [18:34] root@test> SELECT @a! +------+ | @a | +------+ | NULL | +------+ 1 row in set (0.00 sec)