Magento2: Catalog Product Listing Ui component multiplies SQL clauses

[romainruaud]

Preconditions

1. Magento 2.1.1
2.

Steps to reproduce

1. Navigate to the Product Listing page
2. Apply filtering (eg. on attribute set and product type)
3.

Expected result

1. Only one filter on attribute_set_id and type_id

Actual result

When logging the query :

SELECT e.*, at_qty.qty FROM catalog_product_entity AS e
LEFT JOIN cataloginventory_stock_item AS at_qty ON (at_qty.product_id=e.entity_id) AND (at_qty.stock_id=1) WHERE (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (e.attribute_set_id = '15') AND (e.attribute_set_id = '15') AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (e.attribute_set_id = '15') AND (e.attribute_set_id = '15') AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (e.attribute_set_id = '15') AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (e.attribute_set_id = '15') AND (e.attribute_set_id = '15') AND (e.attribute_set_id = '15') AND (e.attribute_set_id = '15') AND (e.attribute_set_id = '15') AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (at_qty.qty >= 100) AND (at_qty.qty <= 1001) AND (e.attribute_set_id = '15') AND (e.attribute_set_id = '15') ORDER BY e.type_id ASC

[1408sheva]

@romainruaud Is this issue still actual? We were not able to reproduce it on ver. 2.1.7 and [2.2-develop]

[korostii]

@romainruaud Is this issue still actual? We were not able to reproduce it on ver. 2.1.7 and [2.2-develop]

Hi @1408sheva. I am sorry to be bothering you by asking these, but I just don't get it.
1) Couldn't you also try to reproduce it on 2.1.1 first? Otherwise it remains unknown whether you followed steps differently or the bug is actually fixed.

2) To me, your "were not able to reproduce" phrase in context of this issue would mean that you did some logging as well yet a different query was produced. If my understanding is correct, could you please post here the query that you got?

[romainruaud]

@1408sheva I can confirm this is still the case on a Magento 2.1.7 (with sample data)

I go to the back-office product grid.

I filter on : "Attribute Set : Bag" and "Product Type : Simple".

I add log on Magento\Catalog\Ui\DataProvider\Product\ProductDataProvider on the getData() method :

    public function getData()
    {
        $writer = new \Zend\Log\Writer\Stream(BP . '/var/log/test.log');
        $logger = new \Zend\Log\Logger();
        $logger->addWriter($writer);
        $logger->info($this->getCollection()->getSelect()->assemble());

Here is what appears on the log file :

2017-07-28T10:00:18+00:00 INFO (6): SELECT e.*, at_qty.qty FROM catalog_product_entity AS e
LEFT JOIN cataloginventory_stock_item AS at_qty ON (at_qty.product_id=e.entity_id) AND (at_qty.stock_id=1) WHERE (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15') AND (e.type_id = 'simple') AND (e.attribute_set_id = '15')

[veloraven]

@romainruaud thank you for your report.
We already have corresponding internal ticket MAGETWO-70364.
I have mentioned this issue there.
Actually #9379 is linked to the internal issue so I'm closing this one in order to avoid duplicates.