Magento2: Layered Nav Price Filter negative and incorrect Magento 2.2.0

[nadroj467]

Preconditions

1. Php 7.1
2. Centos 7
3. Migrated data from Magento 1.9.2.1 to Magento 2.2.0
4. Clean 2.2.0 install with custom theme
5. Single store mode active

Steps to reproduce

1. Currently experiencing issue with layered Nav price filter. The price ranges are completely incorrect. Default filter is showing price range -10 to 395

1. Applying price range filter just seems to throw random products in the results with no relavance to the actual price filter range set.

2. Tried:
   Reindexing via CL
   Disabling layered nav extension (native magento layered nav bar still having same issue)
   Checked price attribute setup in admin
   Checked all child product pricing

Expected result

1. Expecting products to be filtered correctly on pricing

Actual result

1. http://prntscr.com/jp1r63 Price filter range starts from -10 to 395
2. Random products appear to be included when filter is active with no relevance to the pricing filter applied

[nadroj467]

Further to this I have looked at product min_price max_price in database and found that the parent confiurable product is displaying incorrect min max price when all associated simple products are showing correct min max see here https://prnt.sc/jpfpj1

[itaymesh]

I am getting the same issue on 2.2.3

[nadroj467]

Apparently is a bug.

A developer fixed by editing below

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

[itaymesh]

@nadroj467, can you share with me the fix?
Thanks

[cncqw]

@itaymesh rewrite <Vendor>/<Module>/Model/ResourceModel/Product/Indexer/Price/Configurable.php

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',*/
            ]
        );
...

For more information, please see https://github.com/magento/magento2/issues/7367

[alkrenawizid]

Configurable products have redundant prices that cause wrong configurable min_price calculation

• To see the count of all configurable products prices records from the database

SELECT count(*) FROMcatalog_product_entity_decimalWHERE entity_id in (SELECT entity_id FROM catalog_product_entity c where c.type_id = 'configurable');

To solve the issue:

• Delete the price records from the database

DELETE FROMcatalog_product_entity_decimalWHERE entity_id in (SELECT entity_id FROM catalog_product_entity c where c.type_id = 'configurable');

• Reindex products prices

n98-magerun2.phar index:reindex catalog_product_price

• Clear cache

n98-magerun2.phar cache:clean

• Check there are no negative prices on the index table

select * from catalog_product_index_price where min_price < 0;

[maderlock]

To confirm, we are still using the same fix that @cncqw mentioned for 2.2.4. There has been a change to the method signature though, so you'll need to redo this fix to match most likely.

[xeontcs]

Magento 2.2.5. Same issue. is there official patch for this?

[ghost]

Hi @nadroj467 did this issue appear only when you migrate from 1.9 magento to 2.0 ? I'm not able to reproduce on clean magento 2.2 instance

[ghost]

@nadroj467 we can not reproduce the issue on clean magento instance. Please, report the issue to the https://github.com/magento/data-migration-tool repository is there is an issue in the tool.

[korostii]

Please take a look at https://github.com/magento/data-migration-tool/issues/579#issuecomment-417651364
This bug seems to be unrelated to data-migration-tool, please consider reopening the issue.

[sarjangautam]

I also had negative price in price index table for configurable products and this was happening because of Magento1 product price data for config products. In M1 , we used to store price data for config products and when we migrated that data to M2 those M1 price were carried over to M2. After reindexing i used to get negative price in the index table for config products. Since we do not need price for config products in M2 as M2 should calculate the config price based on simple products price i removed the config product price from catalog_product_entity_decimal table and reindexed. After that i no longer have negative price in price index table.

But i still have issue with price filter . Price filter is showing invalid range for certain category (e.g $0 - $200) . I do not any products with $0 price in that category and max price in $500 not $200

[pmathbliss]

dont do this:

DELETE FROMcatalog_product_entity_decimalWHERE entity_id in (SELECT entity_id FROM catalog_product_entity c where c.type_id = 'configurable');

select the price and special price ids first

DELETE FROMcatalog_product_entity_decimalWHERE attribute_id = PRICEID and entity_id in (SELECT entity_id FROM catalog_product_entity c where c.type_id = 'configurable');

[TOIVI291]

@pmathbliss

I also have this problem(after using the data-migration tool).

What did you put at "PRICEID"?

[pmathbliss]

Attribute I'd of your price attributes

On Thu, Feb 28, 2019, 12:14 AM TOIVI291 [e-mail] wrote:

@pmathbliss https://github.com/pmathbliss

I also have this problem(after using the data-migration tool).

What did you put at "PRICEID"?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/magento/magento2/issues/15654#issuecomment-468178591,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AA6BuCU4Y73F1OC7Nu1uRhxPPb1nNK5iks5vR4_XgaJpZM4UWSph
.

[TOIVI291]

@pmathbliss Okay thanks i got the query working, but if i want to retrieve the products on sale, i don't get any products, while i got products before the query. Did you have problems with this to?

[Vladishev]

I found a cause of this issue (Magento 2.2.7). It's not connected to code, but to products have been imported to a database.
Read my answer here https://magento.stackexchange.com/a/267922/32065.

[hafizjee]

faced same problem
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?

[mgsolutionscode]

Magento 2 does not support special_price on configurable products. The migration tool set special_price on configurable products inside table catalog_product_entity_decimal (it is attribute_id = 65).

When there is special_price for configurable product the price index calculation is completely wrong. In Magento 2 special_price is used from simple products. Also, you can see in admin > product > edit page that it is not possible to set special_price.

Also, you can see in this file MagentoCatalogSetupCategorySetup::getDefaultEntities that speicial_price is applied only to simple and virtual products.

So you should just remove special_price from configurable products.

#
# Delete special price for configurable products
#
DELETE
FROM catalog_product_entity_decimal
WHERE attribute_id = 65
  AND entity_id IN (SELECT entity_id FROM catalog_product_entity c WHERE c.type_id = 'configurable')

[chandracs353]

Configurable products have redundant prices that cause wrong configurable min_price calculation

To see the count of all configurable products prices records from the database
SELECT count(*) FROM catalog_product_entity_decimal WHERE entity_id in (SELECT entity_id FROM catalog_product_entity c where c.type_id = 'configurable');

To solve the issue:

Delete the price records from the database
DELETE FROM catalog_product_entity_decimal WHERE entity_id in (SELECT entity_id FROM catalog_product_entity c where c.type_id = 'configurable');

Reindex products prices
php bin/magento index:reindex catalog_product_price

Clear cache
php bin/magento cache:clean

Check there are no negative prices on the index table
select * from catalog_product_index_price where min_price < 0;

If, it will not work then run All commands

Setup Upgrade Using Command Line

php bin/magento setup:upgrade

Run the single-tenant Compiler Using Command Line

php bin/magento setup:di:compile

Static Content Deploy Using Command Line (Use -f for force deploy on 2.2.x or later)

php bin/magento setup:static-content:deploy

Cache Clean Using Command Line

php bin/magento cache:clean

Cache Flush Using Command Line

php bin/magento cache:flush