I'm experiencing a strange issue here where the price navigation filter (layered navigation) somehow came up with negative price ranges.
If I flush the price tables it seems to solve the problem, until next time prices get re-indexed. Then the negative price ranges re-appear.
Another issue is that the price ranges are not even accurate. It would show the following: CA$0.00 – CA$9.99 (2) – as if there are 2 items within this price range. But we do not have any items priced within this price range…
Help please!!
Best Answer
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');
Please backup your database before executing the query below!!!
To resolve, 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');
Then run the following commands which will update the catalog_product_index_price table:
bin/mage index:reindex
bin/mage cache:clean
Run the following to check for negative product prices:
select * from catalog_product_index_price where min_price < 0;