Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16916

wrong results using group_concat

    XMLWordPrintable

    Details

      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?

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            dgrunblatt Daniel Grunblatt
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: