Bug #29779 Wrong result of stored procedure
Submitted: 12 Jul 2007 22:51 Modified: 19 Jul 2007 20:50
Reporter: Sergey Belov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.41-community-nt-log, 5.0, 5.1 BK OS:Any (Linux, Win XP pro 64-bit edition)
Assigned to:
Tags: IN variable, stored procedure

[12 Jul 2007 22:51] Sergey Belov
Description:
Wrong behavior of stored procedure while using IN variable and table field havind the same name.

How to repeat:
PROCEDURE test.procedure1(TEST INT UNSIGNED)
BEGIN
  DECLARE VAR INT UNSIGNED;

  DROP TABLE IF EXISTS `BlahBlah`;
  CREATE TEMPORARY TABLE  `BlahBlah` SELECT 1 as `TEST`;

  SELECT EXISTS(SELECT 7 FROM `BlahBlah` WHERE `TEST`=1);
END

MySQL console returns:
mysql> call test.procedure1(2);
+-------------------------------------------------+
| EXISTS(SELECT 7 FROM `BlahBlah` WHERE `TEST`=1) |
+-------------------------------------------------+
|                                               0 |
+-------------------------------------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

I think result should be 1, but it is zero. It is wrong behavior because `TEST` field is quoted and IN TEST variable is not used in query

Suggested fix:
PROCEDURE test.procedure1(TEST INT UNSIGNED)
BEGIN
  DECLARE VAR INT UNSIGNED;

  DROP TABLE IF EXISTS `BlahBlah`;
  CREATE TEMPORARY TABLE  `BlahBlah` SELECT 1 as `_TEST`;

  SELECT EXISTS(SELECT 7 FROM `BlahBlah` WHERE `_TEST`=1);
END

MySQL console returns:
mysql> call test.procedure1(2);
+--------------------------------------------------+
| EXISTS(SELECT 7 FROM `BlahBlah` WHERE `_TEST`=1) |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Renaming field will solve the trouble.
[12 Jul 2007 22:59] Sergey Belov
Line 'DECLARE VAR INT UNSIGNED;' in stored procedure example is not needed.
[13 Jul 2007 5:19] Sveta Smirnova
Thank you for the report.

Verified as described.

Workaround:

SELECT EXISTS(SELECT 7 FROM `BlahBlah` WHERE `BlahBlah`.`TEST`=1);
[16 Jul 2007 12:40] Konstantin Osipov
Thank you for your bug report.
This is a duplicate of Bug#5967 "Stored procedure declared variable used instead of column"
[19 Jul 2007 20:50] Sergey Belov
Does this bug still fixing or it is suspend? Maybe document it as a feature?