Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.14
-
Debian Squeeze
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.
Attachments
Issue Links
- duplicates
-
MDEV-5719 Wrong result with GROUP BY and LEFT OUTER JOIN
- Closed
- links to