MySQL returning two different results from query vs stored procedure-CodePudding

I'm baffled as to why MySQL Workbench returns 7 (the correct answer) for this query

SELECT ScheduleLocationEventColorID 
FROM schedulelocationeventcolor 
WHERE ScheduleID = 1 AND LocationID = 1;

but returns 1 (incorrect) for the equivalent in a stored procedure.

CREATE PROCEDURE `test`(
    IN locationID INT,
    IN scheduleID INT
)
BEGIN
    DECLARE slecID INT;

    SELECT ScheduleLocationEventColorID 
    INTO slecID 
    FROM schedulelocationeventcolor 
    WHERE ScheduleID = scheduleID AND LocationID = locationID
    LIMIT 1;
    
    SELECT slecID;
END

-- calling the stored procedure here: 
CALL `test`(1, 1);

I tried changing the inputs in the stored procedure call, but I cannot figure out for the life of me why I keep getting a 1 when the result should be a 7.

CodePudding user response：

You should name your procedure parameters something different from your column names. Column names are case-insensitive.

Right now your procedure has a query with this condition:

WHERE ScheduleID = ScheduleID AND LocationID = LocationID

Which evaluates as true AND true because comparing a column to itself is bound to be true (unless the column is NULL).

So with the LIMIT 1, the query returns the first row where both columns are not NULL.