Why is WHERE clause in MySQL query (/stored procedure) returning unwanted data?-CodePudding

I have a table, quotes, with UserID as a primary key. I want to isolate user-specific data by querying based on UserID. However, when I query the table for rows that match a specific UserID, rows are returned that do not match the given ID. Does anyone here know why?

I tried rewriting the query with MATCH(UserID) AGAINST(USERID), but that returned Error 1210: Incorrect arguments to MATCH, and I felt as though I might be moving in the wrong direction.

Here is my quotes table script:

DROP TABLE IF EXISTS quotes;

CREATE TABLE quotes (
UserID varchar(255) NOT NULL,
Gallons decimal(10,0) NOT NULL,
Date varchar(50) NOT NULL,
Address varchar(255) NOT NULL,
InState tinyint DEFAULT NULL,
ppg decimal(10,0) GENERATED ALWAYS AS (if(InState,3,5)) STORED,
Total decimal(10,0) GENERATED ALWAYS AS ((Gallons * ppg)) STORED,
FullName varchar(101) NOT NULL,
KEY UserID (UserID),
CONSTRAINT quotes_ibfk_1 FOREIGN KEY (UserID) REFERENCES users (UserID)
)

Here is my stored procedure that queries the Quote table:

CREATE DEFINER=`root`@`localhost` PROCEDURE `quoteGet`
(       
    IN USERID varchar(255)  
) 
BEGIN       
    SELECT * FROM QUOTES WHERE UserID = USERID;  
END

The two UserID's that mySQL found equal enough to return were 'ddb22baa90894e24e1d53c186e203eba' and 'cde6a2f820a251817cd62c5ca3f322ae'. I called the stored procedure with the latter, and it returned entries of the former along with entries of the latter.

CodePudding user response：

The parameter should be renamed:

CREATE DEFINER=`root`@`localhost` PROCEDURE `quoteGet`
(       
    IN P_USERID varchar(255)  
) 
BEGIN       
    SELECT * FROM QUOTES WHERE UserID = P_USERID;  
END

In current setup UserId = USERID is treated as 1=1.