Duplicate product collection items when loading category positions in the default store

[leoquijano]

Preconditions (*)

1. Magento v2.4.2.
2. A catalog with one parent anchor category and a few subcategories for it.
3. Products assigned to both the parent category and to its subcategories.
4. Different product positions in each category the product is in.

Steps to reproduce (*)

1. Set up a small catalog with the conditions mentioned above: one or more products that are present in the parent category and in its children.
2. For each product, assign a different position for each category that product is in.
3. Add an admin side template or component that displays a product list, filtered by the parent category. This can be achieved with an admin side PHTML file or by using a Page Builder component such as the products list.

It's important that that widget is processed in the default store (Store::DEFAULT_STORE_ID), so the _applyZeroStoreProductLimitations call is used in the following code.

In \Magento\Catalog\Model\ResourceModel\Product\Collection:

public function addCategoryFilter(\Magento\Catalog\Model\Category $category)
{
    $this->_productLimitationFilters['category_id'] = $category->getId();
    if ($category->getIsAnchor()) {
        unset($this->_productLimitationFilters['category_is_anchor']);
    } else {
        $this->_productLimitationFilters['category_is_anchor'] = 1;
    }

    if ($this->getStoreId() == Store::DEFAULT_STORE_ID) {
        $this->_applyZeroStoreProductLimitations();
    } else {
        $this->_applyProductLimitations();
    }

    return $this;
}

Expected result (*)

The products list is rendered in the admin.

Actual result (*)

The products list rendering fails with the following error message:

'Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "<ID>" already exists.'

(where <ID> is a product ID)

---

The issue can be tracked down to the _applyZeroStoreProductLimitations call mentioned above:

protected function _applyZeroStoreProductLimitations()
{
    $filters = $this->_productLimitationFilters;
    $categories = $this->getChildrenCategories((int)$filters['category_id']);

    $conditions = [
        'cat_pro.product_id=e.entity_id',
        $this->getConnection()->quoteInto(
            'cat_pro.category_id IN (?)',
            $categories
        ),
    ];
    $joinCond = join(' AND ', $conditions);

    $fromPart = $this->getSelect()->getPart(\Magento\Framework\DB\Select::FROM);
    if (isset($fromPart['cat_pro'])) {
        $fromPart['cat_pro']['joinCondition'] = $joinCond;
        $this->getSelect()->setPart(\Magento\Framework\DB\Select::FROM, $fromPart);
    } else {
        $this->getSelect()->join(
            ['cat_pro' => $this->getTable('catalog_category_product')],
            $joinCond,
            ['cat_index_position' => 'position']
        );
    }
    $this->_joinFields['position'] = ['table' => 'cat_pro', 'field' => 'position'];

    return $this;
}

The JOIN with the catalog_category_product generates a SQL query like this:

SELECT 
  DISTINCT `e`.*,
  /* ... */
  `cat_pro`.`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 IN (<LIST OF CATEGORY IDS>)
/* ... */

(Where <LIST OF CATEGORY IDS> includes the parent category and its children)

Now, this query is supposed to return 1 row per product. But the join with catalog_category_product can return multiple entries per product, if the product has different positions in different categories. It can even be reproduced by running the simplified query above with the appropriate category IDs. Even though the query has a DISTINCT clause, the fact that a product can have different positions will make it return duplicate entries.

This, in turn, will cause an exception when adding the product to the collection, in \Magento\Framework\Data\Collection::addItem.

---

A workaround can be implemented by replacing the JOIN above with the following via a patch:

protected function _applyZeroStoreProductLimitations()
{
    $filters = $this->_productLimitationFilters;
    $categories = $this->getChildrenCategories((int)$filters['category_id']);

    $categoryProductSelect = $this->getConnection()->select();
    $categoryProductSelect->from("catalog_category_product");
    $categoryProductSelect->reset(\Magento\Framework\DB\Select::ORDER);
    $categoryProductSelect->reset(\Magento\Framework\DB\Select::LIMIT_COUNT);
    $categoryProductSelect->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET);
    $categoryProductSelect->reset(\Magento\Framework\DB\Select::COLUMNS);
    $categoryProductSelect->columns([
        "product_id"   => "product_id",
        "min_position" => new \Zend_Db_Expr("MIN(position)")
    ]);
    $categoryProductSelect->where("category_id IN (?)", $categories);
    $categoryProductSelect->group("product_id");

    $joinCond = "cat_pro.product_id = e.entity_id";

    $fromPart = $this->getSelect()->getPart(\Magento\Framework\DB\Select::FROM);
    if (isset($fromPart['cat_pro'])) {
        $fromPart['cat_pro']['joinCondition'] = $joinCond;
        $this->getSelect()->setPart(\Magento\Framework\DB\Select::FROM, $fromPart);
    } else {
        $this->getSelect()->join(
            ['cat_pro' => $categoryProductSelect],
            $joinCond,
            ['cat_index_position' => 'min_position']
        );
    }
    $this->_joinFields['position'] = ['table' => 'cat_pro', 'field' => 'min_position'];

    return $this;
}

This JOIN will return one entry per product and will pick only one position for each one.

Note that I grouped the products by the minimum position they have in any of the categories they're in. A more sophisticated solution might prefer a specific category (perhaps the one used for the filtering) over the other ones.

---

Please provide Severity assessment for the Issue as Reporter. This information will help during Confirmation and Issue triage processes.

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

I marked this as an S1 because it affects any admin area where filtered product lists are being used. This is an issue that will show up after the catalog has been used for a while (products might have different positions due to the normal catalog administration), and will break the admin rendering of the content areas that include those products.

[m2-assistant]

Hi @leoquijano. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento give me 2.4-develop instance - upcoming 2.4.x release

For more details, please, review the Magento Contributor Assistant documentation.

Please, add a comment to assign the issue: @magento I am working on this

---

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

⚠️ According to the Magento Contribution requirements, all issues must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting.

🕙 You can find the schedule on the Magento Community Calendar page.

📞 The triage of issues happens in the queue order. If you want to speed up the delivery of your contribution, please join the Community Contributions Triage session to discuss the appropriate ticket.

🎥 You can find the recording of the previous Community Contributions Triage on the Magento Youtube Channel

✏️ Feel free to post questions/proposals/feedback related to the Community Contributions Triage process to the corresponding Slack Channel

[craig-bartlett]

I've also been investigating this issue and can confirm it exists in Magento 2.4.1-p1 too.

[m2-assistant]

Hi @engcom-Bravo. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

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

• 4. 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!

• 5. Add label Issue: Confirmed once verification is complete.

• 6. Make sure that automatic system confirms that report has been added to the backlog.

[magento-engcom-team]

✅ Confirmed by @engcom-Bravo
Thank you for verifying the issue. Based on the provided information internal tickets MC-42616 were created

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

[engcom-Bravo]

I am able to reproduce with the steps given by the Reporter and get the same error as mentioned in the ticket description when the call is made to the method using _applyZeroStoreProductLimitations():

[amitmaurya1024]

Any update on this ?

[engcom-November]

Hi @leoquijano
As I can see this issue got fixed in the scope of the internal Jira ticket AC-711 by the internal team

Related commits: https://github.com/magento/magento2/search?q=AC711&type=commits
Based on Jira, target version is 2.4.5
thank you.