MySQL Syntax error message "Operand should contain 1 column(s)"

Question

I tried running the following statement:

INSERT INTO VOUCHER (VOUCHER_NUMBER, BOOK_ID, DENOMINATION)
SELECT (a.number, b.ID, b.DENOMINATION) 
FROM temp_cheques a, BOOK b
WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;

which, as I understand it, should insert into VOUCHER each record from temp_cheques with the ID and DENOMINATION fields corresponding to entries in the BOOK table (temp_cheques comes from a database backup, which I'm trying to recreate in a different format). However, when I run it, I get an error:

Error: Operand should contain 1 column(s)
SQLState:  21000
ErrorCode: 1241

I'm running this in SQuirrel and have not had issues with any other queries. Is there something wrong with the syntax of my query?

EDIT:

The structure of BOOK is:

ID  int(11)
START_NUMBER    int(11)
UNITS   int(11)
DENOMINATION    double(5,2)

The structure of temp_cheques is:

ID  int(11)
number  varchar(20)

Have you tried removing the parenthesis in your SELECT clause? — lc., Jan 19 '09 at 06:06
Yes, and I get: Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay — Elie, Jan 19 '09 at 06:13
I edited the fields so they are all int(11) and that did not affect the error — Elie, Jan 19 '09 at 06:18
I dont know much of mysql. But does the query engine register schema changes? or it has to be restarted for it to read the modified schema? — shahkalpesh, Jan 19 '09 at 06:21
This is correct. Assuming you have many rows in both BOOK and temp_cheques, you're trying to query all rows from both tables and make a cross-reference. Set SQL_BIG_SELECTS = 1 before running this statement. — lc., Jan 19 '09 at 06:22
Thanks lc, post that as an answer and I'll mark it correct. I did that, and removed the parentheses in the select, and it worked. — Elie, Jan 19 '09 at 06:27
No problem. Glad it worked. Fixing database schemas can be a real pain, can't it? — lc., Jan 19 '09 at 07:04
Check this out: http://sourcecodeissues.blogspot.com/2011/09/1241-operand-should-contain-1-columns.html — , Sep 10 '11 at 22:37

score 60 · Accepted Answer · answered Jan 19 '09 at 06:34

Try removing the parenthesis from the SELECT clause. From Microsoft TechNet, the correct syntax for an INSERT statement using a SELECT clause is the following.

INSERT INTO MyTable  (PriKey, Description)
       SELECT ForeignKey, Description
       FROM SomeView

The error you're getting, "The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay.", is actually correct, assuming you have many rows in both BOOK and temp_cheques. You are trying to query all rows from both tables and make a cross-reference, resulting in an m*n size query. SQL Server is trying to warn you of this, before performing a potentially long operation.

Set SQL_BIG_SELECTS = 1 before running this statement, and try again. It should work, but note that this operation may take a long time.

shahkalpesh · Answer 2 · 2009-01-19T06:18:00.060

3

Does B contain the UNITS column?

What is the table structure for temp_cheques and Book?

EDIT: As I said in comments, all the columns should be numeric when doing +/- and when comparing.
Does the following simple SELECT work?

SELECT b.START_NUMBER+b.UNITS-1 FROM Books B

edited Jan 19 '09 at 06:18

answered Jan 19 '09 at 05:58

shahkalpesh

33,172
3
63
88

Yes to the first question. and I've provided the relevant portions of the structure. – Elie Jan 19 '09 at 06:04
Can Between be applied to varchar? Also, what does b.START_NUMBER+b.UNITS-1 mean to a varchar column? I think you should cast it to numeric column and apply "-1" to it. – shahkalpesh Jan 19 '09 at 06:09
I would suggest modifying the title to have error message. That might help people searching for it (rather than generic mysql error thing) – shahkalpesh Jan 19 '09 at 06:10
I updated that to int(11) like the other field, but I get the same error. – Elie Jan 19 '09 at 06:14
try using it in a select. SELECT b.START_NUMBER+b.UNITS-1 FROM Books B - does this statement work? – shahkalpesh Jan 19 '09 at 06:15
If that works, try putting the computing in a WHERE clause. e.g. SELECT b.ID, b.START_NUMBER+b.UNITS-1 FROM Books B WHERE b.START_NUMBER+b.UNITS-1 > 10000 I am doubting that mysql doesn't allow computed columns in a where clause. – shahkalpesh Jan 19 '09 at 06:24
Post your findings in a separate comment & try explaining as to how did you fix it. It will help others. – shahkalpesh Jan 19 '09 at 06:25

score 2 · Answer 3 · answered Jan 19 '09 at 06:11

2

I don't have a MySQL instance handy, but my first guess is the WHERE clause:

WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;

I imagine that the MySQL parser may be interpreting that as:

WHERE number
(BETWEEN start_number AND start_number) + units - 1

Try wrapping everything in parentheses, ie:

WHERE a.number BETWEEN b.START_NUMBER AND (b.START_NUMBER + b.UNITS - 1);

answered Jan 19 '09 at 06:11

RJHunter

2,829
3
25
30

It couldn't be; there's no other place for the "+b.UNITS-1" to go. It would either go in the BETWEEN statement, or throw an invalid token error. – lc. Jan 19 '09 at 06:20
Unless it did parse it correctly (as far as the between statement goes) and my error is something different. All I know is that with or without the parentheses, I get the same error. – Elie Jan 19 '09 at 06:23
It couldn't be = this couldn't be the problem. – lc. Jan 19 '09 at 06:24

score 2 · Answer 4 · answered Jan 19 '09 at 06:33

2

The final version of the query is as follows:

Set SQL_BIG_SELECTS = 1;
INSERT INTO VOUCHER (VOUCHER_NUMBER, BOOK_ID, DENOMINATION)
SELECT a.number, b.ID, b.DENOMINATION
FROM temp_cheques a, BOOK b
WHERE a.number BETWEEN b.START_NUMBER AND (b.START_NUMBER+b.UNITS-1);

The parsing of the BETWEEN statement required parentheses, the SELECT did not, and because of the size of the two tables (215000 records in temp_cheques, 8000 in BOOK) I was breaking a limit on the select size, requiring me to set SQL_BIG_SELECTS = 1.

answered Jan 19 '09 at 06:33

Elie

13,693
23
74
128

Yeah, you were trying to do a cross reference on 1.7 billion combinations. Nothing wrong with it, of course, just with that many comparisons, it wanted to make sure you REALLY wanted to do it. – lc. Jan 19 '09 at 07:02
at least I won't have to do this again... and it only took about 5 seconds to run. – Elie Jan 19 '09 at 07:21

score 1 · Answer 5 · answered Jul 04 '18 at 07:19

I ran into the same error when using Spring Repositories.

My repository contained a method like:

List<SomeEntity> findAllBySomeId(List<String> ids);

This is working fine when running integration tests against an in-memory database (h2). However against a stand alone database like MySql is was failing with the same error.

I've solved it by changing the method interface to:

List<someEntity findBySomeIdIn(List<String> ids);

Note: there is no difference between find and findAll. As described here: Spring Data JPA difference between findBy / findAllBy

MySQL Syntax error message "Operand should contain 1 column(s)"

5 Answers5

Linked

Related