| 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: | |
| 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 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.
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');