Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
None
-
cpanel
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
|
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?