"Sort By: Price" issues with Configurable Products does not work without forced reindex

[lbajsarowicz]

Preconditions

Verified with 2 clean installations (no 3rd party modules)

• Magento 2.3.1 CE
• ElasticSearch 5.6.14
• Production Mode
• Index on Schedule

And:

• Magento 2.2.6 CE
• ElasticSearch 5.6.14
• Production Mode
• Index on Schedule

Steps to reproduce

1. Install clean Magento 2 instance
2. Deploy Sample Data
3. Run setup:upgrade (to install sample data)
4. Enter any category, choose "Price" as a "Sort By"

Expected Result

1. Products are displayed from the cheapest to the most expensive

Actual Result

1. Getting products with prices: 29.00, 22.00, 28.00, 24.00

Notice that also star rating is missing before manual reindex.

Under the hood, the difference is quite big:

Before: BEFORE_result.json.txt

After AFTER_result.json.txt

Workaround

Run bin/magento index:reindex until products are in the right order (sometimes more than once)

[m2-assistant[bot]]

Hi @lbajsarowicz. 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.3-develop instance - upcoming 2.3.x release

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

@lbajsarowicz do you confirm that you were able to reproduce the issue on vanilla Magento instance following steps to reproduce?

• [ ] yes
• [ ] no

---

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

[amenk]

Wild guess: configurables are not reindexed when simples are updated?

[lbajsarowicz]

Tried out already:

• 19878

• 19898

• 20407

• 22028

• 18264

@amenk - That can be a good guess, let me check that on Monday.

[lbajsarowicz]

Even without ElasticSearch (just MySQL indexers) issue occurs after price change.

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

  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.3-develop branch

  Details

  - Add the comment @magento 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!

• [ ] 5. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento 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

• [ ] 6. Add label Issue: Confirmed once verification is complete.

• [ ] 7. Make sure that automatic system confirms that report has been added to the backlog.

[m2-assistant[bot]]

Hi @engcom-Charlie. 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:

• [x] 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.

• [x] 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.

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

• [x] 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento 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!

• [x] 5. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento 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

• [x] 6. Add label Issue: Confirmed once verification is complete.

• [x] 7. Make sure that automatic system confirms that report has been added to the backlog.

[magento-engcom-team]

@engcom-Charlie Thank you for verifying the issue.

Unfortunately, not enough information was provided to acknowledge ticket. Please consider adding the following:

• [ ] Add "Component: " label(s) to this ticket based on verification result. If uncertain, you may follow the best guess
• [ ] Add "Reproduced on " label(s) to this ticket based on verification result
• [ ] Update issue content to follow format required by Issue Reporting Guidelines

Once all required information is added, please add label "Issue: Confirmed" again. Thanks!

[magento-engcom-team]

@engcom-Charlie Thank you for verifying the issue.

Unfortunately, not enough information was provided to acknowledge ticket. Please consider adding the following:

• [ ] Add "Component: " label(s) to this ticket based on verification result. If uncertain, you may follow the best guess
• [ ] Add "Reproduced on " label(s) to this ticket based on verification result
• [ ] Update issue content to follow format required by Issue Reporting Guidelines

Once all required information is added, please add label "Issue: Confirmed" again. Thanks!

[magento-engcom-team]

@engcom-Charlie Thank you for verifying the issue.

Unfortunately, not enough information was provided to acknowledge ticket. Please consider adding the following:

• [ ] Add "Component: " label(s) to this ticket based on verification result. If uncertain, you may follow the best guess
• [ ] Add "Reproduced on " label(s) to this ticket based on verification result
• [ ] Update issue content to follow format required by Issue Reporting Guidelines

Once all required information is added, please add label "Issue: Confirmed" again. Thanks!

[magento-engcom-team]

@engcom-Charlie Thank you for verifying the issue.

Unfortunately, not enough information was provided to acknowledge ticket. Please consider adding the following:

• [ ] Add "Component: " label(s) to this ticket based on verification result. If uncertain, you may follow the best guess
• [ ] Add "Reproduced on " label(s) to this ticket based on verification result
• [ ] Update issue content to follow format required by Issue Reporting Guidelines

Once all required information is added, please add label "Issue: Confirmed" again. Thanks!

[magento-engcom-team]

@engcom-Charlie Thank you for verifying the issue.

Unfortunately, not enough information was provided to acknowledge ticket. Please consider adding the following:

• [x] Add "Component: " label(s) to this ticket based on verification result. If uncertain, you may follow the best guess
• [x] Add "Reproduced on " label(s) to this ticket based on verification result
• [x] Update issue content to follow format required by Issue Reporting Guidelines

Once all required information is added, please add label "Issue: Confirmed" again. Thanks!

[magento-engcom-team]

@engcom-Charlie Thank you for verifying the issue.

Unfortunately, not enough information was provided to acknowledge ticket. Please consider adding the following:

• [ ] Add "Component: " label(s) to this ticket based on verification result. If uncertain, you may follow the best guess
• [ ] Add "Reproduced on " label(s) to this ticket based on verification result
• [ ] Update issue content to follow format required by Issue Reporting Guidelines

Once all required information is added, please add label "Issue: Confirmed" again. Thanks!

[magento-engcom-team]

@sdzhepa Thank you for verifying the issue.

Unfortunately, not enough information was provided to acknowledge ticket. Please consider adding the following:

• [ ] Add "Component: " label(s) to this ticket based on verification result. If uncertain, you may follow the best guess
• [ ] Add "Reproduced on " label(s) to this ticket based on verification result
• [ ] Update issue content to follow format required by Issue Reporting Guidelines

Once all required information is added, please add label "Issue: Confirmed" again. Thanks!

[magento-engcom-team]

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

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

[ilnytskyi]

This seems to be fixed in 2.3-develop branch since the sql fro listing sorts price by different column not final_price but min_price

SELECT `e`.*,
       `cat_index`.`position`                   AS `cat_index_position`,
       `price_index`.`price`,
       `price_index`.`tax_class_id`,
       `price_index`.`final_price`,
       IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price),
          price_index.min_price)                AS `minimal_price`,
       `price_index`.`min_price`,
       `price_index`.`max_price`,
       `price_index`.`tier_price`,
       IFNULL(review_summary.reviews_count, 0)  AS `reviews_count`,
       IFNULL(review_summary.rating_summary, 0) AS `rating_summary`
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 (2, 4) AND cat_index.category_id = 12
         INNER JOIN `catalog_product_index_price` AS `price_index`
                    ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND
                       price_index.customer_group_id = 0
         LEFT JOIN `review_entity_summary` AS `review_summary`
                   ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND
                      review_summary.entity_type =
                      (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
ORDER BY `price_index`.`min_price` asc

However I am not sure if we should save price for configurable products. e.g. in Demo Data after install we have pries saved for configurable products then when we save a configurable product from Admin Panel we will have null saved in catalog_product_entity_decimal table

To see prices for configurable products use this query:

SELECT catalog_product_entity.entity_id, sku, value_id, `value`
FROM catalog_product_entity
         inner join catalog_product_entity_decimal decimal2 on catalog_product_entity.entity_id = decimal2.entity_id and
                                                               attribute_id in (SELECT attribute_id
                                                                                from eav_attribute
                                                                                where attribute_code in
                                                                                      ('price', 'special_price'))
WHERE type_id = 'configurable';

1. final_price will be indexes as price attribute value for configurable product
2. If there is null or 0 price saved the final_price will be indexed as 0

see catalog_product_index_price

any ideas ?

ps: 2.3-develop + demo data for it

[magento-engcom-team]

Hi @lbajsarowicz, @ilnytskyi.

Thank you for your report and collaboration!

The related internal Jira ticket MC-18170 was closed as non-reproducible in 2.3-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.

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

[lbajsarowicz]

I will provide MFTF test to prove the issue, then.

[engcom-Delta]

Hi @lbajsarowicz . Unfortunately, I am not able to reproduce issue on 2.4-develop

Manual testing scenario:

• ElasticSearch 6.x(6.5.4)
• Production Mode
• Cron is installed
• Index on Schedule
• Enter any category, choose "Price" as a "Sort By"
• :heavy_check_mark: Products are displayed from the cheapest to the most expensive

Are you still facing this issue on the latest 2.4-develop branch?

[magento-engcom-team]

Hi @lbajsarowicz, @ilnytskyi, @engcom-Delta.

Thank you for your report and collaboration!

The related internal Jira ticket MC-23835 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.

[grazianodimaio]

This issue can be found on 2.3.5-p2. Does anyone found a solution to this? I'm asking to myself how it can be possible to find something like this after 4 years.

[chdzma]

@grazianodimaio I also have the same problem in 2.3.5.

Did you find a solution?

[grazianodimaio]

@chdzma

I found a solution in this file.

/vendor/magento/module-configurable-product/Model/ResourceModel/Product/Indexer/Price/Configurable.php

I commented out this section in the function fillTemporaryOptionsTable

// Does not make sense to extend query if out of stock products won't appear in tables for indexing

/*if ($this->isConfigShowOutOfStock()) {
   $select->join(
      ['si' => $this->getTable('cataloginventory_stock_item')],
      'si.product_id = l.product_id',
      []
   );
   $select->where('si.is_in_stock = ?', Stock::STOCK_IN_STOCK);
}*/

And now it works correctly, it's absurd because every product is in the same condition, enabled, and out of stock.

I commented directly in the core, since I'm really sick of working on Magento2 and I want to close this project as soon as possibile. When I finish this my agency will abandon the platform for something else.

Last week I wasted three days of work for not-working shipping e-mail. I'm really annoyed.

[vipin20]

Here Is a solution that works for me for version 2.3.5-p2:

vendor/magento/module-configurable-product/Model/ResourceModel/Product/Indexer/Price/Configurable.php:248

$selectForCrossUpdate->columns( [ 'min_price' => new \Zend_Db_Expr('i.min_price - i.price + io.min_price'), 'max_price' => new \Zend_Db_Expr('i.max_price - i.price + io.max_price'), 'min_price' => new \Zend_Db_Expr('io.min_price'), 'max_price' => new \Zend_Db_Expr('io.max_price'), 'tier_price' => 'io.tier_price', ] );

    After this run reindexing

[sheepfy]

Why is this issue closed? This is still wrong in latest magento version

[sheepfy]

This seems to be fixed in 2.3-develop branch since the sql fro listing sorts price by different column not final_price but min_price

SELECT `e`.*,
       `cat_index`.`position`                   AS `cat_index_position`,
       `price_index`.`price`,
       `price_index`.`tax_class_id`,
       `price_index`.`final_price`,
       IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price),
          price_index.min_price)                AS `minimal_price`,
       `price_index`.`min_price`,
       `price_index`.`max_price`,
       `price_index`.`tier_price`,
       IFNULL(review_summary.reviews_count, 0)  AS `reviews_count`,
       IFNULL(review_summary.rating_summary, 0) AS `rating_summary`
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 (2, 4) AND cat_index.category_id = 12
         INNER JOIN `catalog_product_index_price` AS `price_index`
                    ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND
                       price_index.customer_group_id = 0
         LEFT JOIN `review_entity_summary` AS `review_summary`
                   ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND
                      review_summary.entity_type =
                      (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
ORDER BY `price_index`.`min_price` asc

However I am not sure if we should save price for configurable products. e.g. in Demo Data after install we have pries saved for configurable products then when we save a configurable product from Admin Panel we will have null saved in catalog_product_entity_decimal table

To see prices for configurable products use this query:

SELECT catalog_product_entity.entity_id, sku, value_id, `value`
FROM catalog_product_entity
         inner join catalog_product_entity_decimal decimal2 on catalog_product_entity.entity_id = decimal2.entity_id and
                                                               attribute_id in (SELECT attribute_id
                                                                                from eav_attribute
                                                                                where attribute_code in
                                                                                      ('price', 'special_price'))
WHERE type_id = 'configurable';

1. final_price will be indexes as price attribute value for configurable product
2. If there is null or 0 price saved the final_price will be indexed as 0

see catalog_product_index_price

any ideas ?

ps: 2.3-develop + demo data for it

It was not fixed in 2.3-develop :|

[sheepfy]

@ilnytskyi in latest magento version, you will get min_price and max_price = 0 after price reindex for configurable products. You just missed the case, see more details in here https://github.com/magento/magento2/issues/34086

[Nazar65]

This issue should be reopened as well!

[agata-maksymiuk]

I know it's old Magento 2.3.1 but I'm facing this issue, and maybe I can help providing more details.

Configurable products have "0.00" prices, and it is fixed by full reindex. After a while (random, like an hour or next day), prices "0.00" appear again. Note that, it is also fixed by adding this specific product ID to catalog_product_price_cl manually, and waiting for reindex in the next minute.

+-----------+--------------------+---------------------+------------+--------+--------------+-------------+---------------+-----------+-----------+------------+ | entity_id | sku | updated_at | is_salable | price | tax_class_id | final_price | minimal_price | min_price | max_price | tier_price | +-----------+--------------------+---------------------+------------+--------+--------------+-------------+---------------+-----------+-----------+------------+ | 124211 | L22357-W23-516 | 2023-03-02 09:25:52 | 1 | 0.0000 | 2 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | NULL | |

Product has simple products and those simples are in stock.

I have an idea that maybe another process (except indexer_update_all_views) is reindexing prices in the same time - probably external bulk update. They might be using the same temporary table catalog_product_index_price_temp in the same moment, and they're trying to copy the data, that doesn't exist in that table (because another process dropped it and created new one). And the result is 0.00.

@lbajsarowicz @Nazar65 @sheepfy do you have any webapi external product updates in your projects?

[sheepfy]

@agata-maksymiuk I do have external api updates. Which triggers the _cl tables to row reindex. Also, in 2.3, (i bet also in 2.4) you get the 0 min and 0 max price after a price reindex for configurable products. Those prices are definitely wrong there. I have fixed that with a composer patch if I remember well. It's been like 2 years.

Well, this issue is closed, I haven't check in 2.4.4 or 2.4.5, but I am pretty sure it's still happening.

Also I see that you posted the 124211 which is a configurable product (a parent). The parent should have 0 price, 0 final price buuut it needs to have a min price and a max price.

Since this bug was present in 2.3, you get 0 price, 0 final price and also 0 for min and max price. Which seems to be working as expected since this task is closed :))

[agata-maksymiuk]

@sheepfy thank for you reply, I've already found the issue. It's already fixed in the latest Magento.

Here is the commit: https://github.com/magento/magento2/commit/10966be289465ebcc39a2f32c3a15ddce4398a37

In the case that more than one product was reindexed, query was BETWEEN. Let's say it was 2 products waiting for reindex in queue - ID 199 and ID 899. So this query took a random part of the products (based on min and max entity_id of those - 700 products in this case), and put the result into catalog_product_index_price_temp table. And the result was "0" prices, because configurable prices have no their own prices. That is OK for now.

Another query, which calculated configurable products prices based on their simple products prices, and updating catalog_product_index_price_temp table, was just for those specific products: 199 and 899 - query was IN(....) The prices based on simple products were calculated just for those 2 products - ID 199 and ID 899 - the rest of the products between them remained with "0" prices in catalog_product_index_price_temp table.

And the last query just copied all of the data from catalog_product_index_price_temp into `catalog_product_index_price. So the prices for product ids: 199 and 899 was properly indexed. And for the rest between them - from 200 to 898 was set to "0"

That's why it happend "randomly", under specific conditions:

• there must be 2 or more configurable products waiting in price reindex queue
• 1 or more confgurable product between them - these products got "0" prices every partial reindex.

To all of the people redirected by google here to this thread (like me) - to fix that problem in your previous Magento instances, just apply this commit I've mentioned above.