Magento2: Fulltext collection used on category page in toolbar pager links

[daniel-ifrim]

Preconditions

1. Magento 2.4-develop with sample data
   I'm in a case with a bug that reports one more product in getSize() on product collection. The product collection in fact the fulltext product collection from Magento_Catalog_Search. Why I get one more product in search collection is not relevant. How I get that one more product is the issue.

Steps to reproduce

1. On category page, in class \Magento\Catalog\Block\Product\ProductList\Toolbar::getPagerHtml() function the collection in use is \Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection
2. Log the collection query with $this->getCollection()->getSelect()->__toString()
3. The query does an extra JOIN:
   INNER JOIN search_tmp_592dad48d1e409_82724533 AS search_result ON e.entity_id = search_result.entity_id
   It's a temporary MySQL memory table managed from this class:
   \Magento\Framework\Search\Adapter\Mysql\TemporaryStorage
   This feature is used in Magento_CatalogSearch module which is ok.
   But it is used on category page too.

Expected result

1. It should use the usual product collection \Magento\Catalog\Model\ResourceModel\Product\Collection

Actual result

It uses \Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection and adds an extra INNER JOIN on a memory table created on the fly in category page.

Beside this it may run other unexpected code from Magento_CatalogSearch or Magento_Search module (I don't confirm this).

The problem is when declaring this in Magento_CatalogSearch/etc/di.xml:

<type name="Magento\Catalog\Model\Layer\Category">
    <arguments>
        <argument name="context" xsi:type="object">Magento\CatalogSearch\Model\Layer\Category\Context</argument>
    </arguments>
</type>

where in Magento_Catalog/etc/di.xml there is this declaration:

<type name="Magento\Catalog\Model\Layer\Category">
    <arguments>
        <argument name="context" xsi:type="object">Magento\Catalog\Model\Layer\Category\Context</argument>
    </arguments>
</type>

You can clearly see that the declaration from Magento_CatalogSearch module replaces the one from Magento_Catalog.
By commenting the above argument injection in Magento_CatalogSearch/etc/di.xml,
the collection Fulltext won't be anymore in \Magento\Catalog\Block\Product\ProductList\Toolbar::getPagerHtml(). The additional join on the memory tables is not made anymore. My case is fixed too.
But this is not the fix. It's a proof of what's happening.

The virtual types declared in both catalog and catalog search modules around Catalog Layer classes look good, but the argument injection from above and the logic behind them needs to be improved.

[magento-engcom-team]

@daniel-ifrim, thank you for your report.
We've created internal ticket(s) MAGETWO-80728 to track progress on the issue.

[udovicic]

Just to share some info on this, I have been in touch with Magento EE support on this one, and official standpoint is that this is not a bug, but rather planned feature. So it is kind of misleading to have it here marked as bug report and assigned to Community Dashboard including the internal ticket which is a dead end. Perhaps this should be adjusted as well so others can be aware of status as well.

Finally I just want to say that I am unpleasantly surprised that rendering category pages requires not one, but two on the fly indexers with temp tables just to display the category page.

[sshymko]

Experiencing the issue on 2.2.3 when MySQL server gets spontaneously overloaded by the query INSERT INTO search_tmp_* executed on a category view page. We haven't had an issue on 2.1.9 befoe upgradding to 2.2.3. Since then it reproduces infrequently out of blue.

Here's how the load spiked look in New Relic:

The following query is responsible for the spikes:

INSERT INTO `search_tmp_?b?f?ad?fe?_?` SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT  `search_index`.`entity_id`, (((?) + (?)) * ?) AS `score` FROM `catalog_product_index_eav` AS `search_index`
 INNER JOIN `cataloginventory_stock_status` AS `stock_index` ON stock_index.product_id = search_index.entity_id AND `stock_index`.`website_id` = ? AND `stock_index`.`stock_status` = ? AND `stock_index`.`stock_id` = ?
 INNER JOIN `catalog_category_product_index` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = ? WHERE (search_index.store_id = ?) AND (`search_index`.`attribute_id` = ? AND `search_index`.`value` IN (?, ?) AND `search_index`.`store_id` = ?) AND (category_ids_index.category_id = ?)) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
 LIMIT ?

[m2-assistant[bot]]

Hi @engcom-Delta. Thank you for working on this issue.
Looks like this issue is already verified and confirmed. But if you 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.4-develop branch

  Details

  - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.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. If the issue is not relevant or is not reproducible any more, feel free to close it.

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.

[magento-engcom-team]

:white_check_mark: Confirmed by @engcom-Delta
Thank you for verifying the issue. Based on the provided information internal tickets MC-30340 were created

Issue Available: @engcom-Delta, _You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself._

[magento-engcom-team]

Hi @daniel-ifrim.

Thank you for your report and collaboration!

The related internal Jira ticket MC-30340 was closed as non-reproducible in 2.4-develop.
It means that Magento team either unable to reproduce this issue using provided _Steps to Reproduce_ from the _Description section_ on clean Magento instance or the issue has been already fixed in the scope of other tasks.

But if you still run into this problem please update or provide additional information/steps/preconditions in the _Description section_ and reopen this issue.