CODEWITHSUNDEEP
mysql
reza.cse08
reza.cse08

Reputation: 6178

Parameter name conflict with column name in MySQL

The following code works fine when put it outside a function and return 1 if it exists at the table. 

 SET @Result = (SELECT (
     CASE WHEN NOT EXISTS(SELECT 1 FROM `Member` WHERE Username = 'username') THEN 10
     ELSE 1
END) r);

SELECT @Result;

But it returns 10 when I am passing the value 'username' to a function like below

CREATE DEFINER=`root`@`localhost` FUNCTION `FN_CheckUsername`(
   Username VARCHAR(128)
) RETURNS int
READS SQL DATA
BEGIN
  SET @Result = (SELECT (
    CASE WHEN NOT EXISTS(SELECT 1 FROM `Member` WHERE Username = Username) THEN 10
     ELSE 1
    END) r);

RETURN @Result;
END

Upvotes: 0

Views: 139

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521467

Please try naming your username parameter to the FN_CheckUsername function something other than Username:

CREATE DEFINER=`root`@`localhost` FUNCTION `FN_CheckUsername`(uname VARCHAR(128))
    RETURNS int
READS SQL DATA
BEGIN
    SET @Result = (SELECT (
        CASE WHEN NOT EXISTS(SELECT 1 FROM `Member` WHERE Username = uname)
             THEN 10 ELSE 1 END) r);
    RETURN @Result;
END

The parameter and column name could be masking each other, which would result in the exists clause always being true.

Upvotes: 1

Related Questions