[mysql] MySQL - Operand should contain 1 column(s)

While working on a system I'm creating, I attempted to use the following query in my project:

SELECT
topics.id,
topics.name,
topics.post_count,
topics.view_count,
COUNT( posts.solved_post ) AS solved_post,
(SELECT users.username AS posted_by,
    users.id AS posted_by_id
    FROM users
    WHERE users.id = posts.posted_by)
FROM topics
LEFT OUTER JOIN posts ON posts.topic_id = topics.id
WHERE topics.cat_id = :cat
GROUP BY topics.id

":cat" is bound by my PHP code as I'm using PDO. 2 is a valid value for ":cat".

That query though gives me an error: "#1241 - Operand should contain 1 column(s)"

What stumps me is that I would think that this query would work no problem. Selecting columns, then selecting two more from another table, and continuing on from there. I just can't figure out what the problem is.

Is there a simple fix to this, or another way to write my query?

This error can also occur if you accidentally use = instead of IN in the WHERE clause:

FOR EXAMPLE:

WHERE product_id = (1,2,3);

Another place this error can happen in is assigning a value that has a comma outside of a string. For example:

SET totalvalue = (IFNULL(i.subtotal,0) + IFNULL(i.tax,0),0)

This error can also occur if you accidentally use commas instead of AND in the ON clause of a JOIN:

JOIN joined_table ON (joined_table.column = table.column, joined_table.column2 = table.column2)
                                                        ^
                                             should be AND, not a comma

This error can also occur if you accidentally miss if function name.

for example:

set v_filter_value = 100;

select
    f_id,
    f_sale_value
from
    t_seller
where
    f_id = 5
    and (v_filter_value <> 0, f_sale_value = v_filter_value, true);

Got this problem when I missed putting if in the if function!

I got this error while executing a MySQL script in an Intellij console, because of adding brackets in the wrong place:

WRONG:

SELECT user.id
FROM user
WHERE id IN (:ids); # Do not put brackets around list argument

RIGHT:

SELECT user.id
FROM user
WHERE id IN :ids; # No brackets is correct

(SELECT users.username AS posted_by,
users.id AS posted_by_id
FROM users
WHERE users.id = posts.posted_by)

Here you using sub-query but this sub-query must return only one column. Separate it otherwise it will shows error.

In my case, the problem was that I sorrounded my columns selection with parenthesis by mistake:

SELECT (p.column1, p.colum2, p.column3) FROM table1 p where p.column1 = 1;

And has to be:

SELECT p.column1, p.colum2, p.column3 FROM table1 p where p.column1 = 1;

Sounds silly, but it was causing this error and it took some time to figure it out.

COUNT( posts.solved_post ) AS solved_post,
(SELECT users.username AS posted_by,
    users.id AS posted_by_id
    FROM users
    WHERE users.id = posts.posted_by)

Well, you can’t get multiple columns from one subquery like that. Luckily, the second column is already posts.posted_by! So:

SELECT
topics.id,
topics.name,
topics.post_count,
topics.view_count,
posts.posted_by
COUNT( posts.solved_post ) AS solved_post,
(SELECT users.username AS posted_by_username
    FROM users
    WHERE users.id = posts.posted_by)
...