Issue with COUNT(*)

Question

I'm having an issue with this bit of SQL. For some reason, the value being inserted into wCount is always 1. I've tested it using values I know should evaluate to 0, but it still comes out of the procedure as 1. I've also tested the SELECT on it's own outside of the procedure, and the result is 0, using the same values. What is going wrong here?

-- Add user role to table if user does not already have that role assigned to them

CREATE PROCEDURE `new_user_role`(IN userID INT(11), IN roleID INT(11))
BEGIN   
DECLARE wCount INT DEFAULT 0;

SELECT COUNT(*)
INTO wCount
FROM userrole
WHERE RoleID = roleID
AND UserID = userID;

IF wCount = 0 THEN
    INSERT INTO userrole(UserID, RoleID) VALUES(userID, roleID);
END IF;
END

Here is the create table statement

CREATE TABLE `userrole` (
`URKey` bigint(20) NOT NULL AUTO_INCREMENT,
`UserID` int(11) NOT NULL,
`RoleID` int(11) NOT NULL,
PRIMARY KEY (`URKey`),
KEY `FK_UserRole_User_idx` (`UserID`),
KEY `FK_UserRole_Role_idx` (`RoleID`),
CONSTRAINT `FK_UserRole_Role` FOREIGN KEY (`RoleID`) REFERENCES `role`   (`RoleID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_UserRole_User` FOREIGN KEY (`UserID`) REFERENCES `user` (`UserID`) ON DELETE CASCADE ON UPDATE CASCADE

);

If you change it to `COUNT(RoleID)` will it still return 1? – sagi Jun 27 '16 at 14:48 — sagi, Jun 27 '16 at 14:48
Can you post your database table here as well? – Ctc Jun 27 '16 at 14:54 — Ctc, Jun 27 '16 at 14:54
If I count the column it still returns 1 – M. Cove Jun 27 '16 at 15:13 — M. Cove, Jun 27 '16 at 15:13

score 0 · Accepted Answer · answered Jun 27 '16 at 15:13

0

You are passing parameters named userID and roleID, thinking perhaps that they are different from the column names RoleID and UserID. If you pass parameters named (say) XuserID and XroleID, and then use "WHERE RoleID = XroleID AND UserID = XuserID" in the WHERE clause, I predict you'll get something more like what you hoped for.

answered Jun 27 '16 at 15:13

Peter Gulutzan

465
2
8

Thanks! This solved the issue. So the problem was that my input parameter names were the same as the column names? – M. Cove Jun 27 '16 at 15:20
Yes, the identifiers are not case sensitive in this context, so in effect the where condition was interpreted as asking whether a column value was equal to itself. – Peter Gulutzan Jun 27 '16 at 15:44

score 0 · Answer 2 · edited May 23 '17 at 10:28

The problem is with parameters' names. If you want to keep those names, then you have to scope your table column by an alias: MySql Stored Procedure's parameter with the same name as affecting's column, is it possible?

Even though the problem with your query has been found, if I understand your goal correctly, what you're trying to do is to check if a value "exists" before inserting it.

If so, why don't you try this instead:

IF NOT EXISTS (SELECT * FROM userrole WHERE  RoleID = varRoleID AND AND UserID = varUserID) THEN
     INSERT INTO userrole(UserID, RoleID) VALUES(varUserID, varRoleID);
END IF;

It is similar to this: Usage of MySQL's "IF EXISTS"

However this will only work because you're using in a stored procedure.

Issue with COUNT(*)

2 Answers2