[Issue] Url Rewrite Cardinality issue

[m2-assistant[bot]]

This issue is automatically created based on existing pull request: magento/magento2#37690: Url Rewrite Cardinality issue

---

Preconditions and environment

• Magento version 2.4.X

merchandise a product in multiple store views that reindex

Steps to reproduce

Merchandise a product in a multi store view website run a reindex

Expected result

Catalog Search index has been rebuilt successfully in XX:YY:ZZ

Actual result

Catalog Search index process error during indexation process:
Fail in child process

and in exception.log

main.ERROR: Child process failed with message: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s), query was: SELECT `url_rewrite`.*, `relation`.`category_id`, `relation`.`product_id` FROM `url_rewrite`
 LEFT JOIN `catalog_url_rewrite_product_category` AS `relation` ON url_rewrite.url_rewrite_id = relation.url_rewrite_id WHERE (url_rewrite.entity_id IN ('538940')) AND (url_rewrite.entity_type IN ('product')) AND (url_rewrite.store_id IN (9)) AND (url_rewrite.redirect_type IN (0)) AND (relation.category_id = '368', '377', '380', '383', '764', '766', '772', '773', '774', '872', '874', '882', '883', '892', '950')

list of category ids at this point is irellevant

Additional information

This has caused a store to stop functioning properly.

Release note

Fix on url rewrite cardinality issue when running reindex for

Triage and priority

• [ ] Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• [X] 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”.

[alucardatem]

@magento give me 2.4-develop instance

[magento-deployment-service[bot]]

Hi @alucardatem. Thank you for your request. I'm working on Magento instance for you.

[alucardatem]

@magento I am working on this

[magento-deployment-service[bot]]

Hi @alucardatem, here is your Magento Instance: https://243df60ec9a7562e22825d04175ded1f.instances-prod.magento-community.engineering Admin access: https://243df60ec9a7562e22825d04175ded1f.instances-prod.magento-community.engineering/admin_9172 Login: 224c9daf Password: b68897db18ca

[m2-assistant[bot]]

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: :point_down:

• [ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
• [ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue.
• [ ] 3. Add Area: XXXXX label to the ticket, indicating the functional areas 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.

[alucardatem]

@magento Reproduced Issue on 2.4-develop

[alucardatem]

Will continue and finalize this on Monday 10th of july

[alucardatem]

@magento run all tests

[alucardatem]

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

2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. Description is good, alongside images in order to reproduce

3. Add Area: XXXXX label to the ticket, indicating the functional areas it may be related to. Area: Admin Panel + CLI

4. Verify that the issue is reproducible on 2.4-develop branch Issue can be reproduced on 2.4-develop

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

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

[engcom-Bravo]

Hi @alucardatem,

Thanks for your update.

Verified the issue on Magento 2.4-develop instance and the issue is not reproducible.Kindly refer the screenshots.

Steps to reproduce

We have followed the steps and configured as per this comment https://github.com/magento/magento2/issues/37689#issuecomment-1614404873

Indexing has been completed successfully.Please let us know if we are missing anything here.

Thanks.

[alucardatem]

yes. in the original post there were configs also for the stores -> catalog settings and basically what this fix does is to make sure that there is no cardinality issue on filtering

because we have the following:

\Magento\UrlRewrite\Model\Storage\DbStorage::prepareSelect has the following definition:

    protected function prepareSelect(array $data)
    {
        $select = $this->connection->select();
        $select->from($this->resource->getTableName(self::TABLE_NAME));

        foreach ($data as $column => $value) {
            $select->where($this->connection->quoteIdentifier($column) . ' IN (?)', $value);
        }

        return $select;
    }

then than is used in the DynamicStorage and DBStorage which state the follwing: DynamicStorage:

/**
     * @inheritDoc
     */
    protected function prepareSelect(array $data)
    {
        $metadata = [];
        if (isset($data[UrlRewrite::METADATA])) {
            $metadata = $data[UrlRewrite::METADATA];
            unset($data[UrlRewrite::METADATA]);
        }
        $select = $this->connection->select();
        $select->from(
            [
                'url_rewrite' => $this->resource->getTableName(self::TABLE_NAME)
            ]
        );
        $select->joinLeft(
            ['relation' => $this->resource->getTableName(Product::TABLE_NAME)],
            'url_rewrite.url_rewrite_id = relation.url_rewrite_id'
        );
        foreach ($data as $column => $value) {
            $select->where('url_rewrite.' . $column . ' IN (?)', $value);
        }
        if (empty($metadata['category_id'])) {
            $select->where('relation.category_id IS NULL');
        } else {
            $select->where(
                'relation.category_id = ?',
                $metadata['category_id']
            );
        }
        return $select;
    }

and for DbStorage:

 /**
     * @inheritDoc
     */
    protected function prepareSelect(array $data)
    {
        $metadata = [];
        if (isset($data[UrlRewrite::METADATA])) {
            $metadata = $data[UrlRewrite::METADATA];
            unset($data[UrlRewrite::METADATA]);
        }

        $select = $this->connection->select();
        $select->from(
            [
                'url_rewrite' => $this->resource->getTableName(self::TABLE_NAME)
            ]
        );
        $select->joinLeft(
            ['relation' => $this->resource->getTableName(Product::TABLE_NAME)],
            'url_rewrite.url_rewrite_id = relation.url_rewrite_id',
            ['relation.category_id', 'relation.product_id']
        );

        foreach ($data as $column => $value) {
            $select->where('url_rewrite.' . $column . ' IN (?)', $value);
        }
        if (empty($metadata['category_id'])) {
            $select->where('relation.category_id IS NULL');
        } else {
            $select->where(
                'relation.category_id = ?',
                $metadata['category_id']
            );
        }

        return $select;
    }

now expading and looking into the DynamicCategoryRewrites:

both

use Magento\CatalogUrlRewrite\Model\Storage\DynamicStorage;
use Magento\CatalogUrlRewrite\Model\Storage\DbStorage;

are being used

and dynamic storage is used in the constructor

   /**
     * @param ScopeConfigInterface|null $config
     * @param DynamicStorage $dynamicStorage
     */
    public function __construct(
        ScopeConfigInterface $config,
        DynamicStorage $dynamicStorage
    ) {
        $this->config = $config;
        $this->dynamicStorage = $dynamicStorage;
    }

and as you can see:

if the categoryRewrites are enabled it procceeds to execute prody function with the required data, however, when it is not this is the problem where it starts to develop as it ends up with

(relation.category_id = '368', '377', '380', '383', '764', '766', '772', '773', '774', '872', '874', '882', '883', '892', '950') where those numbers are the categories where that product should reside in in order to be able to create the proper redirects.

This is why i am saying that the current way of testing based of VANILLA type instances is not 100% correct and it can only be reproduced when following the logic of the code and the flow of data.

Also CLI wise --- unable to access that in any way shape or form as a contributer and while I am able to replicate that on magento instances, and another member is not, is most likely because not everything is read or missing images or missing configs or missing out on steps.

so basically this is reproducible as long as you have followed the images as well that were provided as guidance