Catalog product collection filters produce errors and cause inconsistent behaviour

[dnsv]

Here are three issues cramped in one, because they are all very similar, are caused by the same model and a possible fix should take all of them into account.

Preconditions

Magento 2.2

The code snippets should be executed in the crontab area, but some problems would likely occur in some other areas too. I've used magento's sample data and n98-magerun's sys:cron:run command for easier testing.

Issue 1

Steps to reproduce

/** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $collection */
$collection = $this->productCollectionFactory->create();
$collection->setVisibility([\Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH]);

$collection->load();

Expected result

A collection of products that is filtered based on visibility.

Actual result

[Zend_Db_Statement_Exception]
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento2.catalog_category_product_index_store0' doesn't exist, query was: SELECT `e`.*, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index_store0` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(4) AND cat_index.category_id='0'

[PDOException]
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento2.catalog_category_product_index_store0' doesn't exist

Details

The code for applying the filters is basically the same as in M1, except the table catalog_category_product_index seems to be deprecated in M2 since segmentation for Category Product Indexer was introduced with MAGETWO-89545. Because no store filter is set on the collection, the search is performed on the default store id (0).

I'm not quite sure what should the correct result be. M1 produces this select statement:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=0 AND cat_index.visibility IN(4) AND cat_index.category_id = '0'

This select returns no results as the table catalog_category_product_index contains only values with store_id > 0. The attribute visibility can be set for scope global, so I'd except the collection to return all products with the given visibility (regardless of how useful a collection filtered by visibility and not by store really is). Or should visibility be applied only when a store is set?

Issue 2

Steps to reproduce

/** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $collection */
$collection = $this->productCollectionFactory->create();
$collection->setVisibility([\Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH])
    ->addStoreFilter(1);

$collection->load();

Expected result

A collection of products that is filtered based on visibility and store id.

Actual result

[Zend_Db_Statement_Exception]
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento2.catalog_category_product_index_store0' doesn't exist, query was: SELECT `e`.*, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index_store0` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(4) AND cat_index.category_id='0'

[PDOException]
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento2.catalog_category_product_index_store0' doesn't exist

Details

This interestingly works if I first set the store filter:

$collection->addStoreFilter(1)
    ->setVisibility([\Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH]);

This produces the select statement:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(4) AND cat_index.category_id='2'

The problem with the visibility filter being set first occurs because the collections limitation filters (function _applyProductLimitations() inside \Magento\Catalog\Model\ResourceModel\Product\Collection) is always applied when a filter function (functions addStoreFilter(..), addWebsiteFilter(..), addCategoryFilter(..), addPriceData(..), setVisibility(..), applyFrontendPriceLimitations()) is being called.

Code walkthrough:

1. setVisibility(..) is called on the collection.
2. setVisibility(..) calls _applyProductLimitations().
3. _applyProductLimitations() calls _prepareProductLimitationFilters().
4. _prepareProductLimitationFilters() sets the store_id product limitation to the default store id (value: 0) and the category_id product limitation to 0.
5. addStoreFilter($storeId) is called on the collection.
6. addStoreFilter($storeId) also calls _applyProductLimitations() which calls _prepareProductLimitationFilters(). This function should update the category_id product limitation to the default category id for the given store, but this doesn't happen because the category_id product limitation will only get set once, and it was already set in step 4.

A possible solution would be to apply the product limitations only before the collection is being loaded - the function _applyProductLimitations() should be only called inside the function load() and not within every filter function. This would make sure that the filters are being applied when they are all set.

Issue 3

Steps to reproduce

// ObjectManager is used only to simplify the example.
$category = \Magento\Framework\App\ObjectManager::getInstance()->get(
    \Magento\Catalog\Model\Category::class
)->load(2);

/** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $collection */
$collection = $this->productCollectionFactory->create();
$collection->addCategoryFilter($category)
    ->addStoreFilter(1);

$collection->load();

Result

The underlying select statement isn't correct. It looks like this:

SELECT `e`.*, `cat_pro`.`position` AS `cat_index_position`, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product` AS `cat_pro` ON cat_pro.product_id=e.entity_id AND cat_pro.category_id='2'
 INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.category_id='2'

But should look like this:

 SELECT `e`.*, `cat_pro`.`position` AS `cat_index_position`, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e`
  INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.category_id='2'

Details

Code walkthrough:

1. addCategoryFilter(..) is called on the collection. This function checks if the collections store id is equal to \Magento\Store\Model::DEFAULT_STORE_ID. If it is, the function _applyZeroStoreProductLimitations() is being called and _applyProductLimitations() is skipped. This adds the excess INNER JOIN.
2. addStoreFilter(..) is called on the collection. Because we've now set a store id that isn't equal to \Magento\Store\Model::DEFAULT_STORE_ID, the conditions applied by _applyZeroStoreProductLimitations() are not correct anymore and should be removed (or shouldn't be applied in the first place).

@dnsv, thank you for your report.
We've acknowledged the issue and added to our backlog.

[zolthan]

What can I do to get rid of the error message?