TheWebGuy TheWebGuy - 4 months ago 9
SQL Question

MySQL complaining about multiple rows

I have the following logic in a mysql stored proc:

SET @UserId = (
Select userid
FROM tickets
where ticketid = ticketID
);


It's giving me an error: Subquery returns more than 1 row

I removed the set around it and just ran the query:

Select userid
FROM tickets
where ticketid = ticketID;


I get back 1 record, why is it complaining that I have more than 1 row? ticketid is the primary key on that table as well!

Answer

Seems like the predicate in the WHERE clause

 ticketid = ticketID 

would evaluate to TRUE for all non-null values of ticketid.

Essentially equivalent to specifying

 ticketid IS NOT NULL

And if ticketid is the PRIMARY KEY, then every row in the table would satisfy that condition.


I'm just guessing here. I suspect you have declared a variable named ticketID and are expecting MySQL to differentiate between ticketid being a reference to a column, and ticketID being a reference to a variable. But that doesn't happen. If there is a procedure variable named ticketID (that's declared in scope of a SQL statement), MySQL takes a reference to ticketid as a reference to the variable. So, again, every row in the table will be returned if ticketid variable has a non-null value.


From the MySQL Reference Manual:

A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets a reference to that name as the name of a variable.

Reference: http://dev.mysql.com/doc/refman/5.7/en/local-variable-scope.html


If that's the issue (again, I'm just guessing there) a workaround is to qualify references to the columns with the table name or table alias.

A lot of developers also name their variables with a prefix, or some other convention, which makes it easier for the reader to distinguish variable names from column names, by reducing ambiguity.

For example:

  DECLARE lv_ticketid INT; 

  SET @UserId = ( SELECT t.userid FROM tickets t WHERE t.ticketid = lv_ticketid );

And where we know that only one row will be returned, we can also add a LIMIT 1 to the query, depending on whether we want an error thrown when there is more than one row returned.

  SET @UserId = ( SELECT t.userid
                   FROM tickets t
                  WHERE t.ticketid = lv_ticketid 
                  ORDER BY t.userid ASC
                  LIMIT 1 
                );
Comments