Magento2: Magento2 multi-store price-per-website: Wrong price indexed for configurable products

[dmitrisol]

I have a multi-website Magento 2.1.0 CE installation, with 2 websites and different prices per-website. Prices are note related to each other, for the same product across the two websites.

In product listing pages (PLP), I keep seeing "as low as $55.55" for configurable prices, along with the correct prices, in non-default website 2, even though prices have been set correctly, all configurable children have the same prices $55.55 in website 1 (default) and $77.77 in website 2 (non-default). Configurable product itself has been priced accordingly, there are no special nor tier prices applied.

I've checked price attribute values in database, all prices are save correctly per-website. Checking the price index table, though, returns correct min_price and max_price for simple products but prices from the wrong website for the configurable products, which is causing the "as low as" issue in product listing pages.

No 3rd-party modules are affecting indexing (vanilla Magento).

Preconditions

1. Magento version CE 2.1.0 (or previous), not tested with more recent versions at the moment.

Steps to reproduce

1. Install vanilla Magento 2.1.0 CE
2. Create a second website, store and store view
3. Configure price scope to be per-website
4. Create a configurable product, to be in both websites, with price 55.55 for website 1 (default) and price 77.77 for website 2 (non-default), set all prices for the children simple products to 55.55 for website 1 and to 77.77 for website 2, enable the product, make it visible in catalog and search, assign to at least one category and save;
5. Perform a full reindex

Expected result

Prices should be shown as 55.55 in website 1 (store 1, default) and 77.77 in website 2 (store 2, non-default), in category page containing the product. No "as low as" prices is supposed to be shown.

Actual result

1. Navigate to product listing (category) page containing the product just added in default store (default website 1), you'll see the price stated as 55.55;
2. Switch store to store 2 (website 2), navigate to product listing (category) page in store 2 (website 2), you will see the price stated as 77.77 (as low as 55.55) even though you have not defined any promotions, tier prices or discounts and all simple children products have price of 77.77 in website 2.
   Select in database from catalog_product_index_price table for website_id IN (1,2) AND entity_id IN (1,2,3), where 1,2,3 are the IDs of the configurable product and its two children simple products. You should always see 55.55 for website 1 and 77.77 for website 2, in all price, min_price, max_price columns, instead you'll see 55.00 in min_price for website 2.

Cause

In vendor/magento/module-configurable-product/Model/ResourceModel/Product/Indexer/Price/Configurable.php, line 192:

$priceColumn = $this->_addAttributeToSelect($select, 'price', 'l.product_id', 0, null, true);

The fourth parameter to Magento\Catalog\Api\Data\ProductInterface\AbstractIndexer::_addAttributeToSelect() is supposed to the the store_id for the (non-default) price to join from the attribute "decimal" value table, instead Magento passes the hard-coded "0", which is causing default store (website) prices to be put in product price index for configurable products, regardless to the website_id.

Proposed solution

Replace line 192 mentioned above with the following:

$select->join(
    ['sg' => $this->getTable('store_group')],
    'sg.website_id = i.website_id',
    []
);
$priceColumn = $this->_addAttributeToSelect($select, 'price', 'l.product_id', 'sg.default_store_id', null, true);

[comdiler]

@dmitrisol, also l.product_id should be actually le.row_id

[sevos1984]

Thanks for reporting, this is a known issue, internal ticket is MAGETWO-60098

[tandih]

Hello,
Anyone try with 2.1.4. Does 2.1.4 version is fixed that issue?

[ritschi72]

Just tried with 2.1.4, still the same issue.

[choukalos]

Issue fixed in mainline (aka 2.2)

[korostii]

Guys, I hate to underline the obvious, but if the bug was discovered on 2.1.x and then reportedly "fixed", it is commonly expected to see that fix in the closest patch (2.1.x) release.
I was about to write more about that, but then I realized Magento Inc. just happens to have a different vision: they expect to see community contributors involved into backporting while their own priorities are elsewhere. That's fine, if you ask me.
But until that changes, maybe we could at least have some honest responses here?

IMHO if the bugfix is not released yet, the phrasing should look like the following:

Hey you all, here's a piece of code that fixes this bug: <insert commit hash(es) here>.
Well, no, we can't tell when the next version will be released and whether this patch will be included. Actually, we have to admit: there is a high probability that we won't release it anytime soon.
So, you know, until then... just select one of these marvelous options we offer you in the meantime (using those, you'll have the bug fixed exclusively for you in hours or days, and not months):

• patch the core files according to the commits referenced (not recommended because you'll lose the edits once you update the Magento to the next released version, so... just repeat that after every upgrade until the fix finds its way into a release)
• fork the repository, and cherry-pick these commits into your local 2.1 branch and use that instead of composer-installed version we released earlier (not recommended if you don't plan to contribute to the project, so... just do a merge from main repository and resolve some conflicts after each release, no biggie. And while you're at it, could you please also make a pull request pointing back into 2.1-develop of our repository? That'd be great)
• read all the devdocs and write a module that'll do these changes "the right way" using all those shiny extends and overrides and plugins and observers and so on (requires some development skills, so if you're a merchant... just call your friendly neighborhood developer guy to do it for you)

Hey, I just thought of something that could be included along with all those "Known Issues" into the release notes:
"Known workarounds and how to use them". That'd be great. Right?

[kervin]

The proposed solution works! Thanks!

$select->join(
['sg' => $this->getTable('store_group')],
'sg.website_id = i.website_id',
[]
);
$priceColumn = $this->_addAttributeToSelect($select, 'price', 'l.product_id', 'sg.default_store_id', null, true);

I cannot understand how Magento does not correct this core bug!

[niko7869]

@kervin i still have problem and i dont even have that code line on 192 ? can you please help me or give me bit detailed explanation to fix please

[kervin]

What version are you using?

I think if you upgrade to 2.1.8 it has been fixed. However, there are some other bugs in 2.1.8..

[niko7869]

@kervin I am using 2.1.8 only,i dont know how to do it and i am stuck :(

[kervin]

I do not have the issue in 2.1.8.

I think Magento refactored that code and the fix proposed in this case is not valid anymore.

Could you try to re-index price?

bin/magento indexer:reindex catalog_product_price

[niko7869]

@kervin i still have problem it didnt work even indexing, any other way ?

[surabhinsi]

same problem with me. In tables catalog_product_index_price, negative values of min and max price are saved.Which is causing lot of issues in price filtering.
I am using magento 2.1.8
I have checked in the above mentioned file there is no as such code on line no 192
I have done reindexing several times, including cache flush

[am2008]

+1,

After reindex, some configurable products prices become negative. Values are random. This is causing lot of issues in price sorting. Re-index don't help.

[surabhinsi]

I have done the changes in vendor/magento/module-configurable-product/Model/ResourceModel/Product/Indexer/Price/Configurable.php and it is working now in Magento 2.1.8
Attached is the file
Configurable.docx

[am2008]

@surabhinsi fix - OK. Thanks.

[magento-engcom-team]

@dmitrisol, thank you for your report.
The issue is already fixed in 2.2.0

[niko7869]

@surabhinsi it didnt work for me :(, can you tell me what exactly you did to make it work

[am2008]

in 2.2 issue still present. @surabhinsi fix works

protected function _applyConfigurableOption()
...
        $select->columns(
            [
                'min_price' => new \Zend_Db_Expr('io.min_price'),
                'max_price' => new \Zend_Db_Expr('io.max_price'),
                'tier_price' => 'io.tier_price',
                /*'min_price' => new \Zend_Db_Expr('i.min_price - i.orig_price + io.min_price'),
                'max_price' => new \Zend_Db_Expr('i.max_price - i.orig_price + io.max_price'),
                'tier_price' => 'io.tier_price',*/
            ]
        );
...

[Loxzibit]

I've just added the fix into a module for anyone interested https://github.com/WeareJH/m2-core-bug-configurable-prices

[am2008]

Why topic closed?

[l1mosh]

i still have the issue in ver. 2.1.7. is any one got any solution?

[giacmir]

I still have this problem in 2.2.2

[maderlock]

Are we still talking about the same problem? This is to do with multiple websites rather than random negative values? The latter could do with its own issue.

[Rudoslav]

I can confirm the same problem as @surabhinsi.
My _catalog_product_index_price_ table contains configurable products with negative values in _min_price_ column.

[sanjumagneto]

Please check https://github.com/manojind/Magento2.2.5-multi-store-tier-price-per-website-Wrong-tier-price-indexed

[giacmir]

This issue reappeared in 2.2.6. The old solution doesn't work anymore.

[manojind]

This issue reappeared in 2.2.3 beta. The old solution doesn't work anymore.

Please check it https://github.com/manojind/Magento2.2.5-multi-store-tier-price-per-website-Wrong-tier-price-indexed

[kervin]

This issue is in 2.2.6 again... Anyone has a solution for this?

[giacmir]

I'm reopening this since there are multiple users reporting that the issue is still present.

[fransyrcc]

Going from Magento 2.2.3 to Magento 2.2.6.
In Magento 2.2.3 the table catalog_product_index_price shows correct price, final_price, min_price, max_price and tier_price.
In Magento 2.2.6 min_price and max_price are set to 0 and tier_priceto null.

[drew7721]

Yep, same issue her. This is once again a bug in 2.2.6.

[fransyrcc]

I found the issue https://github.com/magento/magento2/issues/18264 that solves min_price and max_price but tier_price still set to null

[gavinlimely]

Same here, I've not seen a solution either.

If anyone can suggest a temporary fix, it would be massively appreciated.

[gavinlimely]

@fransyrcc just to confirm, the fix suggested in #18264 doesn't actually fix the problem for me.

[drew7721]

I'm actually using the final_price column instead of the min_price one. That one seems to be valid. For some reason it seems that the indexer does some _funky_ stuff with the prices of configurable products if prices are set on the parent product. I truncated the catalog_product_index_price table and ran indexers again, seems that it's not always the same products that have the price issue. This will get fixed in future releases.

For now, I would suggest looking at the _preparePriceExpressionParameters and maybe the addAttributeToSort of the \Magento\Catalog\Model\ResourceModel\Product\Collection and changing column to .final_price instead of .min_price.

In my opinion, the column name should be thrown with the event catalog_prepare_price_select so that we can decide in our custom modules what column we want to use, just a suggestion.

Essentially, just extend and overwrite those methods in your module then set a preference to use the new class globally. Should work. Let me know if it works. 🤙 ✌️

[kervin]

Hello,

This works for me on 2.2.6. Change this line and reindex price for the configurable products and the simple products associated with it.

https://github.com/magento/magento2/commit/36a3c26bd7492f4638fe02085156d2510687a34a

[gavinlimely]

Hi @kervin, I've tried that already. It doesn't actually work for me.

When I do a full reindex it works fine, however when the partial reindex runs every few minutes. It sets all of the min_prices to 0 in the index table.

Somebody has detailed the problem I'm having in this post: https://magento.stackexchange.com/questions/242801/magento-2-2-6-possible-problems#answer-244821

Cheers

[kervin]

@gavinlimely

1. Did you try to reindex the products? $ids of simple and configurable?

MagentoFrameworkIndexerCacheContextPrice:executeList($ids)

1. At some point I saw that the price in catalog_product_index_price was not good...

You can try to replace final_price in the indexer by price.

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

replace
[
'le.entity_id',
'customer_group_id',
'website_id',
'MIN(final_price)',
'MAX(final_price)',
'MIN(tier_price)',
]

BY
[
'le.entity_id',
'customer_group_id',
'website_id',
'MIN(price)',
'MAX(price)',
'MIN(tier_price)',
]

[magento-engcom-team]

Hi @TomashKhamlai. 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.

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

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

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

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

[TomashKhamlai]

@magento-engcom-team give me 2.3.0 instance

[magento-engcom-team]

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

[magento-engcom-team]

Hi @TomashKhamlai, here is your Magento instance.
Admin access: https://i-7367-2-3-0.instances.magento-community.engineering/admin
Login: admin Password: 123123q
Instance will be terminated in up to 3 hours.

[gavinlimely]

Why has this been closed? It's still an issue?

[TomashKhamlai]

@gavinlimely, according to the expected result

Prices should be shown as 55.55 in website 1 (store 1, default) and 77.77 in website 2 (store 2, non-default), in category page containing the product. No "as low as" prices is supposed to be shown.

the issue is fixed on 2.3.0. Everyone hopes that this fix will be backported to the next patch release on 2.2.x.

[drew7721]

This issue is not fixed. The cause for the negative pricing is because data imported from M1 probably has prices set on configurable products in the catalog_product_entity_decimal table. That is what causes the negative prices. You need to remove pricing for all configurable products from that table and then reindex. In M2 you can't set prices on configurable products, you could do that in M1.

For this issue to be fixed code should either clean data in the data migration tool or the indexer should be changed to disregard the deprecated data, then the issue will be fixed.

[gavinlimely]

This is still an issue in 2.3.0.

Even after removing pricing from the catalog_product_entity_decimal table, the reindex that occurs every few minutes powered by the catalog_product_price_cl table still wrongly sets the min_price to 0 in the price index.

As a hotfix I've changed line 221 of MagentoCatalogModelResourceModelProductIndexerPriceQueryBaseFinalPrice from :

$select->where(sprintf('e.entity_id BETWEEN %s AND %s', min($entityIds), max($entityIds)));

To

$select->where('e.entity_id IN (?)', $entityIds);

I don't understand the pricing indexer super well, but it looks as though on line 200 of MagentoConfigurableProductModelResourceModelProductIndexerPriceConfigurable, the WHERE clause is using an IN. So if the previous query was using a BETWEEN, there could potentially be 1000's of rows without a result.

[manaftryzens]

I had also the same issue and I think the above mentioned fix has resolved the issue

[crysix]

I found out, that if your simple products doesn't have a stock qty (but use stock set to "no"), you wont ever see a min_price in the index_price table for config products.

Solution: Add a qty and is_on_stock (in sql, but also possible with mass attribute change):
UPDATE cataloginventory_stock_item SET qty = 9999999, is_in_stock = 1 WHERE qty = 0;
UPDATE cataloginventory_stock_status SET qty = 9999999 WHERE qty = 0;
UPDATE cataloginventory_stock_status_replica SET qty = 9999999 WHERE qty = 0;

By the way, this is definitely a bug in m2. The indexer should always have a price for configurable products, also if the simple prodcuts are not in stock. Otherwise sorting/filtering wont work.

[aleron75]

I just incurred in this issue on Magento 2.2.6, the BETWEEN...AND... statement gives for granted that entities have consecutive IDs that was not my case, thus I (and others) have the error.

If you have products with consecutive IDs it's impossible to reproduce the issue.

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

[engcom-Bravo]

@magento-engcom-team give me 2.3-develop instance

[magento-engcom-team]

Hi @engcom-Bravo. Thank you for your request. I'm working on Magento 2.3-develop instance for you

[magento-engcom-team]

Hi @engcom-Bravo, here is your Magento instance.
Admin access: https://i-7367-2-3-develop.instances.magento-community.engineering/admin
Login: admin Password: 123123q
Instance will be terminated in up to 3 hours.

[engcom-Bravo]

I just incurred in this issue also on Magento 2.2.9. The issue is fixed.

[clivewalkden]

This is still an issue, I've had a site with this issue and the @gavinlimely fix has worked for me. To replicate this you may have to have a configurable product made up of lots of simple products (20)+, this in turn creates many records in the mview_state referencing the same entity_id.

I've created a patch for anyone interested https://github.com/sozo-design/magento2-patch-module-magento-catalog-gh7367

[ktsixit]

I have this issue in 2.3

I found out, that if your simple products doesn't have a stock qty (but use stock set to "no"), you wont ever see a min_price in the index_price table for config products.

Solution: Add a qty and is_on_stock (in sql, but also possible with mass attribute change):
UPDATE cataloginventory_stock_item SET qty = 9999999, is_in_stock = 1 WHERE qty = 0;
UPDATE cataloginventory_stock_status SET qty = 9999999 WHERE qty = 0;
UPDATE cataloginventory_stock_status_replica SET qty = 9999999 WHERE qty = 0;

By the way, this is definitely a bug in m2. The indexer should always have a price for configurable products, also if the simple prodcuts are not in stock. Otherwise sorting/filtering wont work.

In which file did you add this code?

[ktsixit]

I have the same issue in magento 2.3. How can I fix this?

[hafizjee]

I remove all Negative price manually. After that layered navigation filter shows the correct range and doesn't show the negative price range . But after running the commands :
sudo php bin/magento setup:upgrade
sudo php bin/magento setup:di:compile
sudo php bin/magento setup:static-content:deploy -f
sudo php bin/magento cache:clean
sudo php bin/magento cache:flush

All Negative values reverted and layered navigation show the Negative price range.
Why this happening?