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
