Bug #21877 IN gives: ERROR 1241 (21000): Operand should contain <N> column(s)

• Submitted: 28 Aug 2006 15:54
• Modified: 29 Aug 2006 8:32
• Reporter: Roland Bouman
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.11
• CPU Architecture: Any
• Tags: IN

[28 Aug 2006 15:54] Roland Bouman

Description:
An IN quey fails when multiple column operands are compared to multiple constants. 

How to repeat:
delete from City where (CountryCode,District,Name) in ('CHN','Liaoning','Jinzhou') limit 1;

ERROR 1241 (21000): Operand should contain 3 column(s)

Suggested fix:
Support the multiple operands IN operation also for constants. 

delete from City where (CountryCode,District,Name) in (select 'CHN','Liaoning','Jinzhou') limit 1;

works as expected.

[29 Aug 2006 6:51] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Read carefully about IN() here: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

[29 Aug 2006 8:32] Roland Bouman

Hi! I read the relevant documentation, but the use of tuples as operands is not described there at all. There are some user comments that doe describe the use of tuples. 

I did find out my queries contained a semantic error. To correcly denote the tuples, an extra set of parenthesis is needed:

select * 
from    City 
where  (CountryCode,District,Name) 
in     (
          ('CHN','Liaoning','Jinzhou')
       )
;

So, one pair to denote the list, and one pair to denote a tuple.