Bug #29779 Wrong result of stored procedure

• Submitted: 12 Jul 2007 22:51
• Modified: 19 Jul 2007 20:50
• Reporter: Sergey Belov
• Status: Duplicate
• 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)
• CPU Architecture: Any
• 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?