Bug #95522 unexpected result when using `uid` column name and select in stored procedure.
Submitted: 24 May 2019 9:32 Modified: 24 May 2019 11:39
Reporter: parky hyun sik Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.7.26 OS:Ubuntu (18.0-4.1)
Assigned to: CPU Architecture:x86 (Intel(R) Core(TM) i5-6200U CPU @ 2.30GHz)
Tags: stored procedure, uid column, unexpected result

[24 May 2019 9:32] parky hyun sik
Description:
I was creating a table with `uid` column name with unique index constrains 
and simple stored procedure (only select by uid).

When I called this simple stored procedure, the result was not valid. I was expecting valid uid and only one row, but the output was many rows. (Maybe it was same to select without 'where' syntax.)

How to repeat:
DROP TABLE IF EXISTS `TB_TEST`;
CREATE TABLE IF NOT EXISTS `TB_TEST` (
  `no` INT NOT NULL AUTO_INCREMENT,
  `uid` VARCHAR(64) NOT NULL,
  `type` INT NOT NULL,
  `create` TIMESTAMP NOT NULL DEFAULT NOW(),
  `update` TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (`no`),
  UNIQUE INDEX `UUID_UNIQUE_IDX` (`uid` ASC)) ENGINE = InnoDB;

DROP PROCEDURE IF EXISTS SP_REGISTER;
DELIMITER //
CREATE PROCEDURE SP_REGISTER (IN UID VARCHAR(64), IN TYPE INT)
BEGIN
    INSERT INTO `TB_TEST` (`uid`, `type`, `create`, `update`) VALUES (UID, TYPE, NOW(), NOW());
    SELECT * FROM `TB_TEST` WHERE `no` = LAST_INSERT_ID();
END //
DELIMITER ;

DROP PROCEDURE IF EXISTS `SP_LOGIN`;
DELIMITER //
CREATE PROCEDURE `SP_LOGIN` (IN UID VARCHAR(64))
BEGIN
    SELECT * FROM `TB_TEST` WHERE `uid` = UID;
END //
DELIMITER ;

CALL SP_REGISTER('hello1', 1);
CALL SP_REGISTER('hello2', 1);
CALL SP_REGISTER('hello3', 1);
CALL SP_REGISTER('hello4', 1);
CALL SP_LOGIN('hello1');
[24 May 2019 11:08] Umesh Shastry
Hello parky hyun sik,

Thank you for the report and test case.
Imho name conflicts within stored routines is a known and documented behavior i.e A routine parameter takes precedence over a table column. .   please see https://dev.mysql.com/doc/mysql-reslimits-excerpt/8.0/en/stored-program-restrictions.html

Workaround is to prefix table name with the column or prefix the parameter with p_ or l_ so that there will not be any conflict.

DROP TABLE IF EXISTS `TB_TEST`;
CREATE TABLE IF NOT EXISTS `TB_TEST` (
  `no` INT NOT NULL AUTO_INCREMENT,
  `uid` VARCHAR(64) NOT NULL,
  `type` INT NOT NULL,
  `create` TIMESTAMP NOT NULL DEFAULT NOW(),
  `update` TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (`no`),
  UNIQUE INDEX `UUID_UNIQUE_IDX` (`uid` ASC)) ENGINE = InnoDB;

DROP PROCEDURE IF EXISTS SP_REGISTER;
DELIMITER //
CREATE PROCEDURE SP_REGISTER (IN UID VARCHAR(64), IN TYPE INT)
BEGIN
    INSERT INTO `TB_TEST` (`uid`, `type`, `create`, `update`) VALUES (UID, TYPE, NOW(), NOW());
    SELECT * FROM `TB_TEST` WHERE `no` = LAST_INSERT_ID();
END //
DELIMITER ;

DROP PROCEDURE IF EXISTS `SP_LOGIN`;
DELIMITER //
CREATE PROCEDURE `SP_LOGIN` (IN UID VARCHAR(64))
BEGIN
DECLARE _output TEXT DEFAULT '';
    SET _output = CONCAT("SELECT * FROM `TB_TEST` WHERE `uid`=",`UID`);
	SELECT _output;
    SELECT * FROM `TB_TEST` WHERE `TB_TEST`.`uid` = UID;
END //
DELIMITER ;

CALL SP_REGISTER('hello1', 1);
CALL SP_REGISTER('hello2', 1);
CALL SP_REGISTER('hello3', 1);
CALL SP_REGISTER('hello4', 1);
CALL SP_LOGIN('hello1');
-
mysql> DELIMITER ;
mysql>
mysql> CALL SP_REGISTER('hello1', 1);
+----+--------+------+---------------------+---------------------+
| no | uid    | type | create              | update              |
+----+--------+------+---------------------+---------------------+
|  1 | hello1 |    1 | 2019-05-24 12:52:28 | 2019-05-24 12:52:28 |
+----+--------+------+---------------------+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL SP_REGISTER('hello2', 1);
+----+--------+------+---------------------+---------------------+
| no | uid    | type | create              | update              |
+----+--------+------+---------------------+---------------------+
|  2 | hello2 |    1 | 2019-05-24 12:52:28 | 2019-05-24 12:52:28 |
+----+--------+------+---------------------+---------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL SP_REGISTER('hello3', 1);
+----+--------+------+---------------------+---------------------+
| no | uid    | type | create              | update              |
+----+--------+------+---------------------+---------------------+
|  3 | hello3 |    1 | 2019-05-24 12:52:28 | 2019-05-24 12:52:28 |
+----+--------+------+---------------------+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL SP_REGISTER('hello4', 1);
+----+--------+------+---------------------+---------------------+
| no | uid    | type | create              | update              |
+----+--------+------+---------------------+---------------------+
|  4 | hello4 |    1 | 2019-05-24 12:52:28 | 2019-05-24 12:52:28 |
+----+--------+------+---------------------+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL SP_LOGIN('hello1');
+--------------------------------------------+
| _output                                    |
+--------------------------------------------+
| SELECT * FROM `TB_TEST` WHERE `uid`=hello1 |
+--------------------------------------------+
1 row in set (0.00 sec)

+----+--------+------+---------------------+---------------------+
| no | uid    | type | create              | update              |
+----+--------+------+---------------------+---------------------+
|  1 | hello1 |    1 | 2019-05-24 12:52:28 | 2019-05-24 12:52:28 |
+----+--------+------+---------------------+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

regards,
Umesh
[24 May 2019 11:39] parky hyun sik
Thank Umesh.