| Bug #68897 | Incorrect query result (after server upgrade ) | ||
|---|---|---|---|
| Submitted: | 9 Apr 2013 7:51 | Modified: | 15 Jul 2013 18:19 |
| Reporter: | Helmut Lange | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.6.10 | OS: | Windows (XP SP3) |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
| Tags: | group_concat, query result | ||
[9 Apr 2013 7:51]
Helmut Lange
[9 Apr 2013 8:34]
MySQL Verification Team
Hello Helmut, Thank you for the report. Verified as described. Thanks, Umesh
[9 Apr 2013 8:35]
MySQL Verification Team
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.68-community |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> CREATE TABLE `features_test` (
-> `param` int(11) NOT NULL,
-> `idx` int(11) NOT NULL,
-> `text` varchar(255) default NULL,
-> PRIMARY KEY (`param`,`idx`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO `features_test` (`param`, `idx`, `text`) VALUES
-> (1, 0, 'select'),
-> (1, 1, 'Kabel mit Stecker 5-polig'),
-> (1, 2, 'Kabel ohne Stecker'),
-> (2, 0, 'number'),
-> (2, 1, '22'),
-> (2, 2, '25');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT idList.id AS id, T.text AS xtext,GROUP_CONCAT(T3.text) AS optionen
-> FROM (
-> SELECT @row := @row +1 AS id
-> FROM (
-> SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 )XX1,(SELECT @row :=0)AS XX
-> ) AS idList
-> LEFT JOIN features_test AS T ON(T.param=idList.id AND T.idx=0 )
-> LEFT JOIN features_test AS T3 ON(T3.param=idList.id AND T3.idx>0 )
-> GROUP BY idList.id
-> ORDER BY id ASC;
+------+--------+----------------------------------------------+
| id | xtext | optionen |
+------+--------+----------------------------------------------+
| 1 | select | Kabel mit Stecker 5-polig,Kabel ohne Stecker |
| 2 | number | 22,25 |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
+------+--------+----------------------------------------------+
4 rows in set (0.02 sec)
mysql>
##### 5.5.30 -
mysql>
mysql>
mysql>
mysql>
mysql> CREATE TABLE `features_test` (
-> `param` int(11) NOT NULL,
-> `idx` int(11) NOT NULL,
-> `text` varchar(255) default NULL,
-> PRIMARY KEY (`param`,`idx`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> INSERT INTO `features_test` (`param`, `idx`, `text`) VALUES
-> (1, 0, 'select'),
-> (1, 1, 'Kabel mit Stecker 5-polig'),
-> (1, 2, 'Kabel ohne Stecker'),
-> (2, 0, 'number'),
-> (2, 1, '22'),
-> (2, 2, '25');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql>
mysql> SELECT idList.id AS id, T.text AS xtext,GROUP_CONCAT(T3.text) AS optionen
-> FROM (
-> SELECT @row := @row +1 AS id
-> FROM (
-> SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 )XX1,(SELECT @row :=0)AS XX
-> ) AS idList
-> LEFT JOIN features_test AS T ON(T.param=idList.id AND T.idx=0 )
-> LEFT JOIN features_test AS T3 ON(T3.param=idList.id AND T3.idx>0 )
-> GROUP BY idList.id
-> ORDER BY id ASC;
+------+--------+----------------------------------------------+
| id | xtext | optionen |
+------+--------+----------------------------------------------+
| 1 | select | Kabel mit Stecker 5-polig,Kabel ohne Stecker |
| 2 | number | 22,25 |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
+------+--------+----------------------------------------------+
4 rows in set (0.00 sec)
##### 5.6-10
mysql> CREATE TABLE `features_test` (
-> `param` int(11) NOT NULL,
-> `idx` int(11) NOT NULL,
-> `text` varchar(255) default NULL,
-> PRIMARY KEY (`param`,`idx`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.19 sec)
mysql>
mysql> INSERT INTO `features_test` (`param`, `idx`, `text`) VALUES
-> (1, 0, 'select'),
-> (1, 1, 'Kabel mit Stecker 5-polig'),
-> (1, 2, 'Kabel ohne Stecker'),
-> (2, 0, 'number'),
-> (2, 1, '22'),
-> (2, 2, '25');
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> SELECT idList.id AS id, T.text AS xtext,GROUP_CONCAT(T3.text) AS optionen
-> FROM (
-> SELECT @row := @row +1 AS id
-> FROM (
-> SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 )XX1,(SELECT @row :=0)AS XX
-> ) AS idList
-> LEFT JOIN features_test AS T ON(T.param=idList.id AND T.idx=0 )
-> LEFT JOIN features_test AS T3 ON(T3.param=idList.id AND T3.idx>0 )
-> GROUP BY idList.id
-> ORDER BY id ASC;
+------+--------+----------------------------------------------+
| id | xtext | optionen |
+------+--------+----------------------------------------------+
| 1 | select | Kabel mit Stecker 5-polig,Kabel ohne Stecker |
| 2 | NULL | 22,25 |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
+------+--------+----------------------------------------------+
4 rows in set (0.05 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.6.10-debug |
+--------------+
1 row in set (0.00 sec)
mysql>
[9 Apr 2013 9:20]
Hartmut Holzgraefe
Seems to be related to the GROUP_CONCAT() and the result order, i still get this after replacing the subquery with a simple idList table with just one integer column `id` as primary key and with values 1,2,3,4 in there When replacing the ORDER BY ASC with DESC i get the expected result though with 'number' as `xtext` result for id=2 Also when replacing GROUP_CONCAT() with another aggregate function like AVG() xtext values are correct => so very likely to be a GROUP_CONCAT() specific bug
[9 Apr 2013 9:24]
Hartmut Holzgraefe
Simplified test case with both ASC and DESC queries: CREATE TABLE `features_test` ( `param` int(11) NOT NULL, `idx` int(11) NOT NULL, `text` varchar(255) default NULL, PRIMARY KEY (`param`,`idx`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `features_test` (`param`, `idx`, `text`) VALUES (1, 0, 'select'), (1, 1, 'Kabel mit Stecker 5-polig'), (1, 2, 'Kabel ohne Stecker'), (2, 0, 'number'), (2, 1, '22'), (2, 2, '25'); CREATE TABLE `idList` ( `id` int PRIMARY KEY ); INSERT INTO idList VALUES (1),(2),(3),(4); SELECT idList.id AS id, T.text AS xtext,GROUP_CONCAT(T3.text) AS optionen FROM idList LEFT JOIN features_test AS T ON(T.param=idList.id AND T.idx=0 ) LEFT JOIN features_test AS T3 ON(T3.param=idList.id AND T3.idx>0 ) GROUP BY idList.id ORDER BY id ASC; SELECT idList.id AS id, T.text AS xtext,GROUP_CONCAT(T3.text) AS optionen FROM idList LEFT JOIN features_test AS T ON(T.param=idList.id AND T.idx=0 ) LEFT JOIN features_test AS T3 ON(T3.param=idList.id AND T3.idx>0 ) GROUP BY idList.id ORDER BY id DESC;
[12 Apr 2013 8:03]
Roy Lyseng
Proposed query without using variables:
SELECT idList.id AS id, T.text AS xtext, GROUP_CONCAT(T3.text) AS optionen
FROM (SELECT 1 AS id, UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS idList
LEFT JOIN features_test AS T ON T.param=idList.id AND T.idx=0
LEFT JOIN features_test AS T3 ON T3.param=idList.id AND T3.idx>0
GROUP BY idList.id
ORDER BY id ASC
Not sure it works for you, but using variables seems to be redundant here.
You may be able to work around the problem by extending the GROUP BY list
with the field selected from T.
It is OK, as there is an eq_ref relation between idList and T.
It will also work even with sql_mode only_full_group_by, but performance may suffer a bit:
SELECT idList.id AS id, T.text AS xtext, GROUP_CONCAT(T3.text) AS optionen
FROM (SELECT 1 AS id, UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS idList
LEFT JOIN features_test AS T ON T.param=idList.id AND T.idx=0
LEFT JOIN features_test AS T3 ON T3.param=idList.id AND T3.idx>0
GROUP BY idList.id, T.text
ORDER BY id ASC
[27 Jun 2013 8:29]
Guilhem Bichot
bug#69521 was marked as duplicate of this one.
[13 Jul 2013 8:15]
Alexander Sayadyan
The same bug in 5.6.11
[13 Jul 2013 8:46]
Tigran Petrosyan
Very serious bug. It happens only is some cases and for thous who moved to 5.6 from early versions, like us, it can bring to real problems. We have moved to 5.6 a month ago and only now noticed that strange thing happening with LEFT JOIN with GROUP BY results on huge database. As I understand we need to downgrade to 5.5 till this bug is solved. It opened 3 month ago and no progress with it till now.
[13 Jul 2013 9:55]
Tigran Petrosyan
In bug#69268 which is marked as duplicate of this, is more clearly indicates the problem on all platforms, just simple select with LEFT JOIN and GROUP BY. And the most strange thing is than you also add 'ORDER BY group_by_filed DESC' it works as it should. I can't understand how can someone use 5.6.x when such a bug exists, maybe only if never used LEFT OUTER JOINs.
[15 Jul 2013 18:19]
Paul DuBois
Noted in 5.6.13, 5.7.2 changelogs. Some LEFT JOIN queries with GROUP BY could return incorrect results.
[6 Aug 2013 8:56]
David Dernulf
Will this be fixed anytime soon? It is a huge problem for us.
[6 Aug 2013 9:16]
MySQL Verification Team
Please try 5.6.13. It is fixed.
