MySQL Subquery returns more than one row

MySQLprimary-keystored-proceduressubquery

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.

Best Answer

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.

Related Question