CE 1.9 - Configurable product "As low as" price wrong

Question

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.

Answer 1

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