Catalog Price Rule containing several SKU-s doesn't work

Question

Upgraded a store from CE 1.5 to 1.9.0.1 and while everything else works just fine several Catalog Price Rules stopped working. After some investigation I found something weird:

ANY condition is TRUE
  SKU "is one of" sku1
  SKU "is one of" sku2

works just fine.

ANY condition is TRUE
  SKU "is one of" sku1,sku2

doesn't work.

Any ideas what causes this anomaly, how to fix it and why is this behavior different from older versions?

UPDATE: Preliminary investigation reveals that it's actually a bug in Magento core ... once I have a fix, I'll post it here as an answer.

Answer 1

I had the same issue, but on 1.8.1.0, so i tried to update to 1.9.0.1, still the same issue.

But I found why this happens:

This method: Mage_Rule_Model_Condition_Product_Abstract::prepareConditionSql()

Returns a part of a sql-statement, like so: cpf.sku IN('SKU')

What's interesting is that the IN contains the exact string that was entered in admin, like so: cpf.sku IN('SKU1, SKU2, SKU3')

Note the lack of ' between the SKU's, the string isn't exploded, so it won't find any sku's like that sting.

To fix the issue i modified (copied to local first of course) that method to look like this;

 public function prepareConditionSql()
{
    $alias     = 'cpf';
    $attribute = $this->getAttribute();
    $value     = $this->getValue();
    $operator  = $this->correctOperator($this->getOperator(), $this->getInputType());
      if ($attribute == 'category_ids') {
        $alias     = 'ccp';
        $attribute = 'category_id';
        $value     = $this->bindArrayOfIds($value);
    }

    // Andre Klang: This fixes issue with "is one of" & "is not one of"
    if(!is_array($value) && in_array($operator,array('()','!()'))) {
        $tmp = explode(',',$value);
        foreach($tmp as $trim) $values[] = trim($trim); 
        $value = $values;
    }

    /** @var $ruleResource Mage_Rule_Model_Resource_Rule_Condition_SqlBuilder */
    $ruleResource = $this->getRuleResourceHelper();

    return $ruleResource->getOperatorCondition($alias . '.' . $attribute, $operator, $value);
}

I also reported this as a bug to Magento: http://www.magentocommerce.com/bug-tracking/issue/index/id/278

Answer 2

Please note that this issue has been solved in Magento EE 1.14.2.0. The fix is darn simple :

In Mage_Rule_Model_Condition_Product_Abstract::prepareConditionSql() :

replace

$value = $this->getValue(); by $value = $this->getValueParsed();

Answer 3

Issue with catalog price rules still exist on latest Magento CE 1.9.1 and EE 1.14.1.0 , problems even more complex, details and hot fix for described problem and few more http://firebearstudio.com/blog/catalog-price-rules-core-bug-in-magento-ce-1-9-1-and-ee-1-14-1-0-array-to-string-conversion-error-and-incorrect-price-rules-behavior.html , all reported to bug tracker but no any reaction yet. Core patch with fix on GitHub - https://github.com/biotech/Magento1.9-CatalogPriceRulesFix