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).

[ghost]

@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?

[magento-engcom-team]

Hi @XxXgeoXxX. Thank you for working on this issue.
Looks like this issue is already verified and confirmed. But if your want to validate it one more time, please, go though the following instruction:

• 1. Add/Edit Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 2. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 3. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

• 4. If the issue is not relevant or is not reproducible any more, feel free to close it.

[olivermontes]

hi @XxXgeoXxX any updates?

[LucianMihalache]

Any updates? this breaks the indexer and causes missing products from the indexed product table.
This is a really old problem, how comes that it is still not fixed...

[barryvdh]

Was this ever fixed? Still getting errors in the cron for indexer_update_all_views:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'database.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_product_website` AS `product_website` ON product_website.product_id = e.entity_id AND product_website.website_id IN(1)
 INNER JOIN `catalog_category_product_index_store0` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=0 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=0
sss

[nntoan]

Any updates? We are having the same issue here with cron indexer_update_all_views

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'development.catalog_category_product_index_store0' doesn't exist, query was: SELECT DISTINCT  `e`.*, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id AND product_website.website_id IN(1, 2, 3)
 INNER JOIN `catalog_category_product_index_store0` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=0 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=0 WHERE (e.created_in <= '1571960340') AND (e.updated_in > '1571960340')

[francescopis]

Same here on Magento commerce 2.2.8. any updates?

[manju-Cue]

Same issue I am facing on Magento 2.3.3. I found it in cron.log for indexer_update_all_views.
Did anyone get a solution for this?

[ctadlock]

So two years later its "ready for dev"? Fixes have already been committed, but the Magento team closed them. This is exactly why Magento is not a trusted product.

[ctadlock]

For anyone else with a similar issue. This seems very related to the MAGE_RUN_TYPE setting. When I have it set to store everything works fine, but when it is set to website I get the "catalog_category_product_index_store0 table not found error". For me its when I try to create an order in admin. It's truly fascinating to me that such core bugs can exist in Magento and not get any attention after this long of time.

2 exception(s):
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'store.catalog_category_product_index_store0' doesn't exist, query was: SELECT `main_table`.* FROM `wishlist_item` AS `main_table`
 INNER JOIN `catalog_category_product_index_store0` AS `cat_index` ON cat_index.product_id = main_table.product_id AND cat_index.category_id = '0' AND cat_index.visibility IN (3, 2, 4) WHERE (`wishlist_id` = '2220') AND (`main_table`.`store_id` IN('0'))
Exception #1 (PDOException): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'store.catalog_category_product_index_store0' doesn't exist

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'store.catalog_category_product_index_store0' doesn't exist, query was: SELECT `main_table`.* FROM `wishlist_item` AS `main_table`
 INNER JOIN `catalog_category_product_index_store0` AS `cat_index` ON cat_index.product_id = main_table.product_id AND cat_index.category_id = '0' AND cat_index.visibility IN (3, 2, 4) WHERE (`wishlist_id` = '2220') AND (`main_table`.`store_id` IN('0'))
<pre>#1 Magento\Framework\DB\Statement\Pdo\Mysql->_execute() called at [vendor/magento/zendframework1/library/Zend/Db/Statement.php:303]
#2 Zend_Db_Statement->execute() called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:480]
#3 Zend_Db_Adapter_Abstract->query() called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php:238]
#4 Zend_Db_Adapter_Pdo_Abstract->query() called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:546]
#5 Magento\Framework\DB\Adapter\Pdo\Mysql->_query() called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:613]
#6 Magento\Framework\DB\Adapter\Pdo\Mysql->query() called at [generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php:128]
#7 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor->query() called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:737]
#8 Zend_Db_Adapter_Abstract->fetchAll() called at [generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php:1558]
#9 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor->fetchAll() called at [vendor/magento/framework/Data/Collection/Db/FetchStrategy/Query.php:21]
#10 Magento\Framework\Data\Collection\Db\FetchStrategy\Query->fetchAll() called at [vendor/magento/framework/Data/Collection/AbstractDb.php:782]
#11 Magento\Framework\Data\Collection\AbstractDb->_fetchAll() called at [vendor/magento/framework/Data/Collection/AbstractDb.php:677]
#12 Magento\Framework\Data\Collection\AbstractDb->getData() called at [vendor/magento/framework/Data/Collection/AbstractDb.php:580]
#13 Magento\Framework\Data\Collection\AbstractDb->loadWithFilter() called at [vendor/magento/framework/Data/Collection/AbstractDb.php:565]
#14 Magento\Framework\Data\Collection\AbstractDb->load() called at [vendor/magento/module-sales/Block/Adminhtml/Order/Create/Sidebar/Wishlist.php:57]
#15 Magento\Sales\Block\Adminhtml\Order\Create\Sidebar\Wishlist->getItemCollection() called at [generated/code/Magento/Sales/Block/Adminhtml/Order/Create/Sidebar/Wishlist/Interceptor.php:37]
#16 Magento\Sales\Block\Adminhtml\Order\Create\Sidebar\Wishlist\Interceptor->getItemCollection() called at [vendor/magento/module-sales/Block/Adminhtml/Order/Create/Sidebar/AbstractSidebar.php:153]
#17 Magento\Sales\Block\Adminhtml\Order\Create\Sidebar\AbstractSidebar->getItems() called at [vendor/magento/module-sales/Block/Adminhtml/Order/Create/Sidebar/Wishlist.php:71]
#18 Magento\Sales\Block\Adminhtml\Order\Create\Sidebar\Wishlist->getItems() called at [generated/code/Magento/Sales/Block/Adminhtml/Order/Create/Sidebar/Wishlist/Interceptor.php:50]
#19 Magento\Sales\Block\Adminhtml\Order\Create\Sidebar\Wishlist\Interceptor->getItems() called at [vendor/magento/module-sales/Block/Adminhtml/Order/Create/Sidebar/AbstractSidebar.php:138]
#20 Magento\Sales\Block\Adminhtml\Order\Create\Sidebar\AbstractSidebar->getItemCount() called at [generated/code/Magento/Sales/Block/Adminhtml/Order/Create/Sidebar/Wishlist/Interceptor.php:154]
#21 Magento\Sales\Block\Adminhtml\Order\Create\Sidebar\Wishlist\Interceptor->getItemCount() called at [vendor/magento/module-sales/view/adminhtml/templates/order/create/sidebar/items.phtml:17]
#22 include() called at [vendor/magento/framework/View/TemplateEngine/Php.php:59]
#23 Magento\Framework\View\TemplateEngine\Php->render() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#24 Magento\Framework\View\TemplateEngine\Php\Interceptor->___callParent() called at [vendor/magento/framework/Interception/Interceptor.php:138]
#25 Magento\Framework\View\TemplateEngine\Php\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#26 Magento\Framework\View\TemplateEngine\Php\Interceptor->___callPlugins() called at [generated/code/Magento/Framework/View/TemplateEngine/Php/Interceptor.php:26]
#27 Magento\Framework\View\TemplateEngine\Php\Interceptor->render() called at [vendor/magento/framework/View/Element/Template.php:271]
#28 Magento\Framework\View\Element\Template->fetchView() called at [generated/code/Magento/Sales/Block/Adminhtml/Order/Create/Sidebar/Wishlist/Interceptor.php:518]
#29 Magento\Sales\Block\Adminhtml\Order\Create\Sidebar\Wishlist\Interceptor->fetchView() called at [vendor/magento/framework/View/Element/Template.php:301]
#30 Magento\Framework\View\Element\Template->_toHtml() called at [vendor/magento/module-backend/Block/Template.php:129]
#31 Magento\Backend\Block\Template->_toHtml() called at [vendor/magento/framework/View/Element/AbstractBlock.php:1100]
#32 Magento\Framework\View\Element\AbstractBlock->Magento\Framework\View\Element\{closure}() called at [vendor/magento/framework/View/Element/AbstractBlock.php:1104]
#33 Magento\Framework\View\Element\AbstractBlock->_loadCache() called at [vendor/magento/framework/View/Element/AbstractBlock.php:674]
#34 Magento\Framework\View\Element\AbstractBlock->toHtml() called at [generated/code/Magento/Sales/Block/Adminhtml/Order/Create/Sidebar/Wishlist/Interceptor.php:843]
#35 Magento\Sales\Block\Adminhtml\Order\Create\Sidebar\Wishlist\Interceptor->toHtml() called at [vendor/magento/framework/View/Layout.php:566]
#36 Magento\Framework\View\Layout->_renderBlock() called at [vendor/magento/framework/View/Layout.php:542]
#37 Magento\Framework\View\Layout->renderNonCachedElement() called at [generated/code/Magento/Framework/View/Layout/Interceptor.php:206]
#38 Magento\Framework\View\Layout\Interceptor->renderNonCachedElement() called at [vendor/magento/framework/View/Layout.php:497]
#39 Magento\Framework\View\Layout->renderElement() called at [generated/code/Magento/Framework/View/Layout/Interceptor.php:193]
#40 Magento\Framework\View\Layout\Interceptor->renderElement() called at [vendor/magento/framework/View/Element/AbstractBlock.php:521]
#41 Magento\Framework\View\Element\AbstractBlock->getChildHtml() called at [vendor/magento/module-sales/view/adminhtml/templates/order/create/sidebar.phtml:17]
#42 include() called at [vendor/magento/framework/View/TemplateEngine/Php.php:59]
#43 Magento\Framework\View\TemplateEngine\Php->render() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#44 Magento\Framework\View\TemplateEngine\Php\Interceptor->___callParent() called at [vendor/magento/framework/Interception/Interceptor.php:138]
#45 Magento\Framework\View\TemplateEngine\Php\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#46 Magento\Framework\View\TemplateEngine\Php\Interceptor->___callPlugins() called at [generated/code/Magento/Framework/View/TemplateEngine/Php/Interceptor.php:26]
#47 Magento\Framework\View\TemplateEngine\Php\Interceptor->render() called at [vendor/magento/framework/View/Element/Template.php:271]
#48 Magento\Framework\View\Element\Template->fetchView() called at [vendor/magento/framework/View/Element/Template.php:301]
#49 Magento\Framework\View\Element\Template->_toHtml() called at [vendor/magento/module-backend/Block/Template.php:129]
#50 Magento\Backend\Block\Template->_toHtml() called at [vendor/magento/framework/View/Element/AbstractBlock.php:1100]
#51 Magento\Framework\View\Element\AbstractBlock->Magento\Framework\View\Element\{closure}() called at [vendor/magento/framework/View/Element/AbstractBlock.php:1104]
#52 Magento\Framework\View\Element\AbstractBlock->_loadCache() called at [vendor/magento/framework/View/Element/AbstractBlock.php:674]
#53 Magento\Framework\View\Element\AbstractBlock->toHtml() called at [vendor/magento/framework/View/Layout.php:566]
#54 Magento\Framework\View\Layout->_renderBlock() called at [vendor/magento/framework/View/Layout.php:542]
#55 Magento\Framework\View\Layout->renderNonCachedElement() called at [generated/code/Magento/Framework/View/Layout/Interceptor.php:206]
#56 Magento\Framework\View\Layout\Interceptor->renderNonCachedElement() called at [vendor/magento/framework/View/Layout.php:497]
#57 Magento\Framework\View\Layout->renderElement() called at [generated/code/Magento/Framework/View/Layout/Interceptor.php:193]
#58 Magento\Framework\View\Layout\Interceptor->renderElement() called at [vendor/magento/framework/View/Element/AbstractBlock.php:521]
#59 Magento\Framework\View\Element\AbstractBlock->getChildHtml() called at [vendor/magento/module-sales/view/adminhtml/templates/order/create/data.phtml:106]
#60 include() called at [vendor/magento/framework/View/TemplateEngine/Php.php:59]
#61 Magento\Framework\View\TemplateEngine\Php->render() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#62 Magento\Framework\View\TemplateEngine\Php\Interceptor->___callParent() called at [vendor/magento/framework/Interception/Interceptor.php:138]
#63 Magento\Framework\View\TemplateEngine\Php\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#64 Magento\Framework\View\TemplateEngine\Php\Interceptor->___callPlugins() called at [generated/code/Magento/Framework/View/TemplateEngine/Php/Interceptor.php:26]
#65 Magento\Framework\View\TemplateEngine\Php\Interceptor->render() called at [vendor/magento/framework/View/Element/Template.php:271]
#66 Magento\Framework\View\Element\Template->fetchView() called at [vendor/magento/framework/View/Element/Template.php:301]
#67 Magento\Framework\View\Element\Template->_toHtml() called at [vendor/magento/module-backend/Block/Template.php:129]
#68 Magento\Backend\Block\Template->_toHtml() called at [vendor/magento/framework/View/Element/AbstractBlock.php:1100]
#69 Magento\Framework\View\Element\AbstractBlock->Magento\Framework\View\Element\{closure}() called at [vendor/magento/framework/View/Element/AbstractBlock.php:1104]
#70 Magento\Framework\View\Element\AbstractBlock->_loadCache() called at [vendor/magento/framework/View/Element/AbstractBlock.php:674]
#71 Magento\Framework\View\Element\AbstractBlock->toHtml() called at [vendor/magento/framework/View/Layout.php:566]
#72 Magento\Framework\View\Layout->_renderBlock() called at [vendor/magento/framework/View/Layout.php:542]
#73 Magento\Framework\View\Layout->renderNonCachedElement() called at [generated/code/Magento/Framework/View/Layout/Interceptor.php:206]
#74 Magento\Framework\View\Layout\Interceptor->renderNonCachedElement() called at [vendor/magento/framework/View/Layout.php:497]
#75 Magento\Framework\View\Layout->renderElement() called at [generated/code/Magento/Framework/View/Layout/Interceptor.php:193]
#76 Magento\Framework\View\Layout\Interceptor->renderElement() called at [vendor/magento/framework/View/Layout.php:594]
#77 Magento\Framework\View\Layout->_renderContainer() called at [vendor/magento/framework/View/Layout.php:544]
#78 Magento\Framework\View\Layout->renderNonCachedElement() called at [generated/code/Magento/Framework/View/Layout/Interceptor.php:206]
#79 Magento\Framework\View\Layout\Interceptor->renderNonCachedElement() called at [vendor/magento/framework/View/Layout.php:497]
#80 Magento\Framework\View\Layout->renderElement() called at [generated/code/Magento/Framework/View/Layout/Interceptor.php:193]
#81 Magento\Framework\View\Layout\Interceptor->renderElement() called at [vendor/magento/module-sales/Controller/Adminhtml/Order/Create/LoadBlock.php:89]
#82 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock->execute() called at [generated/code/Magento/Sales/Controller/Adminhtml/Order/Create/LoadBlock/Interceptor.php:24]
#83 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->execute() called at [vendor/magento/framework/App/Action/Action.php:108]
#84 Magento\Framework\App\Action\Action->dispatch() called at [vendor/magento/module-backend/App/AbstractAction.php:248]
#85 Magento\Backend\App\AbstractAction->dispatch() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#86 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->___callParent() called at [vendor/magento/framework/Interception/Interceptor.php:138]
#87 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#88 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#89 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#90 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#91 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#92 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#93 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#94 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#95 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#96 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#97 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#98 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#99 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#100 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#101 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#102 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#103 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#104 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#105 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#106 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#107 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#108 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#109 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#110 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#111 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/module-backend/App/Action/Plugin/Authentication.php:143]
#112 Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#113 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#114 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->___callPlugins() called at [generated/code/Magento/Sales/Controller/Adminhtml/Order/Create/LoadBlock/Interceptor.php:39]
#115 Magento\Sales\Controller\Adminhtml\Order\Create\LoadBlock\Interceptor->dispatch() called at [vendor/magento/framework/App/FrontController.php:162]
#116 Magento\Framework\App\FrontController->processRequest() called at [vendor/magento/framework/App/FrontController.php:98]
#117 Magento\Framework\App\FrontController->dispatch() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#118 Magento\Framework\App\FrontController\Interceptor->___callParent() called at [vendor/magento/framework/Interception/Interceptor.php:138]
#119 Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#120 Magento\Framework\App\FrontController\Interceptor->___callPlugins() called at [generated/code/Magento/Framework/App/FrontController/Interceptor.php:26]
#121 Magento\Framework\App\FrontController\Interceptor->dispatch() called at [vendor/magento/framework/App/Http.php:116]
#122 Magento\Framework\App\Http->launch() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#123 Magento\Framework\App\Http\Interceptor->___callParent() called at [vendor/magento/framework/Interception/Interceptor.php:138]
#124 Magento\Framework\App\Http\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/justbetter/magento2-sentry/Plugin/GlobalExceptionCatcher.php:58]
#125 JustBetter\Sentry\Plugin\GlobalExceptionCatcher->aroundLaunch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#126 Magento\Framework\App\Http\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#127 Magento\Framework\App\Http\Interceptor->___callPlugins() called at [generated/code/Magento/Framework/App/Http/Interceptor.php:26]
#128 Magento\Framework\App\Http\Interceptor->launch() called at [vendor/magento/framework/App/Bootstrap.php:261]
#129 Magento\Framework\App\Bootstrap->run() called at [pub/index.php:40]
</pre>

[TerrorSquad]

@ctadlock You mentioned that there are fixes for this. Are we talking about a patch? Do you mind sharing?

[Siyadev9]

Hi @ctadlock how did you manage to change the MAGE_RUN_TYPE ?

[github-jira-sync-bot]

✅ Jira issue https://jira.corp.magento.com/browse/AC-1026 is successfully created for this GitHub issue.

[m2-assistant]

✅ Confirmed by @engcom-Hotel. Thank you for verifying the issue.
Issue Available: @engcom-Hotel, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

[MatthijsBreed]

For those that want a short term solution/patch, you can edit the following lines in vendor/magento/module-catalog/Model/ResourceModel/Category/Collection.php

private function getProductsCountQuery(array $categoryIds, $addVisibilityFilter = true): Select
    {
        $categoryTable = $this->getTable('catalog_category_product_index');
// Start edit
        if ($this->_storeManager->getStore($this->getProductStoreId())->getWebsiteId() == "0") {
            $categoryTable = 'catalog_category_product';
        }
// End edit
        $select = $this->_conn->select()
            ->from(
                ['cat_index' => $categoryTable],
                ['category_id' => 'cat_index.category_id', 'count' => 'count(cat_index.product_id)']
            )
            ->where('cat_index.category_id in (?)', \array_map('\intval', $categoryIds));
        if (true === $addVisibilityFilter) {
            $select->where('cat_index.visibility in (?)', $this->catalogProductVisibility->getVisibleInSiteIds());
        }
        if (count($categoryIds) > 1) {
            $select->group('cat_index.category_id');
        }
        return $select;
    }

[Nuranto]

I'm also having this on 2.4.6, indexer_update_all_views crashes with Table 'shop.catalog_category_product_index_store0' doesn't exist, leaving price indexer in a working status, and preventing other indexes to get reindexed via backlog.

In a certain way, this is linked to #35216 (included in 2.4.7-beta, but not solving anything !)
and #34109

@MatthijsBreed I don't think your fix is safe : when $addVisibilityFilter is true it's probably crashing. ( catalog_category_product does not have visibility field)

[leonhelmus]

@ctadlock I have the same issue on 2.4.6. It would be nice that it does not look at the admin store id...

[Nuranto]

Issue is still present on 2.4.7