Bug #91924 GROUP_CONCAT randomly gets inverted results
Submitted: 7 Aug 2018 21:18 Modified: 7 Aug 2018 22:27
Reporter: Daniel Grunblatt Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:10.1.31-MariaDB OS:CentOS
Assigned to: CPU Architecture:Any
Tags: group_concat

[7 Aug 2018 21:18] Daniel Grunblatt
Description:
I'm getting wrong results while querying using group_concat. Results are randomly inverted.

How to repeat:

CREATE TABLE `subject` (
  `subject_id` int(6) NOT NULL,
  `name` varchar(50) NOT NULL,
  `course_id` int(6) NOT NULL,
  `teacher_id` int(6) NOT NULL,
  `load_type` varchar(20) NOT NULL,
  `hours` int(11) NOT NULL,
  `area_id` int(2) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `number_exam` int(6) NOT NULL,
  `parent` int(5) NOT NULL,
  `print_visible` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `subject` (`subject_id`, `name`, `course_id`, `teacher_id`, `load_type`, `hours`, `area_id`, `active`, `number_exam`, `parent`, `print_visible`) VALUES
(80, 'Lengua y Literatura', 8, 0, '1', 4, 2, 0, 4, 0, 1),
(81, 'Matemática', 8, 0, '1', 4, 2, 0, 4, 0, 1),
(82, 'Ciencias Sociales', 8, 0, '1', 4, 2, 0, 4, 0, 1),
(83, 'Ciencias Naturales', 8, 0, '1', 4, 2, 0, 4, 0, 1),
(84, 'Italiano', 8, 0, '2', 5, 2, 0, 4, 0, 1),
(85, 'Inglés', 8, 0, '2', 4, 2, 0, 4, 0, 1),
(86, 'Expresion Plástica', 8, 0, '2', 3, 2, 0, 2, 0, 1),
(87, 'Música', 8, 0, '2', 2, 2, 0, 2, 0, 1),
(88, 'Informática', 8, 0, '2', 3, 2, 0, 2, 0, 1),
(89, 'Educación Física', 8, 0, '2', 2, 2, 0, 2, 0, 1);

CREATE TABLE `evaluations_mark` (
  `mark_id` int(13) NOT NULL,
  `note` varchar(225) NOT NULL,
  `comments` text NOT NULL,
  `subject_id` int(13) NOT NULL,
  `course_id` int(13) NOT NULL,
  `user_id` int(13) NOT NULL,
  `evaluation_id` int(13) NOT NULL,
  `number_exam` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `evaluations_mark` (`mark_id`, `note`, `comments`, `subject_id`, `course_id`, `user_id`, `evaluation_id`, `number_exam`) VALUES
(125, 'SA', '', 80, 8, 28265, 0, 1),
(147, 'MSA', '', 82, 8, 28265, 0, 1),
(169, 'MSA', '', 83, 8, 28265, 0, 1),
(191, 'SA', '', 81, 8, 28265, 0, 1),
(1078, 'SA', '', 85, 8, 28265, 0, 1),
(1430, 'MSA', '', 84, 8, 28265, 0, 1),
(2634, 'MSA', '', 89, 8, 28265, 0, 1),
(3279, 'MSA', '', 83, 8, 28265, 0, 2),
(3510, 'MSA', '', 85, 8, 28265, 0, 2),
(3646, 'MSA', '', 88, 8, 28265, 0, 1),
(3753, 'SA', '', 80, 8, 28265, 0, 2),
(3775, 'SA', '', 81, 8, 28265, 0, 2),
(3797, 'SA', '', 82, 8, 28265, 0, 2),
(4363, 'SA', '', 87, 8, 28265, 0, 1),
(4892, 'MSA', '', 84, 8, 28265, 0, 2),
(6858, 'MSA', '', 86, 8, 28265, 0, 1);

ALTER TABLE `evaluations_mark`
  ADD PRIMARY KEY (`mark_id`);

if I run this: 
SELECT subject.name as subject, GROUP_CONCAT(evaluations_mark.note order by subject.name asc) as mark 
FROM evaluations_mark 
LEFT JOIN subject ON evaluations_mark.subject_id = subject.subject_id
WHERE evaluations_mark.number_exam IN (1,2) AND evaluations_mark.user_id = 28265
AND subject.print_visible='1' 
AND subject.number_exam = '4' 
GROUP BY evaluations_mark.subject_id
ORDER by subject.name ASC

I should get this result:
subject	            mark
Ciencias Naturales  MSA,MSA
Ciencias Sociales   MSA,MSA
Inglés	            MSA,SA
Italiano	    MSA,MSA
Lengua y Literatura MSA,SA
Matemática	    SA,SA

However, I get this:
subject	            mark
Ciencias Naturales  MSA,MSA
Ciencias Sociales   SA,MSA
Inglés	            MSA,SA
Italiano	    MSA,MSA
Lengua y Literatura SA,SA
Matemática	    SA,SA
[7 Aug 2018 21:35] Daniel Grunblatt
If I run same thing in here: http://sqlfiddle.com/#!9/d8b52e/6. 
Result is correct.

If I run it under phpmyadmin/mysql, result is wrong. 

Why?
[7 Aug 2018 22:14] Miguel Solorzano
Thank you for the bug report. This bug system is for MySQL Oracle database, please file the bug on their bug system. Thanks in advance.
[7 Aug 2018 22:27] Daniel Grunblatt
I'm sorry... but where should I report a MySQL bug? is there any other MySQL around? Problem IS in MySQL. Not in sqlfiddle.com
[8 Aug 2018 12:52] Miguel Solorzano
Just to clarify my last comment. Thanks:

MySQL bugs should be reported in this system, but the bug you are encountering is not in MySQL but in the MariaDB fork that is not supported by us.