Как исправить странное поведение group by?

Question

[Andrzej Wielski]

Здравствуйте.

Имеются две таблицы - свойства, и их значения.
Структура соответственно следующая:

products_options
product_option_values


Суть проблемы такова - при выборке свойств по подходящим товарам группировка происходит неверно. Часть значений попросту исчезает.

Пример запроса:

select * from `product_options`
inner join `product_option_values` on `product_options`.`id` = `product_option_values`.`option_id`
where `product_option_values`.`product_id` in (1,2,3)
group by `product_option_values`.`value`

Как мы видим - в ответ выдаются значения, value которых не более 150.


Но если мы уберем group by...


То увидим значения с value и 200, и даже 300.
Как поступить? Я уже был на тостере с этим вопросом, но увы, мне не смогли помочь. Я уже устал вручную пересобирать таблицу (копируя данные во временную, пересоздавая, и возвращая данные обратно).

Тип таблиц - InnoDB

Answer 1

[vayho]

Удалил ответ, думая что product_id все же уникален но нет.
У вас на скриншоте в IN запросе product_id 161 и 159, у них одинаковые value = 80, в такой ситуации MySQL выдает неопределенную выборку. Вот дополнительная информация:
stackoverflow.com/questions/1591909/group-by-behav...

Не используйте * в выборке, определяйте явно поля которые хотите видеть. Используйте агрегацию в GROUP BY либо делайте GROUP BY по уникальному полю если хотите видеть DISTINCT.

Comments

[Andrzej Wielski]

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

[vayho]

Что выдает этот запрос на реальных данных?
select `product_option_values`.`value`, count(`product_option_values`.`product_id`),
group_concat(`product_option_values`.`product_id`, ',') from `product_options`
inner join `product_option_values` on `product_options`.`id` = `product_option_values`.`option_id` AND `product_option_values`.`product_id` in (1,2,3)
group by `product_option_values`.`value` having count(`product_option_values`.`product_id`) > 1;

[Andrzej Wielski]

vayho: Значения на месте.
https://habrastorage.org/files/a65/be3/6bd/a65be36...

При этом, если выполнить банально
select `product_option_values`.`option_id`, `product_option_values`.`value` from `product_option_values` where `product_option_values`.`product_id` in (...) group by `product_option_values`.`value`

То получим тот же баг - значений нет. Чую, проблема как раз в группировке.

[vayho]

Здесь нельзя исправлять комментарии, вместо group_concat(`product_option_values`.`product_id`, ',') используйте group_concat(`product_option_values`.`product_id`) чтобы лишнюю запятую убрать.

А по поводу бага,
select `product_option_values`.`value`, count(`product_option_values`.`option_id`),
group_concat(`product_option_values`.`option_id`) from `product_options`
inner join `product_option_values` on `product_options`.`id` = `product_option_values`.`option_id` AND `product_option_values`.`product_id` in (1,2,3)
group by `product_option_values`.`value` having count(`product_option_values`.`product_id`) > 1;
Выполните этот вопрос и если он вернет какие то данные, значит option_id тоже не уникально для value в группировке.

[Andrzej Wielski]

vayho: Спасибо большое. Вся проблема была в option_id, запрос группировал все по значению которое повторялось с разными option_id, вот mysql и "слил" эти два поля воедино.
Добавил дополнительно группировку option_id (группируя по двум полям), все заработало как надо.

Answer 2

[Adamos]

select * from `product_options`
inner join `product_option_values` on `product_options`.`id` = `product_option_values`.`option_id` AND `product_option_values`.`product_id` in (1,2,3)
group by `product_option_values`.`value`

Comments

[Andrzej Wielski]

К сожалению, не помогает.
https://habrastorage.org/files/8b9/e23/897/8b9e238...

[Adamos]

Ну, а без этого IN в выборке с GROUP BY встречаются эти id с другими value? Есть подозрение, что дело именно в группировке. Может быть, стоит разбить на подзапросы - и группировать уже готовое сведение.

[Andrzej Wielski]

Adamos: Без выборки по IN данные верные. Очень странно