[MDEV-7193] Incorrect Query Result (MySQL Bug 68897) in MariaDB 10.0.14 Created: 2014-11-24  Updated: 2015-02-17  Resolved: 2015-02-17

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.14
Fix Version/s: 10.0.16

Type: Bug Priority: Critical
Reporter: Evi Vanoost Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: upstream-fixed
Environment:

Debian Squeeze


Issue Links:
Duplicate
duplicates MDEV-5719 Wrong result with GROUP BY and LEFT O... Closed

 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.



 Comments   
Comment by Elena Stepanova [ 2014-11-24 ]

Thanks for the report.
It's probably the same problem as in MDEV-5719, assigning to psergey to make sure of that when he has a fix for MDEV-5719.

Comment by Sergei Petrunia [ 2015-02-17 ]

Indeed, this bug is fixed by the fix for MDEV-5719. I'll add a testcase.

Comment by Sergei Petrunia [ 2015-02-17 ]

Fixed by fix for MDEV-5719

Generated at Thu Feb 08 07:17:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.