MySQL Bugs: #21904: parser problem when using IN with a double "(())"

Bug #21904	parser problem when using IN with a double "(())"
Submitted:	29 Aug 2006 15:25	Modified:	18 Mar 2007 22:43
Reporter:	Marco Tusa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql-max-5.0.24-linux-i686-icc-glibc	OS:	Linux (LINUX (redhat / fedora))
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	rt_q1_2007

Description:
The problem raise migrating a java application using Hibernate, while it was performing a simple query using the IN statment: 

SELECT DISTINCT 
obj.ID as x0_0_ FROM 
MEDIA obj WHERE (423 IN((SELECT reg0_.ID_REGION FROM Media_X_Region reg0_ WHERE obj.ID=reg0_.id)))

having as result: 
ERROR 1242 (21000): Subquery returns more than 1 row
xxx@localhost [mediabase2]> SELECT DISTINCT
    -> obj.ID as x0_0_ FROM
    -> MEDIA obj WHERE (423 in((SELECT reg0_.ID_REGION FROM Media_X_Region reg0_ WHERE obj.ID=reg0_.id)));
ERROR 1242 (21000): Subquery returns more than 1 row
xxx@localhost [mediabase2]>

After some test I realized that the problem was generated by the double parentesis AFTER the IN ((.. .. ..)).

if using ANY instead in a different message will occur:
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 '(SELECT reg0_.ID_REGION FROM Media_X_Region reg0_ WHERE obj.ID=reg0_.id)))' at line 1

How to repeat:
create 3 tables as follows:
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id_a` int(5) NOT NULL auto_increment,
  `t` varchar(4) collate utf8_bin default NULL,
  PRIMARY KEY  (`id_a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

LOCK TABLES `a` WRITE;
INSERT INTO `a` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d');
UNLOCK TABLES;

DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id_b` int(5) NOT NULL auto_increment,
  `t` varchar(4) collate utf8_bin default NULL,
  PRIMARY KEY  (`id_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

LOCK TABLES `b` WRITE;
INSERT INTO `b` VALUES (2,'bb'),(3,'cc'),(4,'dd'),(12,'aa');
UNLOCK TABLES;

DROP TABLE IF EXISTS `axb`;
CREATE TABLE `axb` (
  `id_a` int(5) NOT NULL,
  `id_b` int(5) NOT NULL,
  PRIMARY KEY  (`id_a`,`id_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

LOCK TABLES `axb` WRITE;
INSERT INTO `axb` VALUES (1,12),(2,2),(3,3),(4,4);
UNLOCK TABLES;

then perform the select:
SELECT DISTINCT  a.id_a FROM  a WHERE (12 in((SELECT axb.id_b FROM axb WHERE a.id_a=axb.id_a)));
+------+
| id_a |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

IT WILL WORKS, the do:
xxx@localhost [mediabase2]> insert into axb values(2,12);
Query OK, 1 row affected (0.03 sec)

xxx@localhost [mediabase2]> SELECT DISTINCT  a.id_a FROM  a WHERE (12 in((SELECT axb.id_b FROM axb WHERE a.id_a=axb.id_a)));
ERROR 1242 (21000): Subquery returns more than 1 row

IT will not BUT if you remove the double IN (()):

xxx@localhost [mediabase2]> SELECT DISTINCT  a.id_a FROM  a WHERE (12 in(SELECT axb.id_b FROM axb WHERE a.id_a=axb.id_a));
+------+
| id_a |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

Suggested fix:
This looks like a parser review problem, don't know how to fix...

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

See also user note "Posted by Per Persson on January 28 2005 12:04pm" here: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

I have already read the doc several times and I haven't found any mension of the double parentesis using IN, so either you tell me the exact page where the statement is or you reconize it as a bug.
Other db like Oracle for instance handle with the parser.

Please note that we have test it also with mysql 5.0.24 standard and it works without generating the double (( problem.

So this difference underline that how the max version behave IS NOT CORRECT.

Please check it.

Marco, I've just tried with mysql-standard-5.0.24-linux-i686 package:

ssmirnova@shella ~/mysql-standard-5.0.24-linux-i686
$bin/mysql --socket=/tmp/mysql.sock -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `a`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `a` (
    ->   `id_a` int(5) NOT NULL auto_increment,
    ->   `t` varchar(4) collate utf8_bin default NULL,
    ->   PRIMARY KEY  (`id_a`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Query OK, 0 rows affected (0.18 sec)

mysql>
mysql> LOCK TABLES `a` WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `a` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS `b`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `b` (
    ->   `id_b` int(5) NOT NULL auto_increment,
    ->   `t` varchar(4) collate utf8_bin default NULL,
    ->   PRIMARY KEY  (`id_b`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  `id_a` int(5) NOT NULL,
  `id_b` int(5) NOT NULL,
  PRIMARY KEY  (`id_a`,`id_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

LOCK TABLES `axb` WRITE;
INSERT INTO `axb` VALUES (1,12),(2,2),(3,3),(4,4);
UNLOCK TABLES;

SELECT DISTINCT  a.id_a FROM  a WHERE (12 in((SELECT axb.id_b FROM axb WHERE
a.id_a=axb.id_a)));Query OK, 0 rows affected (0.15 sec)

mysql>
mysql> LOCK TABLES `b` WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `b` VALUES (2,'bb'),(3,'cc'),(4,'dd'),(12,'aa');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS `axb`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE `axb` (
    ->   `id_a` int(5) NOT NULL,
    ->   `id_b` int(5) NOT NULL,
    ->   PRIMARY KEY  (`id_a`,`id_b`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> LOCK TABLES `axb` WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `axb` VALUES (1,12),(2,2),(3,3),(4,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT DISTINCT  a.id_a FROM  a WHERE (12 in((SELECT axb.id_b FROM axb WHERE
    -> a.id_a=axb.id_a)));
+------+
| id_a |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into axb values(2,12);
Query OK, 1 row affected (0.10 sec)

mysql> SELECT DISTINCT  a.id_a FROM  a WHERE (12 in((SELECT
    -> axb.id_b FROM axb WHERE a.id_a=axb.id_a)));
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> SELECT DISTINCT  a.id_a FROM  a WHERE (12 in(SELECT
    -> axb.id_b FROM axb WHERE a.id_a=axb.id_a));
+------+
| id_a |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> \q
Bye

As you see results are same as in your initial description. If you use another 5.0.24 standard package, indicate accurate version of it and reopen bug.

According to some internal communication, this is a SQL 2003 violation and must be considered a bug.

See internal documentation above.

According to last comment verified as "SQL 2003 violation".

But "= ANY" seems to have a different behavior:

A SELECT like this works:

select MA_REFNR from MA where (MA_REFNR <b>= ANY</b> ((select MA_REFNR from MA where 1=1)))

but not this one:

select MA_REFNR from MA where (MA_REFNR <b>IN</b> ((select MA_REFNR from MA where 1=1)))

So, what to do?
Replace "IN" with "= ANY" ?

My SELECTs are generated dynamically, so there can be multiple parenthese
.

See related bug#25220

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17692

ChangeSet@1.2321, 2007-01-05 18:33:08-07:00, malff@weblab.(none) +5 -0
  Bug#21904 (parser problem when using IN with a double "(())")
  
  Before this fix, a IN predicate of the form: "IN (( subselect ))", with two
  parenthesis, would be evaluated as a single row subselect: if the subselect
  returns more that 1 row, the statement would fail.
  
  The SQL:2003 standard defines a special exception in the specification,
  and mandates that this particular form of IN predicate shall be equivalent
  to "IN ( subselect )", which involves a table subquery and works with more
  than 1 row.
  
  This fix implements "IN (( subselect ))", "IN ((( subselect )))" etc
  as per the SQL:2003 requirement.
  
  All the details related to the implementation of this change have been
  commented in the code, and the relevant sections of the SQL:2003 spec
  are given for reference, so they are not repeated here.
  
  Having access to the spec is a requirement to review in depth this patch.

Bug #25734 was marked as duplicate of this one

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18989

ChangeSet@1.2321, 2007-01-29 17:32:52-07:00, malff@weblab.(none) +5 -0
  Bug#21904 (parser problem when using IN with a double "(())")
  
  Before this fix, a IN predicate of the form: "IN (( subselect ))", with two
  parenthesis, would be evaluated as a single row subselect: if the subselect
  returns more that 1 row, the statement would fail.
  
  The SQL:2003 standard defines a special exception in the specification,
  and mandates that this particular form of IN predicate shall be equivalent
  to "IN ( subselect )", which involves a table subquery and works with more
  than 1 row.
  
  This fix implements "IN (( subselect ))", "IN ((( subselect )))" etc
  as per the SQL:2003 requirement.
  
  All the details related to the implementation of this change have been
  commented in the code, and the relevant sections of the SQL:2003 spec
  are given for reference, so they are not repeated here.
  
  Having access to the spec is a requirement to review in depth this patch.

Pushed into  5.0.38, 5.1.17

Noted in 5.0.38, 5.1.17 changelogs.

IN ((subquery)), IN (((subquery))), and so forth, are equivalent to
IN (subquery), which is always interpreted as a table subquery (so
that it is allowed to return more than one row). MySQL was treating
the "over-parenthesized" subquery as a single-row subquery and
rejecting it if it returned more than one row. This bug primarily
affected automatically generated code (such as queries generated by
Hibernate), because humans rarely write the over-parenthesized forms.