MySQL ведет себя по-разному на 3 серверах одной и той же версии

#mysql

Вопрос:

У меня этот запрос выполняется на двух разных скриптовых сайтах, оба настроены на использование MySQL 5.6:

 SELECT name, rank, position FROM(
  SELECT name, position,
  @rank:= IF(@prev = name, @rank   1, 1) AS rank,
  @prev:= name

  FROM (SELECT * FROM drivers 
    LEFT JOIN results on drivers.id = results.driver_id 
    JOIN (SELECT @rank := 1) AS init
    ORDER BY name, results.position ASC) AS temp

) AS derived WHERE rank <= 3 ORDER BY name, rank

Предполагается, что это дает 3 верхние позиции каждого драйвера. Запрос работает на скрипте # 1, но не на скрипте # 2 или производственном сервере, хотя все три из них работают на MySQL 5.6.

Есть ли настройка, которую я упускаю?

Скрипка # 1 — работает

Скрипка # 2 — не работает

1. В Oracle вычислять что-либо вручную в select подобным образом было бы очень опасно, поскольку результаты не определены и могут зависеть от того, как оптимизатор решит выполнить запрос. Я бы предположил, что то же самое относится и к MySQL, и что разница, которую вы получаете, заключается в том, что системы генерируют разные планы? Или даже то, что они используют одни и те же планы, но сталкиваются с данными в другом порядке. Вы не можете использовать встроенную функцию RANK, или это было добавлено позже?

2. @ewramner Похоже, что проблема не была инициализирована prev должным образом. К сожалению, RANK был добавлен в 8.0

Ответ №1:

Вы можете использовать следующее решение:

 SELECT name, rank, position FROM (
    SELECT name, position,
        @rank:= IF(@prev = name, @rank   1, 1) AS rank,
        @prev:= name
    FROM (
        SELECT * 
        FROM drivers LEFT JOIN results ON drivers.id = results.driver_id 
            JOIN (SELECT @rank := 1) AS init_rank
            JOIN (SELECT @prev := '') AS init_prev
        ORDER BY name, results.position ASC
    ) AS temp
) AS derived 
WHERE rank <= 3 
ORDER BY name, rank

Я также добавил инициализацию для @prev переменной на JOIN .

разные демоверсии:

Начиная с MySQL 8.0, вы можете использовать встроенную RANK функцию window. Таким образом, вам не нужны переменные @prev or @rank :

 SELECT name, `rank`, position FROM (
    SELECT name, position, RANK() OVER (PARTITION BY name ORDER BY name, position) AS `rank`
    FROM drivers LEFT JOIN results ON drivers.id = results.driver_id
) AS derived 
WHERE `rank` <= 3 
ORDER BY name, `rank`

демо на dbfiddle.uk

1. Это отлично, спасибо. Не могли бы вы объяснить, почему это работает? Я бы ожидал, что @prev он будет «инициализирован» после первой итерации, когда name будет назначен, что приведет true к последующим итерациям в IF условии. Тем не менее, кажется, что это всегда false .