MySQL - Operand should contain 1 column(s)

Question

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?

score 135 · Accepted Answer · answered Dec 26 '12 at 22:10

135

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

answered Dec 26 '12 at 22:10

cdhowie

158,093
24
286
300

Thanks for the response. I'll fix my query, and mark you as the answer, but just for input, do you think there's a "better" way to write my query than what I'm using now (but also disregarding the error in it)? – Dec 26 '12 at 22:13
Ah. Thank you for the edit on your original post. I will be sure to mark you as the answer when StackOverflow lets me. Thanks a lot! – Dec 26 '12 at 22:15
Well, the `COUNT()` is throwing things off a bit; the query I've given will probably give an error due to the aggregation. You may need to move that aggregation into a subquery, depending on the goals of your query (which are not clear to me at the moment). – cdhowie Dec 26 '12 at 22:15

Mauro Bilotti · Answer 2 · 2021-09-24T12:51:03.150

28

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.

edited Sep 24 '21 at 12:51

answered Sep 07 '20 at 15:15

Mauro Bilotti

5,628
4
44
65

3

I can't believe this is what my problem was, I thought brackets were legal there – Josh McGee Oct 23 '20 at 18:08
Yes, this was my problem as well :) Thank you – cantsay Oct 05 '22 at 23:34
Yes, same problem here. SQL and parenthesis and single quote marks around fields can be confusing. – dsagman Jan 15 '23 at 15:16

score 24 · Answer 3 · answered Apr 01 '16 at 06:29

24

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

answered Apr 01 '16 at 06:29

silkfire

24,585
15
82
105

1

That really saved me a lot of time. Thanks! – Mariia Abramyk Sep 15 '21 at 12:42

score 15 · Answer 4 · edited Mar 27 '18 at 07:00

15

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

FOR EXAMPLE:

WHERE product_id = (1,2,3);

edited Mar 27 '18 at 07:00

Jin Kwon

20,295
14
115
184

answered May 26 '17 at 09:43

jay padaliya

624
6
12

1

Or LIKE instead of IN as i did and couldnt find why this error happens. ty for pointer. – Edgars Aivars Jan 29 '18 at 23:05
2

It can also happen if you put brackets around the fields in the SELECT clause, e.g. SELECT (Field1, Field2) FROM Table – Paul Chris Jones Feb 24 '20 at 21:03

score 7 · Answer 5 · answered Dec 26 '12 at 22:10

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

score 3 · Answer 6 · answered Oct 15 '20 at 13:28

3

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

answered Oct 15 '20 at 13:28

Janac Meena

3,203
35
32

this does not match with the first answer here: https://stackoverflow.com/questions/24551177/hql-unexpected-ast-node-vector – rubydio Oct 29 '20 at 12:20
1

@rubydio, that question references HQL and Hibernate, whereas my answer is referring to MySQL in an Intellij console – Janac Meena Nov 02 '20 at 18:05
@janac-meena thanks, i knew it, but when is already written i couldn't see the error – Felipe Anselmo Sep 01 '23 at 14:47

score 1 · Answer 7 · answered Aug 21 '19 at 13:04

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!

score 0 · Answer 8 · edited Sep 12 '18 at 21:49

0

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)

edited Sep 12 '18 at 21:49

kgui

4,015
5
41
53

answered Sep 12 '18 at 19:50

iAndy

11
3

score 0 · Answer 9 · answered Dec 08 '19 at 05:53

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.

answered Dec 08 '19 at 05:53

Moshiur Rahman

1,534
12
26

score 0 · Answer 10 · edited Oct 07 '22 at 12:44

0

I also have the same issue in making a company database. this is the code

SELECT FNAME,DNO FROM EMP 
 WHERE SALARY IN (SELECT MAX(SALARY), DNO
FROM EMP GROUP BY DNO);

edited Oct 07 '22 at 12:44

Nathan

789
11
20

answered Oct 01 '22 at 13:39

Silly Devil

1

score 0 · Answer 11 · answered Apr 12 '23 at 05:46

I also had this error message using Dapper with MySQL. Example code below:

SELECT ColA, ColB, ColC
FROM Table
WHERE ColA IN (@listOfColumns);

The issue with the above code is Dapper will automatically add the brackets for the @listOfColumns parameter substitution meaning the brackets in the snippet above will double wrap the list of columns causing the error.

MySQL - Operand should contain 1 column(s)

11 Answers11

Linked

Related