MySQL Bugs: #25220: DOC: Differences between IN and =ANY

Bug #25220	DOC: Differences between IN and =ANY
Submitted:	20 Dec 2006 15:21	Modified:	10 Jan 2007 18:08
Reporter:	Luigi Alice	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.34-BK	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
This works:

select A from B where A IN (select C from D);
select A from B where A = ANY (select C from D);
select A from B where A = ANY ((select C from D));
select A from B where A IN ('x', 'y', 'z');

This does not work:

select A from B where A IN ((select C from D));
select A from B where A = ANY ('x', 'y', 'z');

Why this, if the manual says, that IN and =ANY are synonyms?

http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

Please, double "(())" should be allowed like in MSSQL and Oracle etc...

How to repeat:
Just use any Table

Thank you for a problem report. One part of your report is not a bug, but the other I do can repeat (with 5.0.34-BK). Look:

mysql> show create table t1;
+-------+-----------------------------------------------------------------------
----------------+
| Table | Create Table
                |
+-------+-----------------------------------------------------------------------
----------------+
| t1    | CREATE TABLE `t1` (
  `i` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
----------------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+-----------------------------------------------------------------------
----------------+
| Table | Create Table
                |
+-------+-----------------------------------------------------------------------
----------------+
| t2    | CREATE TABLE `t2` (
  `k` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
----------------+
1 row in set (0.00 sec)

mysql> select i from t1 where i in (select k from t2);
Empty set (0.01 sec)

mysql> select i from t1 where i = ANY (select k from t2);
Empty set (0.01 sec)

mysql> select i from t1 where i = ANY ((select k from t2));
Empty set (0.01 sec)

mysql> select i from t1 where i in (1, 2, 3);
Empty set (0.00 sec)

mysql> select i from t1 where i = ANY (1, 2, 3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '1, 2,
 3)' at line 1

The above is not a bug. ANY should be used with subquery, and there is no subquery above.

mysql> select i from t1 where i in ((select k from t2));
ERROR 1242 (21000): Subquery returns more than 1 row

This looks like a bug for me, or a request for more explanation on that manual page, not just:

"The word IN is an alias for = ANY. Thus, these two statements are the same"

Looks like in this context IN expects list of expressions, and internal () should be one scalar item from that list. Let's try:

mysql> select k from t2;
+------+
| k    |
+------+
|  100 |
|   42 |
+------+
2 rows in set (0.00 sec)

Indeed, several values...

mysql> select i from t1 where i in ((select k from t2 limit 1));
Empty set (0.00 sec)

Now we have that item of expressions list. Looks like a parser bug for me, indeed.

See Related Bug#21904

This report contains two parts:

Part 1:

select A from B where A IN ((select C from D));
this is a bug, and is a duplicate of Bug#21904.

Part 2:

select A from B where A = ANY ('x', 'y', 'z');
does not work, and is not a bug in the code as explained earlier.

Part 2 is a bug in the documentation, which should clarify when 'IN' and 'ANY'
are equivalent or not, since the wording is misleading.

As a result and to track the code and documentation issues separately :
- Bug#21904 will fix part 1
- Bug#25220 will fix part 2, and is not flaged as a duplicate.

Changing the title and reassigning accordingly.

Thanks for the report.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.