MySQL Bugs: #14556: Double-brackets cause "Subquery returns more than 1 row" error

Bug #14556	Double-brackets cause "Subquery returns more than 1 row" error
Submitted:	2 Nov 2005 0:54	Modified:	2 Dec 2005 8:57
Reporter:	Marc Hodgins (Candidate Quality Contributor)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.14	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
With a DELETE statement that uses a subquery in the WHERE clause, if the subquery returns multiple rows AND it is contained within an extra set of brackets than is necessary, it causes a "Subquery returns more than 1 row" error to be returned.

I stumbled across this when writing multiple functions that each handle a part of building the SQL statement, and they accidentially doubled-up the brackets.  I had assumed that extra layers of brackets would typically be discarded without causing a change in the meaning of the subquery -- no?

How to repeat:
The following query works fine (assuming the subquery returns more than one row):

DELETE FROM links WHERE link_id=1 AND link_type IN (SELECT link_type FROM link_types WHERE link_type_group=2)

However, an extra set of brackets with the exact same query returns the "subquery returns more than 1 row" error:

DELETE FROM links WHERE link_id=1 AND link_type IN ((SELECT link_type FROM link_types WHERE link_type_group=2))

Is this by design or a bug?

Suggested fix:
Have the parser silently discard extra brackets that otherwise have no meaning..

Thank you for a problem report. Sorry, but I can't repeat the problem you described on latest 4.1.16 and 5.0.16 builds with the following actions:

mysql> create table test(c1 int);
Query OK, 0 rows affected (0,01 sec)

mysql> insert into test values (1), (2);
Query OK, 2 rows affected (0,00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table test2 as select * from test;
Query OK, 2 rows affected (0,09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from test where c1 in (select c1 from test2 where c1=1);
Query OK, 1 row affected (0,10 sec)

mysql> select * from test;
+------+
| c1   |
+------+
|    2 |
+------+
1 row in set (0,00 sec)

mysql> delete from test where c1 in ((select c1 from test2 where c1=2));
Query OK, 1 row affected (0,01 sec)

mysql> select * from test;
Empty set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.16    |
+-----------+
1 row in set (0,00 sec)

Now on 4.1.16:

mysql> create table test(c1 int);
Query OK, 0 rows affected (0,04 sec)

mysql> insert into test values (1), (2);
Query OK, 2 rows affected (0,00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table test2 as select * from test;
Query OK, 2 rows affected (0,04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from test where c1 in (select c1 from test2 where c1=1);
Query OK, 1 row affected (0,05 sec)

mysql> delete from test where c1 in ((select c1 from test2 where c1=2));
Query OK, 1 row affected (0,02 sec)

mysql> select * from test;
Empty set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.16    |
+-----------+
1 row in set (0,00 sec)

Can it be so, that subquery really returns more than 1 row? Please, provide a complete simple test case, like mine.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".