| Bug #71206 | GROUP_CONCAT changes GROUP BY behavior | ||
|---|---|---|---|
| Submitted: | 22 Dec 2013 17:13 | Modified: | 23 Dec 2013 8:45 |
| Reporter: | Dmitry Kuskov | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.5.32-0ubuntu0.12.04.1-log, 5.6.15, 5.1.72, 5.5.35, 5.7.3 | OS: | Linux (ubuntu 12.04) |
| Assigned to: | CPU Architecture: | Any | |
[22 Dec 2013 17:14]
Dmitry Kuskov
CREATE TABLE IF NOT EXISTS `test` ( `i_instanceid` int(10) unsigned NOT NULL, `i_descriptions` text NOT NULL, PRIMARY KEY (`i_instanceid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[23 Dec 2013 8:34]
Valeriy Kravchuk
This is also repeatable with latest and greatest 5.6.15:
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.15-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE IF NOT EXISTS `test` (
-> `i_instanceid` int(10) unsigned NOT NULL,
-> `i_descriptions` text NOT NULL,
-> PRIMARY KEY (`i_instanceid`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.91 sec)
mysql> INSERT INTO `test` (`i_instanceid`, `i_descriptions`) VALUES
-> (1, '[{"type":"html","value":"\\u0425\\u043e\\u043b\\u043e\\u0434\\u0440\
\u044b\\u0436\\u0435\\u0441\\u0442\\u0432\\u043e 2013","color":"99ccff"},{"type"
:"html","value":"<div style=\\"white-space: nowrap; margin: 10px\\"><div style=\
\"white-space: nowrap; padding: 3px;\\"><div style=\\"width: 60px; height: 32px;
vertical-align: top; display: inline-block;\\"><div style=\\"width: 48px; heigh
t: 32px; vertical-align: top; display: inline-block; border: 2px solid rgb(255,
255, 255);>\\"><div style=\\"position: absolute; width: 48px; height: 32px; vert
ical-align: top; display: inline-block; background-size: contain; background-ima
ge: url(http:\\/\\/cdn.dota2.com\\/apps\\/570\\/icons\\/econ\\/sockets\\/gem_sta
t.ea886ecaac62d6eb..."><\\/div><div style=\\"position: absolute; filter:alpha(op
acity=90); -moz-opacity:0.9; -khtml-opacity: 0.9; opacity: 0.9; width: 48px; hei
ght: 32px; vertical-align: top; display: inline-block; background-size: contain;
background-image: url(http:\\/\\/cdn.dota2.com\\/apps\\/570\\/)\\"><\\/div><\\/
div><\\/div><div style=\\"vertical-align: top; display: inline-block; margin-lef
t: 12px padding: 2px\\"><span style=\\"font-size: 18px; color: rgb(255, 255, 255
)\\">\\u0423\\u043d\\u0438\\u0447\\u0442\\u043e\\u0436\\u0435\\u043d\\u043e \\u0
43a\\u0430\\u0437\\u0430\\u0440\\u043c: 0<\\/span><br><span style=\\"font-size:
12px\\">\\u0420\\u0443\\u043d\\u0430<\\/span><\\/div><\\/div><\\/div>"}]'),
-> (2, '[{"type":"html","value":"\\u0425\\u043e\\u043b\\u043e\\u0434\\u0440\
\u044b\\u0436\\u0435\\u0441\\u0442\\u0432\\u043e 2013","color":"99ccff"},{"type"
:"html","value":"<div style=\\"white-space: nowrap; margin: 10px\\"><div style=\
\"white-space: nowrap; padding: 3px;\\"><div style=\\"width: 60px; height: 32px;
vertical-align: top; display: inline-block;\\"><div style=\\"width: 48px; heigh
t: 32px; vertical-align: top; display: inline-block; border: 2px solid rgb(255,
255, 255);>\\"><div style=\\"position: absolute; width: 48px; height: 32px; vert
ical-align: top; display: inline-block; background-size: contain; background-ima
ge: url(http:\\/\\/cdn.dota2.com\\/apps\\/570\\/icons\\/econ\\/sockets\\/gem_sta
t.ea886ecaac62d6eb..."><\\/div><div style=\\"position: absolute; filter:alpha(op
acity=90); -moz-opacity:0.9; -khtml-opacity: 0.9; opacity: 0.9; width: 48px; hei
ght: 32px; vertical-align: top; display: inline-block; background-size: contain;
background-image: url(http:\\/\\/cdn.dota2.com\\/apps\\/570\\/)\\"><\\/div><\\/
div><\\/div><div style=\\"vertical-align: top; display: inline-block; margin-lef
t: 12px padding: 2px\\"><span style=\\"font-size: 18px; color: rgb(255, 255, 255
)\\">\\u0423\\u0431\\u0438\\u0439\\u0441\\u0442\\u0432: 0<\\/span><br><span styl
e=\\"font-size: 12px\\">\\u0420\\u0443\\u043d\\u0430<\\/span><\\/div><\\/div><\\
/div>"}]');
Query OK, 2 rows affected (0.31 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM `test` GROUP BY `i_descriptions`;
+----------+
| COUNT(*) |
+----------+
| 1 |
| 1 |
+----------+
2 rows in set (0.42 sec)
mysql> SELECT GROUP_CONCAT(`i_instanceid`), COUNT(*) FROM `test` GROUP BY `i_des
criptions`;
+------------------------------+----------+
| GROUP_CONCAT(`i_instanceid`) | COUNT(*) |
+------------------------------+----------+
| 1,2 | 2 |
+------------------------------+----------+
1 row in set (0.03 sec)
It's not clear why now we have one group instead of two (as the first query correctly shows).
[23 Dec 2013 8:45]
MySQL Verification Team
Hello Dmitry, Thank you for the bug report and test case. Verified as described. Thanks, Umesh
[24 Dec 2013 23:44]
MySQL Verification Team
#testcase:
drop table if exists t1;
create table if not exists `t1`(`a` int,`b` text)engine=innodb default charset=utf8;
insert into t1(a,b) values (1,repeat('a',1025)),(2,repeat('a',1024));
select count(*) from `t1` group by `b`;
select group_concat(`a`), count(*) from `t1` group by `b`;
Description: Queries SELECT GROUP_CONCAT(`i_instanceid`), COUNT(*) FROM `test` GROUP BY `i_descriptions` and SELECT COUNT(*) FROM `test` GROUP BY `i_descriptions` produces different row count on certain data How to repeat: INSERT INTO `test` (`i_instanceid`, `i_descriptions`) VALUES (1, '[{"type":"html","value":"\\u0425\\u043e\\u043b\\u043e\\u0434\\u0440\\u044b\\u0436\\u0435\\u0441\\u0442\\u0432\\u043e 2013","color":"99ccff"},{"type":"html","value":"<div style=\\"white-space: nowrap; margin: 10px\\"><div style=\\"white-space: nowrap; padding: 3px;\\"><div style=\\"width: 60px; height: 32px; vertical-align: top; display: inline-block;\\"><div style=\\"width: 48px; height: 32px; vertical-align: top; display: inline-block; border: 2px solid rgb(255, 255, 255);>\\"><div style=\\"position: absolute; width: 48px; height: 32px; vertical-align: top; display: inline-block; background-size: contain; background-image: url(http:\\/\\/cdn.dota2.com\\/apps\\/570\\/icons\\/econ\\/sockets\\/gem_stat.ea886ecaac62d6eb..."><\\/div><div style=\\"position: absolute; filter:alpha(opacity=90); -moz-opacity:0.9; -khtml-opacity: 0.9; opacity: 0.9; width: 48px; height: 32px; vertical-align: top; display: inline-block; background-size: contain; background-image: url(http:\\/\\/cdn.dota2.com\\/apps\\/570\\/)\\"><\\/div><\\/div><\\/div><div style=\\"vertical-align: top; display: inline-block; margin-left: 12px padding: 2px\\"><span style=\\"font-size: 18px; color: rgb(255, 255, 255)\\">\\u0423\\u043d\\u0438\\u0447\\u0442\\u043e\\u0436\\u0435\\u043d\\u043e \\u043a\\u0430\\u0437\\u0430\\u0440\\u043c: 0<\\/span><br><span style=\\"font-size: 12px\\">\\u0420\\u0443\\u043d\\u0430<\\/span><\\/div><\\/div><\\/div>"}]'), (2, '[{"type":"html","value":"\\u0425\\u043e\\u043b\\u043e\\u0434\\u0440\\u044b\\u0436\\u0435\\u0441\\u0442\\u0432\\u043e 2013","color":"99ccff"},{"type":"html","value":"<div style=\\"white-space: nowrap; margin: 10px\\"><div style=\\"white-space: nowrap; padding: 3px;\\"><div style=\\"width: 60px; height: 32px; vertical-align: top; display: inline-block;\\"><div style=\\"width: 48px; height: 32px; vertical-align: top; display: inline-block; border: 2px solid rgb(255, 255, 255);>\\"><div style=\\"position: absolute; width: 48px; height: 32px; vertical-align: top; display: inline-block; background-size: contain; background-image: url(http:\\/\\/cdn.dota2.com\\/apps\\/570\\/icons\\/econ\\/sockets\\/gem_stat.ea886ecaac62d6eb..."><\\/div><div style=\\"position: absolute; filter:alpha(opacity=90); -moz-opacity:0.9; -khtml-opacity: 0.9; opacity: 0.9; width: 48px; height: 32px; vertical-align: top; display: inline-block; background-size: contain; background-image: url(http:\\/\\/cdn.dota2.com\\/apps\\/570\\/)\\"><\\/div><\\/div><\\/div><div style=\\"vertical-align: top; display: inline-block; margin-left: 12px padding: 2px\\"><span style=\\"font-size: 18px; color: rgb(255, 255, 255)\\">\\u0423\\u0431\\u0438\\u0439\\u0441\\u0442\\u0432: 0<\\/span><br><span style=\\"font-size: 12px\\">\\u0420\\u0443\\u043d\\u0430<\\/span><\\/div><\\/div><\\/div>"}]');