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

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 the website_code 0). E.g: if you have two websites (other than admin) 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:

$this->_addWebsiteJoinToSelect($select, true);
$this->_addProductWebsiteJoinToSelect($select, 'cw.website_id', 'le.entity_id');

As you can guess, those lines are doing some joins to website/store_group/store tables, and Mage_Catalog_Model_Resource_Product_Indexer_Abstract::_addWebsiteJoinToSelect() is precisely responsible of the "bug":

protected function _addWebsiteJoinToSelect($select, $store = true, $joinCondition = null)
{
    if (!is_null($joinCondition)) {
        $joinCondition = 'cw.website_id = ' . $joinCondition;
    }

    $select->join(
        array('cw' => $this->getTable('core/website')),
        $joinCondition,
        array()
    );

    if ($store) {
        $select->join(
            array('csg' => $this->getTable('core/store_group')),
            'csg.group_id = cw.default_group_id',
            array())
        ->join(
            array('cs' => $this->getTable('core/store')),
            'cs.store_id = csg.default_store_id',
            array());
    }

    return $this;
}

If you look at the generated SQL select query at this state, it will look like this:

SELECT `l`.`parent_id`, `l`.`product_id`, `i`.`customer_group_id`, `i`.`website_id`
FROM `catalog_product_index_price_final_idx` AS `i`
 INNER JOIN `catalog_product_super_link` AS `l` ON l.parent_id = i.entity_id
 INNER JOIN `catalog_product_super_attribute` AS `a` ON l.parent_id = a.product_id
 INNER JOIN `catalog_product_entity_int` AS `cp` ON l.product_id = cp.entity_id AND cp.attribute_id = a.attribute_id AND cp.store_id = 0
 LEFT JOIN `catalog_product_super_attribute_pricing` AS `apd` ON a.product_super_attribute_id = apd.product_super_attribute_id AND apd.website_id = 0 AND cp.value = apd.value_index
 LEFT JOIN `catalog_product_super_attribute_pricing` AS `apw` ON a.product_super_attribute_id = apw.product_super_attribute_id AND apw.website_id = i.website_id AND cp.value = apw.value_index
 INNER JOIN `catalog_product_entity` AS `le` ON le.entity_id = l.product_id
 CROSS JOIN `core_website` AS `cw`
 INNER JOIN `core_store_group` AS `csg` ON csg.group_id = cw.default_group_id
 INNER JOIN `core_store` AS `cs` ON cs.store_id = csg.default_store_id
 INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = le.entity_id AND pw.website_id = cw.website_id
WHERE (le.required_options=0)
GROUP BY `l`.`parent_id`,
    `i`.`customer_group_id`,
    `i`.`website_id`,
    `l`.`product_id`

Did you see the CROSS JOIN core_website AS cw part? It is risky to do that, as a CROSS JOIN (which is, in MySQL, a strict alias of INNER 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 a CROSS 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 in catalog_product_index_price_final_idx table and is called just before Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable::_applyConfigurableOption().

Here are the facts: if no $joinCondition is specified as third parameter of Mage_Catalog_Model_Resource_Product_Indexer_Abstract::_addWebsiteJoinToSelect(), it will result in a CROSS JOIN without join condition, which is incoherent because we have already a website_id in catalog_product_index_price_final_idx table. Then... Guess the result: your calculated min_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 table catalog_product_index_price_final_idx and the same one of core_website table. In Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable::_applyConfigurableOption(), specify the join condition in _addWebsiteJoinToSelect() method:

$this->_addWebsiteJoinToSelect($select, true, 'i.website_id');

In SQL, the join will result to:

SELECT `l`.`parent_id`, `l`.`product_id`, `i`.`customer_group_id`, `i`.`website_id` 
FROM `catalog_product_index_price_final_idx` AS `i`
 INNER JOIN `catalog_product_super_link` AS `l` ON l.parent_id = i.entity_id
 INNER JOIN `catalog_product_super_attribute` AS `a` ON l.parent_id = a.product_id
 INNER JOIN `catalog_product_entity_int` AS `cp` ON l.product_id = cp.entity_id AND cp.attribute_id = a.attribute_id AND cp.store_id = 0
 LEFT JOIN `catalog_product_super_attribute_pricing` AS `apd` ON a.product_super_attribute_id = apd.product_super_attribute_id AND apd.website_id = 0 AND cp.value = apd.value_index
 LEFT JOIN `catalog_product_super_attribute_pricing` AS `apw` ON a.product_super_attribute_id = apw.product_super_attribute_id AND apw.website_id = i.website_id AND cp.value = apw.value_index
 INNER JOIN `catalog_product_entity` AS `le` ON le.entity_id = l.product_id
 INNER JOIN `core_website` AS `cw` ON cw.website_id = i.website_id
 INNER JOIN `core_store_group` AS `csg` ON csg.group_id = cw.default_group_id
 INNER JOIN `core_store` AS `cs` ON cs.store_id = csg.default_store_id
 INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = le.entity_id AND pw.website_id = cw.website_id
WHERE (le.required_options=0)
GROUP BY `l`.`parent_id`,
    `i`.`customer_group_id`,
    `i`.`website_id`,
    `l`.`product_id`

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

Do you have any explanation for why this is happening? I've implemented the same solution by I can't figure why the first solution works