Bug #80765 GROUP_CONCAT() function with IN() statement. Wrong result

• Submitted: 16 Mar 2016 12:41
• Modified: 20 Mar 2016 14:09
• Reporter: Elshad Agayev
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.5.47
• OS: Ubuntu
• CPU Architecture: Any
• Tags: group_concat

[16 Mar 2016 12:41] Elshad Agayev

Description:
I'm not sure is it bug or not. But we all know that GROUP_CONCAT() function returns a string value. When to try to use this function with in() statement we have to get an error response. Let's write a select like this: SELECT * FROM t1 WHERE id IN (SELECT GROUP_CONCAT(id SEPARATOR ',') FROM t1)
If the optimizer replaces the group_concat's string result with subquery than above query should looks like this: SELECT * FROM t1 WHERE id IN ('1,2,3')
This is wrong syntax for select statement. But if we run first example we'll get one record with the id equal to first value of group_concat result. It is logically wrong I think.

How to repeat:
create a table:

CREATE TABLE `t1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(10) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert sample records:

INSERT INTO `t1` (`id`, `title`) VALUES (NULL, 'title 1'), (NULL, 'title 2'), (NULL, 'title 3'), (NULL, 'title 4'), (NULL, 'title 5'), (NULL, 'title 6'), (NULL, 'title 7'), (NULL, 'title 8'), (NULL, 'title 9'), (NULL, 'title 10');

At last run this query:

mysql> SELECT * FROM t1 WHERE id IN (SELECT GROUP_CONCAT(id SEPARATOR ',') FROM t1);
+----+---------+
| id | title   |
+----+---------+
|  1 | title 1 |
+----+---------+
1 row in set (0.00 sec)

As you see we got one record with the id number 1.

Suggested fix:
I think this is query optimizer problem and should be fixed in source codes.

[20 Mar 2016 14:09] MySQL Verification Team

Thank you for the bug report.

C:\dbs>5.5\bin\mysql -uroot --port=3550 -e "SELECT * FROM test.t1 WHERE id IN (SELECT GROUP_CONCAT(id  SEPARATOR ',') FROM test.t1)"
+----+---------+
| id | title   |
+----+---------+
|  1 | title 1 |
+----+---------+

C:\dbs>5.5\bin\mysql -uroot --port=3550 -e "SELECT * FROM test.t1 WHERE id IN (SELECT GROUP_CONCAT(id  SEPARATOR ',') FROM test.t1 GROUP BY id)"
+----+----------+
| id | title    |
+----+----------+
|  1 | title 1  |
|  2 | title 2  |
|  3 | title 3  |
|  4 | title 4  |
|  5 | title 5  |
|  6 | title 6  |
|  7 | title 7  |
|  8 | title 8  |
|  9 | title 9  |
| 10 | title 10 |
+----+----------+