| Bug #80224 | CONCAT_WS duplication of arguments when used in conjunction with HAVING | ||
|---|---|---|---|
| Submitted: | 1 Feb 2016 19:13 | Modified: | 15 Feb 2016 8:59 |
| Reporter: | Fredric Johansson | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.5.46, 5.6.28, 5.7.11 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[15 Feb 2016 8:59]
MySQL Verification Team
Hello Fredric, Thank you for the report and test case. Observed with 5.7.11 build. Thanks, Umesh
[15 Feb 2016 9:00]
MySQL Verification Team
-- 5.7.11
mysql> SELECT CONCAT_WS('-', GROUP_CONCAT(eg.eg_id), 'f', 'a' ) a, MAX(ep_id) epmax FROM eg JOIN ep USING (eg_id) GROUP BY en_id HAVING epmax>=1 AND a;
+-------------------------------------+---------+
| a | epmax |
+-------------------------------------+---------+
| 1008437,1008437,1008437,1008437-f-a | 3360182 |
| 1008438,1008438-f-a-f-a | 3390182 |
| 1008439,1008439,1008439,1008439-f-a | 3810002 |
+-------------------------------------+---------+
3 rows in set (0.00 sec)
mysql> SELECT CONCAT_WS('-', GROUP_CONCAT(eg.eg_id), 'f', 'a' ) a, MAX(ep_id) epmax FROM eg JOIN ep USING (eg_id) GROUP BY en_id HAVING epmax>=1;
+-------------------------------------+---------+
| a | epmax |
+-------------------------------------+---------+
| 1008437,1008437,1008437,1008437-f-a | 3360182 |
| 1008438,1008438-f-a | 3390182 |
| 1008439,1008439,1008439,1008439-f-a | 3810002 |
+-------------------------------------+---------+
3 rows in set (0.00 sec)
mysql> SELECT CONCAT_WS('-', GROUP_CONCAT(DISTINCT eg.eg_id), 'f', 'a' ) a, MAX(ep_id) epmax FROM eg JOIN ep USING (eg_id) GROUP BY en_id HAVING epmax>=1 AND a;
+-------------+---------+
| a | epmax |
+-------------+---------+
| 1008437-f-a | 3360182 |
| 1008438-f-a | 3390182 |
| 1008439-f-a | 3810002 |
+-------------+---------+
3 rows in set (0.01 sec)
Description: In the following queries CONCAT_WS() returns different strings depending on whether or not HAVING is used to filter the resultset. #1 HAVING referencing a: duplication of argument 3 and 4 in row 2 #2 HAVING not referencing a: expected result #3 HAVING referencing a AND DISTINCT in GROUP_CONCAT: expected result #1 mysql> SELECT CONCAT_WS('-', GROUP_CONCAT(eg.eg_id), 'f', 'a' ) a, MAX(ep_id) epmax FROM eg JOIN ep USING (eg_id) GROUP BY en_id HAVING epmax>=1 AND a; +-------------------------------------+---------+ | a | epmax | +-------------------------------------+---------+ | 1008437,1008437,1008437,1008437-f-a | 3360182 | | 1008438,1008438-f-a-f-a | 3390182 | | 1008439,1008439,1008439,1008439-f-a | 3810002 | +-------------------------------------+---------+ 3 rows in set (0.00 sec) #2 mysql> SELECT CONCAT_WS('-', GROUP_CONCAT(eg.eg_id), 'f', 'a' ) a, MAX(ep_id) epmax FROM eg JOIN ep USING (eg_id) GROUP BY en_id HAVING epmax>=1; +-------------------------------------+---------+ | a | epmax | +-------------------------------------+---------+ | 1008437,1008437,1008437,1008437-f-a | 3360182 | | 1008438,1008438-f-a | 3390182 | | 1008439,1008439,1008439,1008439-f-a | 3810002 | +-------------------------------------+---------+ 3 rows in set (0.00 sec) #3 mysql> SELECT CONCAT_WS('-', GROUP_CONCAT(DISTINCT eg.eg_id), 'f', 'a' ) a, MAX(ep_id) epmax FROM eg JOIN ep USING (eg_id) GROUP BY en_id HAVING epmax>=1 AND a; +-------------+---------+ | a | epmax | +-------------+---------+ | 1008437-f-a | 3360182 | | 1008438-f-a | 3390182 | | 1008439-f-a | 3810002 | +-------------+---------+ 3 rows in set (0.00 sec) How to repeat: CREATE TABLE `eg` ( `en_id` int(10) unsigned NOT NULL DEFAULT '0', `eg_id` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`eg_id`), KEY `en_id` (`en_id`) ) ENGINE=InnoDB; CREATE TABLE `ep` ( `eg_id` int(10) unsigned NOT NULL, `ep_id` int(10) unsigned NOT NULL, PRIMARY KEY (`eg_id`,`ep_id`) ) ENGINE=InnoDB; INSERT INTO eg (en_id, eg_id) VALUES (2585,1008444); INSERT INTO eg (en_id, eg_id) VALUES (10598,1008437); INSERT INTO eg (en_id, eg_id) VALUES (26241,1008446); INSERT INTO eg (en_id, eg_id) VALUES (31698,1008440); INSERT INTO eg (en_id, eg_id) VALUES (35407,1008441); INSERT INTO eg (en_id, eg_id) VALUES (51867,1008443); INSERT INTO eg (en_id, eg_id) VALUES (53590,1008438); INSERT INTO eg (en_id, eg_id) VALUES (53594,1008439); INSERT INTO eg (en_id, eg_id) VALUES (54224,1008442); INSERT INTO eg (en_id, eg_id) VALUES (99351,1008445); INSERT INTO ep (ep_id, eg_id) VALUES (220043,1008437); INSERT INTO ep (ep_id, eg_id) VALUES (3360013,1008437); INSERT INTO ep (ep_id, eg_id) VALUES (3360014,1008437); INSERT INTO ep (ep_id, eg_id) VALUES (3360182,1008437); INSERT INTO ep (ep_id, eg_id) VALUES (3390181,1008438); INSERT INTO ep (ep_id, eg_id) VALUES (3390182,1008438); INSERT INTO ep (ep_id, eg_id) VALUES (3390179,1008439); INSERT INTO ep (ep_id, eg_id) VALUES (3390180,1008439); INSERT INTO ep (ep_id, eg_id) VALUES (3810001,1008439); INSERT INTO ep (ep_id, eg_id) VALUES (3810002,1008439);