Group BY в запросах MySql vs MariaDB Исправлено

Question

Group BY в запросах MySql vs MariaDB Исправлено

Форум разработчиков »

3

Столкнулись с проблемой разного поведения MySql и MariaDB (sqlmode => TRADITIONAL) для кода:

$products = $collection->getProducts('id', $offset, $count);

Для теста берем стандартную "чистую" установку SS 8.22.x или SS 9.0.5 с автозаполением товарами через мастер (мобльные, планшеты и т.п.). В списке категорий есть

+Мобильные телефоны

 - Apple(4)
 - Samsung(4)
 - Nokia (4)

Берем и добавляем новую подкатегорию Яблоки ( type=0 ) с родителем "Мобильные телефоны" и в нее добавляем товары из Apple, пока все хорошо.

После чего начинается магия для кода:

$collection = new shopProductsCollection($product_ids, [no_plugins => true]);
...
$products = $collection->getProducts('id', $offset, $count);

Внутри вызывается примерно такой sql:

SELECT p.id FROM shop_product p JOIN shop_category_products cp1 ON p.id = cp1.product_id WHERE cp1.category_id IN(1,2,4,6,2260) GROUP BY cp1.sort, p.id ORDER BY cp1.sort ASC, p.id LIMIT 12

Где 2260 это ID y=нашей созданной категории Яблоки

И вот тут на MySql все ок:

, а на MariaDB результат совершенно другой

т.е. появились дубли 68, 69 и в результат не попали нужные id.

Лечится или DISTINCT() или заменой GROUP BY cp1.sort, p.id на GROUP BY p.id. Но это все внутри коллекции shopProductsCollection поэтому надо менять код там.

Есть предложения как это обойти (может подкрутить настройки sql сервера для MariaDB)?

Или все же это баг коллекции и надо ждать решения от разработчиков Shop-Script?

20 комментариев

+1

Михаил Ушенин Webasyst 18 марта 2022 17:47 #

После чего начинается магия для кода:
После каких конкретно действий она начинается?
Внутри вызывается примерно такой sql:
Лучше точно, а не примерно. Иначе трудно анализировать ваше сообщение об ошибке.

ответить
- 0
  
  Евгений Е 21 марта 2022 12:33 #
  Михаил, в плагине который реагирует на стандартный хук при сохранении категории вызывается код
  $collection = new shopProductsCollection($product_ids, [no_plugins => true]); ... $products = $collection->getProducts('id', $offset, $count);
  т.е. ничего сверхъестественного, просто запрос к коллекции.
  Далее дается перехваченный SQL, сформированный коллекцией.
  "Примерный" , потому что на стенде у проверяющего могут быть другие id категорий != 2260, а какие то свои.
  Сам запрос SQL полностью такой как формирует shopProductsCollection.
  Лечится или DISTINCT() или заменой GROUP BY cp1.sort, p.id на GROUP BY p.id. Но это все внутри коллекции shopProductsCollection поэтому надо менять код там.
  в shopProductsCollection есть 2 места
  $distinct = $this->joins && !$this->group_by ? 'DISTINCT ' : '';
  и
  if ((empty($this->info['sort_products']) && !waRequest::get('sort')) || waRequest::get('sort') == 'sort') { $this->group_by = $alias.'.sort, p.id'; $this->order_by = $alias.'.sort ASC, p.id'; }
  которые надо изменить, чтобы решить проблему с MariaDB (и думается это второе условие для GROUP BY).
  ответить
  - +1
    
    Anton F 21 марта 2022 20:20 #
    Лечится или DISTINCT() или заменой GROUP BY cp1.sort, p.id на GROUP BY p.id. Но это все внутри коллекции shopProductsCollection поэтому надо менять код там.
    ты об этом запросе?
    
    SELECT p.id FROM shop_product p JOIN shop_category_products cp1 ON p.id = cp1.product_id WHERE cp1.category_id IN(1,2,4,6,2260) GROUP BY cp1.sort, p.id ORDER BY cp1.sort ASC, p.id LIMIT 12
    
    если так то чем какая-то чушь - DISTINCT аналогичен GROUP BY(рекомендуется использовать GROUP BY т.к. DISTINCT это mysql расширение)
    ответить
+1

Anton F 19 марта 2022 13:48 #

А кто тебе сказал что они должны работать одинаково? "совместимы" не тоже самое что "идентичны"/"аналогичны". Базовое руководство:
MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4 function as limited drop-in replacements for MySQL 5.7, as far as InnoDB is concerned. However, the implementation differences continue to grow in each new MariaDB version.

ответить
+1

Anton F 19 марта 2022 13:53 #
в данном случае проблема в том, что разработчик не понимает/не знает разницу между
```
ON p.id = cp1.product_id
и 
ON cp1.product_id = p.id
```
ответить
- +1
  
  Syrnik.com 19 марта 2022 22:27 #
  
  Это ладно. Вот зачем ВА группирует по значению сортировки (cp1.sort) -- никак не могу сообразить.
  
  ответить
  - +1
    
    Anton F 21 марта 2022 20:15 #
    
    без этого запрос работать не будет: если поле не указано в group by, то оно не попадет и в последующий order by
    
    ответить
    - +1
      
      Anton F 21 марта 2022 20:25 #
      
      вернее будет, но только если не используется sql_model only_full_group_by
      
      ответить
+1

Anton F 19 марта 2022 14:01 #

sqlmode => TRADITIONAL не влияет на запросы (т.е. SELECT)

ответить
+1

Михаил Ушенин Webasyst 22 марта 2022 11:02 #
Эта часть в запросе значит, что вы формируете коллекцию из товаров, содержащихся в некоторой категории и вложенных в неё подкатегориях, и при этом пытаетесь применять значение сортировки «вручную» (настроенное путём перетаскивания товаров в категории)?
```
WHERE cp1.category_id IN(1,2,4,6,2260)
...
ORDER BY cp1.sort ASC
```
Но при отображении внутри категории товаров из вложенных подкатегорий сортировка, настроенная вручную, не может осмысленно работать — такая логика в Shop-Script никогда не закладывалась (пока что).

Проверьте — возможно, что-то не так в вашей логике формирования коллекции.
ответить
- +1
  
  Евгений Е 22 марта 2022 19:57 #
  Вот еще пример утрированного кода, который вызывает данный баг при сохранении категории:
  $productsCollection = new shopProductsCollection('category/'.$parent_id, ['no_plugins' => true]); $cat_product_ids = $productsCollection->getProducts('id', 0, $productsCollection->count());
  нужны товары именно всей родительской категории и подкатегорий. Получается он ломает коллекцию для MariaDB и обойти условие
  if ((empty($this->info['sort_products']) && !waRequest::get('sort')) || waRequest::get('sort') == 'sort')
  по ветке else не получается т.к. нет "защиты от дурака", если у родителя стоит ручная сортировка и включена настройка "Включить товары из подкатегорий".
  Тут явно надо доработать:
  Но при отображении внутри категории товаров из вложенных подкатегорий сортировка, настроенная вручную, не может осмысленно работать — такая логика в Shop-Script никогда не закладывалась (пока что).
  Хорошо если это вскоре появится в обновлениях, но нам то как быть в этом случае? Не видим механизма обхода без явных хаков, к которым бы не хотелось прибегать.
  ответить
  - +1
    
    Михаил Ушенин Webasyst 23 марта 2022 08:37 #
    
    если у родителя стоит ручная сортировка и включена настройка "Включить товары из подкатегорий"
    Средствами интерфейса Shop-Script такое сочетание настроек выбрать не получается, насколько в вижу. Если у вас получается, расскажите, как этого можно добиться.
    
    ответить
    - +1
      
      Евгений Е 23 марта 2022 11:04 #
      
      Хах, сюрприз - тестовая база с товарами "из коробки" вся содержит взаимоисключающие условия. А тест с багами полностью на ней делали.
      Поправьте хотя бы ее, чтобы на такое не попадать больше. С остальным тогда разберемся. И возможно в cron, который repair DB делает, тоже исправления внести для категорий с включенным параметром "Включить товары из подкатегорий" для пустых sort_products присвоить сортировку.
      Спасибо!
      
      ответить
      - +1
        
        Евгений Е 23 марта 2022 12:02 #
        
        Михаил, все же баг остался для категорий родителей у кого включена ручная сортировка и есть товары, которые принадлежат родителю (из практике такое часто может быть, когда в категорию накидали товары, а потом начали дробить на дочерние) SQL на MariaDB все равно остается невалидным из-за
        
        GROUP BY cp1.sort, p.id
        
        SELECT p.id FROM shop_product p JOIN shop_category_products cp1 ON p.id = cp1.product_id WHERE cp1.category_id = 1 GROUP BY cp1.sort, p.id ORDER BY cp1.sort ASC, p.id LIMIT 4
        
        Но в этом случае исключены дубли и код вернет желаемые 4 элемента.
        
        Ну и проблему с взаимоисключающими параметрами на старых сайтах никто не исключал.
        Заметил еще, что некоторые плагины импорта из XML/YML грешат такими багами.
        
        ответить
        
        +1
        
        Евгений Е 23 марта 2022 12:27 #
        
        И может все же в коллекции условие
        if ((empty($this->info['sort_products']) && !waRequest::get('sort')) || waRequest::get('sort') == 'sort')
        надо заменить на
        if ((empty($this->info['sort_products']) && !waRequest::get('sort') && !$this->info['include_sub_categories']) || waRequest::get('sort') == 'sort')
        
        ответить
        
        +1
        
        Михаил Ушенин Webasyst 23 марта 2022 15:02 #
        
        Не улавливаю, что вы тут имеете в виду. Приведите пример для этого случая:
        для категорий родителей у кого включена ручная сортировка и есть товары, которые принадлежат родителю (из практике такое часто может быть, когда в категорию накидали товары, а потом начали дробить на дочерние)
        
        ответить
        
        +1
        
        Евгений Е 23 марта 2022 19:26 #
        
        Ну для этого случая, что описал МарияДБ вернет такой же результат, что и MySQL, т.к в этом примере не будет дублей товаров при выключенном параметре показа товаров дочерних категорий.
        Но вот если вернуться к предыдущим примерам:
        $productsCollection = new shopProductsCollection('category/'.$parent_id, ['no_plugins' => true]); $cat_product_ids = $productsCollection->getProducts('id', 0, $productsCollection->count());
        порождает серию SQL-запросов, среди которых
        SELECT COUNT(DISTINCT p.id) FROM shop_product p JOIN shop_category_products cp1 ON p.id = cp1.product_id WHERE cp1.category_id IN(1,2,4,6,2266,2269)
        и
        SELECT p.id FROM shop_product p JOIN shop_category_products cp1 ON p.id = cp1.product_id WHERE cp1.category_id IN(1,2,4,6,2266,2269) GROUP BY cp1.sort, p.id ORDER BY cp1.sort ASC, p.id LIMIT 12
        и если убрать LIMIT 12
        SELECT p.id FROM shop_product p JOIN shop_category_products cp1 ON p.id = cp1.product_id WHERE cp1.category_id IN(1,2,4,6,2266,2269) GROUP BY cp1.sort, p.id ORDER BY cp1.sort ASC, p.id
        
        То на удивление число элементов будет больше чем 12, т.к. МарияДБ вернет результат с дублями (на тестовом стенде 19 элементов вернул).
        Т.е.
        $productsCollection->count()
        учитывает DISTINCT
        SELECT COUNT(DISTINCT p.id)...
        а
        $productsCollection->getProducts(...)
        в МарииДБ не сохраняет уникальность p.id в запросе
        SELECT p.id FROM shop_product p JOIN shop_category_products cp1 ON p.id = cp1.product_id WHERE cp1.category_id IN(1,2,4,6,2266,2269) GROUP BY cp1.sort, p.id ORDER BY cp1.sort ASC, p.id
        Поэтому есть подозрение, что для общего теоретиского случая вызова (не проверял на тестовом стенде)
        $productsCollection = new shopProductsCollection('search/category_id=1,2,3,4,2266,2269');
        если товары повторяются внутри категорий
        category_id=1,2,3,4,2266,2269
        и есть сортировка Вручную, то будут снова проблемы для
        $productsCollection->getProducts(...)
        из-за дублей p.id в результате, т.к. ожидаю получить
        $productsCollection->count()
        элементов, но получив это количество результат будет с дублями и часть уникальных id просто не будет учтено.
        
        ответить
        
        +1
        
        Михаил Ушенин Webasyst 24 марта 2022 08:16 #
        
        для общего теоретиского случая вызова (не проверял на тестовом стенде)
        Давайте обсуждать конкретные практические случаи. Если проблема не возникает на практике в магазинах, настроенных стандартным образом, боюсь, мы не можем тратить ресурсы на её «исправление», рискуя что-то сломать в работающих магазинах.
        
        ответить
      - +1
        
        Михаил Ушенин Webasyst 23 марта 2022 14:52 #
        
        Хах, сюрприз - тестовая база с товарами "из коробки" вся содержит взаимоисключающие условия. А тест с багами полностью на ней делали.
        Поправьте хотя бы ее, чтобы на такое не попадать больше. С остальным тогда разберемся.
        Исправим, спасибо!
        
        ответить
        
        +1
        
        Михаил Ушенин Webasyst 31 марта 2022 15:39 #
        
        Настройки категорий в демонстрационной базе товаров исправили. Сообщите, пожалуйста, если вдруг снова столкнётесь с этой проблемой.
        
        ответить

Group BY в запросах MySql vs MariaDB Исправлено

20 комментариев

Добавить комментарий