I'm using CE 1.9.2.1 and have tested this on a vanilla installation of Magento. This issue only occurs when my products are associated to multiple websites.
I have applied a price adjustment to two of the three associated products. On the front-end category view, the "As low as" price shows twice the intended adjustment (-£3.00, not -£1.50).
** UPDATE **
I have checked the catalog_product_index_price
table of my database and found that the min_price
value for my configurable product is incorrect, even here.
I dug a little deeper and found protected function _applyConfigurableOption()
in
app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Configurable.php
Looking at line 221
it shows:
$select->columns(array(
'min_price' => new Zend_Db_Expr('i.min_price + io.min_price'),
'max_price' => new Zend_Db_Expr('i.max_price + io.max_price'),
'tier_price' => $write->getCheckSql('i.tier_price IS NOT NULL', 'i.tier_price + io.tier_price', 'NULL'),
'group_price' => $write->getCheckSql(
'i.group_price IS NOT NULL',
'i.group_price + io.group_price', 'NULL'
),
));
By trial and error, I changed the second line to the following (removed i.min_price
):
'min_price' => new Zend_Db_Expr('io.min_price')
I then went back into my product and saved it again which forced a reindex of the database. The catalog_product_index_price
table showed that the min_price
value was now -3.0000 so this shows the issue is io.min_price
.
Now I need to know what to do with the io.min_price
value so that it doesn't create a double discount when saving to the database.
Best Answer
I finally figured out what was wrong in indexer.
You put me on the right way by indicating that the problem was related to multi-websites.
The price variation will be applied as many times there are websites (minus the website code
admin
, which has thewebsite_code
0). E.g: if you have two websites (other thanadmin
) and set a price variation of 5, then the variation will be 10 instead 5. If you have three websites, then the variation will be 15, etc.The problem also affect the
max_price
column.I looked back into older versions of Magento, and I found that the bug was not present in CE 1.8.0.0 (and may be some of other newer versions between 1.8.x and 1.9.x).
So, after making a diff between files, I found some additions, on latest CE version, in app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Configurable.php ; the offending lines are:
As you can guess, those lines are doing some joins to
website
/store_group
/store
tables, andMage_Catalog_Model_Resource_Product_Indexer_Abstract::_addWebsiteJoinToSelect()
is precisely responsible of the "bug":If you look at the generated SQL select query at this state, it will look like this:
Did you see the
CROSS JOIN core_website AS cw
part? It is risky to do that, as aCROSS JOIN
(which is, in MySQL, a strict alias ofINNER JOIN
) without joining condition will do the join for each line of the joined table. The number of results is mathematically the product of the number of lines from the source table by the number of lines from the target table. E.g: if you had 2 lines in the source table, and 3 lines in the target table, then aCROSS JOIN
would give you 6 lines (2 x 3).In our case, it could be okay, as we want a product for all websites and calculate its price for each website. But, that work is in fact already done by
Mage_Catalog_Model_Resource_Product_Indexer_Price_Default::_prepareFinalPriceData
, which puts a line for each product and website incatalog_product_index_price_final_idx
table and is called just beforeMage_Catalog_Model_Resource_Product_Indexer_Price_Configurable::_applyConfigurableOption()
.Here are the facts: if no
$joinCondition
is specified as third parameter ofMage_Catalog_Model_Resource_Product_Indexer_Abstract::_addWebsiteJoinToSelect()
, it will result in aCROSS JOIN
without join condition, which is incoherent because we have already awebsite_id
incatalog_product_index_price_final_idx
table. Then... Guess the result: your calculatedmin_price
/max_price
is affected by the fact that a same variation will be duplicated as many times there are websites.To solve the issue, we have to restore coherence between
website_id
column of tablecatalog_product_index_price_final_idx
and the same one ofcore_website
table. InMage_Catalog_Model_Resource_Product_Indexer_Price_Configurable::_applyConfigurableOption()
, specify the join condition in_addWebsiteJoinToSelect()
method:In SQL, the join will result to:
Ok, our join now looks like this:
INNER JOIN core_website AS cw ON cw.website_id = i.website_id
.Then... That's it. Problem solved.
Hope it will help some people. And don't expect Magento to correct the problem, as the version 2 is now out and that there will be probably only security updates in the future...