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

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)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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


Method 3

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

Method 4

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.

Method 5

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0