Почему MySQL разрешает запросы "group by" без агрегатных функций?

Сюрприз - это абсолютно корректный запрос в MySQL:

select X, Y from someTable group by X

Если вы пробовали этот запрос в Oracle или SQL Server, вы получите естественное сообщение об ошибке:

Column 'Y' is invalid in the select list because it is not contained in 
either an aggregate function or the GROUP BY clause.

Итак, как MySQL определяет, какой Y будет отображаться для каждого X? Он просто выбирает один. Из того, что я могу сказать, он просто выбирает первый, который он находит. Обоснование заключается в том, что если Y не является ни агрегатной функцией, ни в предложении group by, то указание "выбрать Y" в вашем запросе не имеет смысла начинать. Поэтому я, как механизм базы данных, вернулю все, что захочу, и вам понравится.

Theres даже параметр конфигурации MySQL, чтобы отключить эту "слабость". http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

В этой статье упоминается также, как MySQL был подвергнут критике за то, что ANSI-SQL несовместим в этом отношении. http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html

Мой вопрос: Почему был разработан MySQL таким образом? Каково было их обоснование для разрыва с ANSI-SQL?

Ответы

Ответ 1

Я считаю, что он должен был обрабатывать случай, когда группировка по одному полю подразумевала бы, что другие поля также группируются:

SELECT user.id, user.name, COUNT(post.*) AS posts 
FROM user 
  LEFT OUTER JOIN post ON post.owner_id=user.id 
GROUP BY user.id

В этом случае имя пользователя всегда будет уникальным для user.id, поэтому есть удобство в том, чтобы не требовать имя пользователя в предложении GROUP BY (хотя, как вы говорите, есть определенные возможности для проблем)

Ответ 2

В соответствии с эта страница (онлайн-руководство 5.0), оно обеспечивает лучшую производительность и удобство для пользователя.

Ответ 3

К сожалению, почти все разновидности SQL имеют ситуации, когда они нарушают ANSI и имеют непредсказуемые результаты.

Звучит так, как будто они предполагали, что к нему относятся как к функции "FIRST (Y)", которую имеют многие другие системы.

Скорее всего, эта конструкция - это то, что команда MySQL жалеет, но не хочет останавливаться на поддержке из-за количества приложений, которые могли бы сломаться.

Rob

Ответ 4

MySQL рассматривает это один столбец DISTINCT, когда вы используете GROUP BY без агрегатной функции. Используя другие варианты, вы либо имеете весь результат, либо должны быть разными, либо использовать подзапросы и т.д. Вопрос заключается в том, действительно ли результаты предсказуемы.

Кроме того, хорошая информация находится в этой теме.

Ответ 5

Из того, что я прочитал на странице справки mysql, говорится: "Вы можете использовать эту функцию для повышения производительности, избегая ненужной сортировки и группировки столбцов. Однако это полезно прежде всего, когда все значения в каждом неагрегированном столбце, не названном в GROUP BY, одинаковы для каждой группы".

Я предлагаю вам прочитать эту страницу (ссылка на справочное руководство по mysql): http://dev.mysql.com/doc/refman/5.5/en//group-by-extensions.html

Ответ 6

На самом деле это очень полезный инструмент, когда все остальные поля не должны быть в агрегатной функции, когда вы группируете поле. Вы можете манипулировать результатом, который будет возвращен, просто заказывая его сначала, а затем группируя его после. например, если бы я хотел получить информацию для входа в систему, и мне захотелось увидеть последний раз, когда пользователь вошел в систему, я бы сделал это.

Таблица

USER
user_id | name

USER_LOGIN_HISTORY 
user_id | date_logged_in

USER_LOGIN_HISTORY имеет несколько строк для одного пользователя, поэтому, если я присоединяюсь к пользователям, он будет возвращать много строк. поскольку меня интересует только последняя запись, я бы сделал это

select 
  user_id,
  name,
  date_logged_in

from(

  select 
    u.user_id, 
    u.name, 
    ulh.date_logged_in

  from users as u

    join user_login_history as ulh
      on u.user_id = ulh.user_id

  where u.user_id = 1234

  order by ulh.date_logged_in desc 

)as table1

group by user_id

Это приведет к возврату одной строки с именем пользователя и в последний раз, когда пользователь зарегистрировался.