Details
Critical
Description
I was having issues where a certain query would not return the expected results. So I dug and found this: https://bugs.mysql.com/bug.php?id=68897
I can replicate the issue on MariaDB 10.0.14. Copy-pasta of a test case:
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;
I would expect the DESC and ASC to return similar results, instead one returns a NULL value.
Gliffy Diagrams
Attachments
Issue Links
- duplicates
-
MDEV-5719 Wrong result with GROUP BY and LEFT OUTER JOIN
-
- Closed
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Indeed, this bug is fixed by the fix for MDEV-5719. I'll add a testcase.
Fixed by fix for MDEV-5719
Thanks for the report.
It's probably the same problem as in
MDEV-5719, assigning to Sergei Petrunia to make sure of that when he has a fix forMDEV-5719.