Нумерация GROUP_CONCAT с несколькими столбцами группируется по

Вам следует избегать использования таких пользовательских переменных в производственном коде.

В руководстве для MySQL 5.6 говорится:

Как правило, кроме операторов SET, вы никогда не должны присваивать значение пользовательской переменной и читать значение в том же операторе.

И даже в документации для 8.0 говорится:

Порядок оценки выражений, включающих пользовательские переменные, не определен. Например, нет гарантии, что SELECT @a, @a:=@a+1 сначала оценит @a, а затем выполнит присвоение.

В будущих выпусках это может больше не работать:

Предыдущие выпуски MySQL позволяли присваивать значение пользовательской переменной в операторах, отличных от SET. Эта функция поддерживается в MySQL 8.0 для обратной совместимости, но может быть удалена в будущих версиях MySQL.

Итак, вот решение без пользовательских переменных:

SELECT 
r.submission_id,
rd.category_id,
GROUP_CONCAT(CONCAT('Reviewer ', (SELECT COUNT(*) + 1 
                                  FROM review 
                                  JOIN review_detail ON review.id = review_detail.review_id 
                                  WHERE r.submission_id = review.submission_id 
                                  AND review_detail.category_id = rd.category_id 
                                  AND review_detail.id < rd.id
                                 ), ': ', rating, ' of review ', review_id) ORDER BY rating SEPARATOR ' / ') AS shorter_column_name
FROM 
review r 
JOIN review_detail rd ON rd.review_id = r.id
GROUP BY r.submission_id, rd.category_id;

который возвращается

+---------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| submission_id | category_id | shorter_column_name                                                                                                                           |
+---------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|             1 |           1 | Reviewer 1:  submission 1.1 cat 1 of review 1 / Reviewer 2:  submission 1.2 cat 1 of review 2 / Reviewer 3:  submission 1.3 cat 1 of review 5 |
|             1 |           2 | Reviewer 1:  submission 1.1 cat 2 of review 1 / Reviewer 2:  submission 1.2 cat 2 of review 2 / Reviewer 3:  submission 1.3 cat 2 of review 5 |
|             2 |           1 | Reviewer 1:  submission 2.1 cat 1 of review 3 / Reviewer 2:  submission 2.2 cat 1 of review 7                                                 |
|             2 |           2 | Reviewer 1:  submission 2.1 cat 2 of review 3 / Reviewer 2:  submission 2.2 cat 2 of review 7                                                 |
|             3 |           1 | Reviewer 1:  submission 3.1 cat 1 of review 4 / Reviewer 2:  submission 3.2 cat 1 of review 6 / Reviewer 3:  submission 3.3 cat 1 of review 8 |
|             3 |           2 | Reviewer 1:  submission 3.1 cat 1 of review 4 / Reviewer 2:  submission 3.2 cat 2 of review 6 / Reviewer 3:  submission 3.3 cat 2 of review 6 |
+---------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+

чтобы исправить ваш запрос.

Основная проблема заключается в том, что таблицы по своей природе не отсортированы, поэтому оптимизатор MySQL удаляет файл ORDER BY.

В MySQL достаточно поместить все таблицы в предложение FROM, чтобы сделать подзапрос с порядком, mysql сохранит его.

В Мариадбе этого недостаточно. Вы также добавили LIMIT 18446744073709551615, чтобы оптимизатор сохранил его.

Схема (MySQL v5.5)

Запрос №1

SELECT
    submission_id,
    category_id,
    @i,
    @grp,
    CONCAT_WS("-", submission_id, category_id) AS catgroup,
    GROUP_CONCAT(
        CONCAT(
            'Reviewer ',
            @i := IF(
                @grp = CONCAT_WS("-", submission_id, category_id),
                @i := @i + 1,
                IF(
                    @grp := CONCAT_WS("-", submission_id, category_id),
                    1,
                    1
                )
            ),
            ': ',
            rating,
            ' of review ',  review_id
        )
    ORDER BY review_id, submission_id, category_id 
    SEPARATOR ' / '
    ) concatText
FROM
    (SELECT review_id, submission_id, category_id,`rating` FROM review_detail
LEFT JOIN review ON review.id = review_detail.review_id
     ORDER BY review_id, submission_id, category_id ) t1,
    (
SELECT
    @i := 0,
    @grp := ''
) init


GROUP BY
    submission_id,
    category_id;

Результат

| submission_id | category_id | @i  | @grp | catgroup | concatText                                                                                                                                    |
| ------------- | ----------- | --- | ---- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------- |
| 1             | 1           | 0   |      | 1-1      | Reviewer 3:  submission 1.1 cat 1 of review 1 / Reviewer 2:  submission 1.2 cat 1 of review 2 / Reviewer 1:  submission 1.3 cat 1 of review 5 |
| 1             | 2           | 3   | 1-1  | 1-2      | Reviewer 3:  submission 1.1 cat 2 of review 1 / Reviewer 2:  submission 1.2 cat 2 of review 2 / Reviewer 1:  submission 1.3 cat 2 of review 5 |
| 2             | 1           | 3   | 1-2  | 2-1      | Reviewer 1:  submission 2.1 cat 1 of review 3 / Reviewer 2:  submission 2.2 cat 1 of review 7                                                 |
| 2             | 2           | 2   | 2-1  | 2-2      | Reviewer 2:  submission 2.1 cat 2 of review 3 / Reviewer 1:  submission 2.2 cat 2 of review 7                                                 |
| 3             | 1           | 2   | 2-2  | 3-1      | Reviewer 2:  submission 3.1 cat 1 of review 4 / Reviewer 1:  submission 3.2 cat 1 of review 6 / Reviewer 3:  submission 3.3 cat 1 of review 8 |
| 3             | 2           | 3   | 3-1  | 3-2      | Reviewer 3:  submission 3.1 cat 1 of review 4 / Reviewer 2:  submission 3.3 cat 2 of review 6 / Reviewer 1:  submission 3.2 cat 2 of review 6 |

Просмотр в DB Fiddle

Вам нужно использовать двухступенчатый подзапрос для сортировки по номеру рецензента.

SET @i := 0;
SET @grp := '';
SELECT
    submission_id,
    category_id,
    GROUP_CONCAT(
      CONCAT(
        'Reviewer ',
        i,
        ': ',
        rating,
        ' of review ',  review_id
      )
      ORDER BY i
      SEPARATOR ' / '
    ) concatText
FROM
-- second, add numbering
(
  SELECT *,
    @i := IF(
      @grp = @grp := CONCAT_WS('-',submission_id,category_id),
      @i + 1, 1) i
  FROM
  -- first, sort for numbering
  (
    SELECT
        review_id,
        submission_id,
        category_id,
        rating
    FROM review_detail LEFT JOIN review ON review.id = review_detail.review_id
    ORDER BY
        submission_id,
        category_id,
        review_id
  ) t1
) t2
GROUP BY
    submission_id,
    category_id
;

скрипт базы данных

Для полноты картины я также добавляю решение, как это можно сделать в Mysql 8.0.

Он работает как с COUNT (*)

with base as (
    
  SELECT
    review_id,
    submission_id,
    category_id,
    rating,
    count(*) over (partition by submission_id,category_id  order by review_id) num
  
    FROM review_detail LEFT JOIN review ON review.id = review_detail.review_id
    ORDER BY
        submission_id,
        category_id,
        review_id
)
select   
  submission_id,
         category_id,
         group_concat(concat('Reviewer', num, ': ', rating, ' of review ',  review_id ) separator ', ') concattext
from     base
group by 
submission_id,
category_id
;

ИЛИ ROW_NUMBER ()

with base as (
        SELECT
            review_id,
            submission_id,
            category_id,
            rating,
            ROW_NUMBER() over (partition by submission_id,category_id  order by review_id) num
        FROM review_detail 
        LEFT JOIN review ON review.id = review_detail.review_id
        ORDER BY
            submission_id,
            category_id,
            review_id
    )
    SELECT   
        submission_id,
        category_id,
        group_concat(concat('Reviewer', num, ': ', rating, ' of review ',  review_id  ) separator ', ') concattext
    from base
    group by 
        submission_id,
        category_id
;

DB Fiddle