MySQL Subquery returns more than one row

Question

I have a table namely tbl_plays. It has the following columns and data

id(PK)| gameid | drawid | etc
----------------------------------------------
 1 | 1509 | 73 | etc

I am running this simple query.

SELECT id FROM tbl_play WHERE id = 1 OR gameid = 0

This query is run as a subquery which returns the id column data to a variable.

Now my question, why do I get the error SUBQUERY RETURNS MORE THAN ONE ROW where as it returns only one row. In Workbench it returns an editable result-set, and myAdmin shows edit/delete buttons.

Does it have anything related to the fact that I am fetching a PK column data? If it does, then how can I over come this.

Regards

P.S: id column has all unique values.

• Jeroen: What if one row matches the id, and another one matches the gameid?
• echo_salik: All the values or row ID and GameID are unique. All values are greater than zero. Sorry for not mentioning that.
• Jeroen: Why are you looking for the GameID then? You could do with just searching for the ID.

Answer 1

You need to add the LIMIT clause

SELECT id FROM tbl_play WHERE id = 1 OR gameid = 0 LIMIT 1;

This is an oversimplified answer.

If there is a row with PK=1, you find it.

If not, a full table scan may ensue because of gameid not being indexed.

You are better off splitting the query with a UNION. Perhaps, like this:

SELECT id FROM
(
 SELECT id FROM tbl_play WHERE id = 1
 UNION
 (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;

Since I do not know the data, I cannot predict any performance results.

Give it a Try !!!

UPDATE 2014年01月23日 11:01 EST

The reason it works because of the goal I set for the query : Retrieve one value.

First look at the UNION

 SELECT id FROM tbl_play WHERE id = 1
 UNION
 (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)

The first SELECT can return at most one row.

The second SELECT can return at most one row because of the LIMIT 1.

In the worst case scenario, the UNION will have two rows.

Now look at the whole query:

SELECT id FROM
(
 SELECT id FROM tbl_play WHERE id = 1
 UNION
 (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;

The outer part of the query will receive either 0, 1, or 2 rows.

Then, the part ORDER BY id LIMIT 1 forces the outer query to choose the first value.

• echo_salik: It works. :) Can you tell me the reason why this works?
• RolandoMySQLDBA: I updated my answer to explain why my suggested query works.