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?

Your subquery is selecting two columns, while you are using it to project one column (as part of the outer SELECT clause). You can only select one column from such a query in this context.

Consider joining to the users table instead; this will give you more flexibility when selecting what columns you want from users.

SELECT
topics.id,
topics.name,
topics.post_count,
topics.view_count,
COUNT( posts.solved_post ) AS solved_post,
users.username AS posted_by,
users.id AS posted_by_id

FROM topics

LEFT OUTER JOIN posts ON posts.topic_id = topics.id
LEFT OUTER JOIN users ON users.id = posts.posted_by

WHERE topics.cat_id = :cat
GROUP BY topics.id

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

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

SELECT (p.column1, p.column2, p.column3) FROM table1 p WHERE p.column1 = 1;

And has to be:

SELECT p.column1, p.column2, 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.

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

FOR EXAMPLE:

WHERE product_id = (1,2,3);

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)
...

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

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!

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)

(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.