Неправильный результат при выборе счетчика с помощью функции order by и math в MySQL 5.7.17

У меня есть простая таблица с именем temp с тремя столбцами и следующими данными:

# c1  c2  v
  1   1  'a'
  1   2  'b'
  1   1  'b'
  1   2  'a'

Я запускаю запрос:

SELECT
    t01.c1,
    t01.c2,
    COUNT(DISTINCT v) AS cnt
FROM
(
    SELECT 
        FLOOR(c1) AS c1,
        FLOOR(c2) AS c2,
        v
    FROM 
        temp
)   AS t01
GROUP BY
    t01.c1,
    t01.c2
ORDER BY
    cnt DESC

Это должно возвращать следующее:

# c1, c2, cnt
   1,  1,  2
   1,  2,  2

Но на самом деле он возвращает это:

# c1, c2, cnt
   1,  1,  1
   1,  2,  1
   1,  1,  1
   1,  2,  1

Это странно, когда он содержит команду floor по группе подсчитывать разные, а результат неверно - он не группирует данные.

Является ли это ошибкой MySQL в версии 5.7.17?

Ответы

Ответ 1

Да, это ошибка, связанная с объединением производной таблицы (подзапрос в FROM) во внешний запрос. Пожалуйста, сообщите об ошибке в bugs.mysql.com.

Обходные:

  • Установить optimizer_switch='derived_merge=off';
  • Добавьте предложение LIMIT в подзапрос. Это будет препятствовать его объединению во внешний запрос.
  • Вручную объединить подзапрос с внешним запросом:
SELECT 
  FLOOR(c1) AS g1, 
  FLOOR(c2) AS g2, 
  COUNT(DISTINCT v) AS cnt   
FROM temp
GROUP BY g1, g2
ORDER BY cnt DESC;

Ответ 2

Он просто попробовал это в MySql 5.6 на sqlfiddle, и он просто работает. И дальше dbfiddle в MySql 5.7 это не так.

Итак, возможно, что-то с MySql 5.7, как вы уже подумали.

В соответствии с Справочное руководство по MySql в разделе (Функции, устаревшие в MySQL 5.7)

GROUP BY неявно сортирует по умолчанию (то есть, в отсутствие ASC или DESC), но полагаясь на неявную сортировку GROUP BY MySQL 5.7 устарел. Для достижения определенного порядка сортировки сгруппированных результатов, предпочтительнее использовать. Чтобы создать заданный порядок сортировки, используйте явные обозначения ASC или DESC для столбцов GROUP BY или Предложение ORDER BY. Сортировка GROUP BY - это расширение MySQL, которое может меняться в будущем выпуске; например, чтобы сделать возможным оптимизатор для упорядочения группировок любым способом, который он считает наиболее эффективно и избегать служебных данных сортировки.

Обновление

Я попробовал что-то другое для вас, которое работает:

SELECT
    t01.c1,
    t01.c2,
    count(v) AS cnt
FROM
(
    SELECT 
        DISTINCT v as v,
        FLOOR(c1) AS c1,
        FLOOR(c2) AS c2

    FROM 
        temp
)   AS t01
GROUP BY
    t01.c1,
    t01.c2
ORDER BY
    cnt DESC

Это мой оператор create:

CREATE TABLE temp
    (`c1` int, `c2` int, `v` varchar(5))
;

INSERT INTO temp
    (`c1`, `c2`, `v`)
VALUES
    (1, 1, '''a'''),
    (1, 1, '''a'''),
    (1, 2, '''b'''),
    (1, 1, '''b'''),
    (1, 1, '''c'''),
    (1, 2, '''a''')
;

результат: https://www.db-fiddle.com/f/7zBFKzd3pE7ymrD5LTcmkz/1