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 behaviour)

February 7, 2015 - Fire development, Magento tips & tricks

magento-catalog-price-rules-bug Short summary of the problem – if you have Magento catalog price rules with multiple select attributes in conditions or ONE OF price rules conditions with comma separated values – on latest Magento versions – you can have Array to string conversion error during you apply rules or incorrect rules behavior.

In this post we provide explanation and information about how to fix this Magento core bug what exists on latest Magento Community Edition 1.9.1 and Enterprise Edition 1.14.1.0 (confirmed on this versions by a lot of manualtests described below, we also know that on Magento CE 1.7 everything works fine and according to StackExchange issue can exist on all versions CE 1.8.x – 1.9.x). Issues are already reported on Magento bug tracker, but no any official patches are confirmed. Fixes provided below have been created in urgent matter for the significant Magento upgraded from 1.7 to 1.9.1. The update includes about 100 different price rules and 20000 products, fixes were tested and used on production but we can not guarantee that our core patches resolve issue complete. Keep in mind, that all code is provided without any guarantees and you can use it on your own responsibility. In a case when something went wrong, we can’t be blamed. We hope to get official patches for Community and Enterprise editions from Magento team as soon as possible!

Issue reports:

Download patch from GitHub repository – just upload it in app/code/local to overwrite core methods with patched methods

Samples of catalog rules issues on Magento CE 1.9.1 & EE 1.14.1.0

1. Catalog price rules which use multiple select attribute values in CONTAINS or NOT CONTAINS conditions can not be applied and cause error during you apply catalog price rules – Array to string conversion in app/code/core/Mage/Rule/Model/Resource/Rule/Condition/SqlBuilder.php in line 75, In same time IS ONE OF and IS NOT ONE OF conditions work good.

MultiSelect Attribute (attribute code – brand)

– TEST: Create catalog price rule with brand CONTAINS (any number of selected values)

– RESULT: Get an exception thrown and a message stating unable to apply rules in the admin.

2015-01-30T00:38:49+00:00 ERR (3): exception ‘Exception’ with message ‘Notice: Array to string conversion in app/code/core/Mage/Rule/Model/Resource/Rule/Condition/SqlBuilder.php on line 75’ in app/code/core/Mage/Core/functions.php:245

– TEST: Create catalog product price rule with brand NOT CONTAINS (any number of selected values)

– RESULT: Get an exception thrown and a message stating unable to apply rules in the admin.

2015-01-30T00:43:42+00:00 ERR (3): exception ‘Exception’ with message ‘Notice: Array to string conversion in app/code/core/Mage/Rule/Model/Resource/Rule/Condition/SqlBuilder.php on line 75’ in app/code/core/Mage/Core/functions.php:245

– TEST: Create catalog product price rule with brand IS ONE OF (any number of selected values)

– RESULT: No exceptions thrown and condition SQL generated correctly:

[0] = > ‘cpf’.’brand’ IN(‘237’, ‘236’, ‘232’, ‘235’)

– TEST: Create catalog product price rule with brand IS NOT ONE OF (any number of selected values)

– RESULT: No exceptions thrown and condition SQL generated correctly:

[0] => ‘cpf’.’brand’ NOT IN(‘237’, ‘236’, ‘232’, ‘235’)

2. Catalog price rules which use text attribute values in ONE OF condition cannot be applied correctly because in SQL query values are not exploded by comma to array

Text Attribute (attribute code – stock)

– TEST: Create catalog price rule with stock is one of: 1,3

– RESULT: Incorrect rules applied as the comma separated values as not accepted:

(‘cpf’.’stock’ IN(‘1,3’))

– TEST: Create catalog price rule with stock contains: 1,3

– RESULT: Incorrect rules applied as the comma separated values are not split correctly:

(‘cpf’.’stock’ LIKE ‘%1,3%’)

– TEST: Create catalog price rule with the following matching rules, stock IS 1 OR stock IS 3

– RESULT: Rules are applied as expected:

(‘cpf’.’stock’=’1′ OR ‘cpf’.’stock’=’3′)

Source code of patch for catalog price rules

To fix issues with catalog price rules, we have edited two Magento core files. In code below you can find our code between comments //CORE PATCH: … /// END CORE PATCH

File : app/code/local/Mage/Rule/Model/Resource/Rule/Condition/SqlBuilder.php

/**
     * Convert operator for sql where
     *
     * @param string $field
     * @param string $operator
     * @param string|array $value
     * @return string
     */
    public function getOperatorCondition($field, $operator, $value)
    {
        // CORE PATCH: Check to see if this is an (), {}, !() or !{} query, it is comma separated and it is not an array.
        if ( in_array($operator, array('()', '{}', '!()', '!{}')) && !is_array($value) && strstr($value, ',') ) {
            $value = explode(',', $value);
            $value = array_map('trim', $value);
        }
        // END CORE PATCH
        // CORE PATCH: Fix for products with few assigned values form multieselect attribute in catalog rules, regexp in SQL because in product flat table multieselect values stored as coma separated
        if (strstr($field, 'cpf.')){
            $attribute_parse = explode('.', $field);  // get attribute code ( attribute from cpf.attribute )
            $attribute = $attribute_parse[1];
            $attribute_type = Mage::getSingleton("eav/config")->getAttribute('catalog_product', $attribute)->getFrontendInput();
            if (strstr($attribute_type, 'multiselect')){
                $attribute_values = implode('|', $value);
                $result_patched = '`cpf`.`'.$attribute.'` REGEXP "(^|,)'.$attribute_values.'(,|$)"';
                return $result_patched; // return SQL for avoid future SqlBuider processing
            }
        }
        // END CORE PATCH
        switch ($operator) {
            case '!=':
            case '>=':
            case '<=':
            case '>':
            case '<':
                $selectOperator = sprintf('%s?', $operator);
                break;
            case '{}':
            case '!{}':
                // CORE PATCH: Commented out the category_id matching since any array should use this select operator.
                if (/*preg_match('/^.*(category_id)$/', $field) &&*/ is_array($value)) {
                    $selectOperator = ' IN (?)';
                } else {
                    $selectOperator = ' LIKE ?';
                    $value          = '%' . $value . '%';
                }
                if (substr($operator, 0, 1) == '!') {
                    $selectOperator = ' NOT' . $selectOperator;
                }
                break;
            case '()':
                $selectOperator = ' IN(?)';
                break;
            case '!()':
                $selectOperator = ' NOT IN(?)';
                break;
            default:
                $selectOperator = '=?';
                break;
        }
        $field = $this->_adapter->quoteIdentifier($field);
        if (is_array($value) && in_array($operator, array('==', '!=', '>=', '<=', '>', '<'))) {
            $results = array();
            foreach ($value as $v) {
                $results[] = $this->_adapter->quoteInto("{$field}{$selectOperator}", $v);
            }
            $result = implode(' AND ', $results);
        } else {
            $result = $this->_adapter->quoteInto("{$field}{$selectOperator}", $value);
        }
        return $result;
    }
}

/**

* Convert operator for sql where

* @param string $field

* @param string $operator

* @param string|array $value

* @return string

public function getOperatorCondition($field, $operator, $value)

{

// CORE PATCH: Check to see if this is an (), {}, !() or !{} query, it is comma separated and it is not an array.

if ( in_array($operator, array('()', '{}', '!()', '!{}')) && !is_array($value) && strstr($value, ',') ) {

$value = explode(',', $value);

$value = array_map('trim', $value);

}

// END CORE PATCH

// CORE PATCH: Fix for products with few assigned values form multieselect attribute in catalog rules, regexp in SQL because in product flat table multieselect values stored as coma separated

if (strstr($field, 'cpf.')){

$attribute_parse = explode('.', $field); // get attribute code ( attribute from cpf.attribute )

$attribute = $attribute_parse[1];

$attribute_type = Mage::getSingleton("eav/config")->getAttribute('catalog_product', $attribute)->getFrontendInput();

if (strstr($attribute_type, 'multiselect')){

$attribute_values = implode('|', $value);

$result_patched = '`cpf`.`'.$attribute.'` REGEXP "(^|,)'.$attribute_values.'(,|$)"';

return $result_patched; // return SQL for avoid future SqlBuider processing

}

// END CORE PATCH

switch ($operator) {

case '!=':

case '>=':

case '<=':

case '>':

case '<':

$selectOperator = sprintf('%s?', $operator);

break;

case '{}':

case '!{}':

// CORE PATCH: Commented out the category_id matching since any array should use this select operator.

if (/*preg_match('/^.*(category_id)$/', $field) &&*/ is_array($value)) {

$selectOperator = ' IN (?)';

} else {

$selectOperator = ' LIKE ?';

$value = '%' . $value . '%';

}

if (substr($operator, 0, 1) == '!') {

$selectOperator = ' NOT' . $selectOperator;

}

break;

case '()':

$selectOperator = ' IN(?)';

break;

case '!()':

$selectOperator = ' NOT IN(?)';

break;

default:

$selectOperator = '=?';

break;

}

$field = $this->_adapter->quoteIdentifier($field);

if (is_array($value) && in_array($operator, array('==', '!=', '>=', '<=', '>', '<'))) {

$results = array();

foreach ($value as $v) {

$results[] = $this->_adapter->quoteInto("{$field}{$selectOperator}", $v);

}

$result = implode(' AND ', $results);

} else {

$result = $this->_adapter->quoteInto("{$field}{$selectOperator}", $value);

}

return $result;

}

File : app/code/local/Mage/CatalogRule/Model/Action/Index/Refresh.php

/**
     * Prepare index select
     *
     * @param Mage_Core_Model_Website $website
     * @param $time
     * @return Varien_Db_Select
     */
    protected function _prepareIndexSelect(Mage_Core_Model_Website $website, $time)
    {
        $nA = $this->_connection->quote('N/A');
        $this->_connection->query('SET @price := 0');
        $this->_connection->query('SET @group_id := NULL');
        $this->_connection->query('SET @action_stop := NULL');
        $indexSelect = $this->_connection->select()
            ->from(array('cppt' => $this->_getTemporaryTable()), array())
            ->order(array('cppt.grouped_id', 'cppt.sort_order', 'cppt.rule_product_id'))
            ->columns(
                array(
                    'customer_group_id' => 'cppt.customer_group_id',
                    'product_id'        => 'cppt.product_id',
                    'rule_price'        =>  $this->_calculatePrice(),
                    'latest_start_date' => 'cppt.from_date',
                    'earliest_end_date' => 'cppt.to_date',
                    new Zend_Db_Expr(
                        $this->_connection->getCaseSql(
                            '',
                            array(
                                $this->_connection->getIfNullSql(
                                    new Zend_Db_Expr('@group_id'),
                                    $nA
                                ) . ' != cppt.grouped_id' => new Zend_Db_Expr('@action_stop := cppt.action_stop'),
                                $this->_connection->getIfNullSql(
                                    new Zend_Db_Expr('@group_id'),
                                    $nA
                                ) . ' = cppt.grouped_id' => '@action_stop := '
                                    . $this->_connection->getIfNullSql(
                                        new Zend_Db_Expr('@action_stop'),
                                        new Zend_Db_Expr(0)
                                    ) . ' + cppt.action_stop',
                            )
                        )
                    ),
                    new Zend_Db_Expr('@group_id := cppt.grouped_id'),
                    'from_time'         => 'cppt.from_time',
                    'to_time'           => 'cppt.to_time'
                )
            );
        $select = $this->_connection->select()
            ->from($indexSelect, array())
            ->joinInner(
                array(
                    'dates' => $this->_connection->select()->union(
                        array(
                            new Zend_Db_Expr(
                                'SELECT ' . $this->_connection->getDateAddSql(
                                    $this->_connection->fromUnixtime($time),
                                    -1,
                                    Varien_Db_Adapter_Interface::INTERVAL_DAY
                                ) . ' AS rule_date'
                            ),
                            new Zend_Db_Expr('SELECT ' . $this->_connection->fromUnixtime($time) . ' AS rule_date'),
                            new Zend_Db_Expr(
                                'SELECT ' . $this->_connection->getDateAddSql(
                                    $this->_connection->fromUnixtime($time),
                                    1,
                                    Varien_Db_Adapter_Interface::INTERVAL_DAY
                                ) . ' AS rule_date'
                            ),
                        )
                    )
                ),
                '1=1',
                array()
            )
            ->columns(
                array(
                    'rule_product_price_id' => new Zend_Db_Expr('NULL'),
                    'rule_date'             => 'dates.rule_date',
                    'customer_group_id'     => 'customer_group_id',
                    'product_id'            => 'product_id',
                    'rule_price'            => 'rule_price', /* CORE PATCH: change MIN(rule_price) to rule_price for select from rule_product_price_tmp table , with MIN catalog price rule calculation totally wrong after core patch for fix catalog rules on Mage/Rule/Model/Resource/Rule/Condition/SqlBuilder.php */
                    'website_id'            => new Zend_Db_Expr($website->getId()),
                    'latest_start_date'     => 'latest_start_date',
                    'earliest_end_date'     => 'earliest_end_date',
                )
            )
            ->where(new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " >= from_time"))
            ->where(
                $this->_connection->getCheckSql(
                    new Zend_Db_Expr('to_time = 0'),
                    new Zend_Db_Expr(1),
                    new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " <= to_time")
                )
            )
            ->group(array('customer_group_id', 'product_id', 'dates.rule_date'));
        return $select;
    }

/**

* Prepare index select

* @param Mage_Core_Model_Website $website

* @param $time

* @return Varien_Db_Select

protected function _prepareIndexSelect(Mage_Core_Model_Website $website, $time)

{

$nA = $this->_connection->quote('N/A');

$this->_connection->query('SET @price := 0');

$this->_connection->query('SET @group_id := NULL');

$this->_connection->query('SET @action_stop := NULL');

$indexSelect = $this->_connection->select()

->from(array('cppt' => $this->_getTemporaryTable()), array())

->order(array('cppt.grouped_id', 'cppt.sort_order', 'cppt.rule_product_id'))

->columns(

array(

'customer_group_id' => 'cppt.customer_group_id',

'product_id' => 'cppt.product_id',

'rule_price' => $this->_calculatePrice(),

'latest_start_date' => 'cppt.from_date',

'earliest_end_date' => 'cppt.to_date',

new Zend_Db_Expr(

$this->_connection->getCaseSql(

'',

array(

$this->_connection->getIfNullSql(

new Zend_Db_Expr('@group_id'),

$nA

) . ' != cppt.grouped_id' => new Zend_Db_Expr('@action_stop := cppt.action_stop'),

$this->_connection->getIfNullSql(

new Zend_Db_Expr('@group_id'),

$nA

) . ' = cppt.grouped_id' => '@action_stop := '

. $this->_connection->getIfNullSql(

new Zend_Db_Expr('@action_stop'),

new Zend_Db_Expr(0)

) . ' + cppt.action_stop',

)

new Zend_Db_Expr('@group_id := cppt.grouped_id'),

'from_time' => 'cppt.from_time',

'to_time' => 'cppt.to_time'

)

);

$select = $this->_connection->select()

->from($indexSelect, array())

->joinInner(

array(

'dates' => $this->_connection->select()->union(

array(

new Zend_Db_Expr(

'SELECT ' . $this->_connection->getDateAddSql(

$this->_connection->fromUnixtime($time),

-1,

Varien_Db_Adapter_Interface::INTERVAL_DAY

) . ' AS rule_date'

new Zend_Db_Expr('SELECT ' . $this->_connection->fromUnixtime($time) . ' AS rule_date'),

new Zend_Db_Expr(

'SELECT ' . $this->_connection->getDateAddSql(

$this->_connection->fromUnixtime($time),

Varien_Db_Adapter_Interface::INTERVAL_DAY

) . ' AS rule_date'

)

'1=1',

array()

)

->columns(

array(

'rule_product_price_id' => new Zend_Db_Expr('NULL'),

'rule_date' => 'dates.rule_date',

'customer_group_id' => 'customer_group_id',

'product_id' => 'product_id',

'rule_price' => 'rule_price', /* CORE PATCH: change MIN(rule_price) to rule_price for select from rule_product_price_tmp table , with MIN catalog price rule calculation totally wrong after core patch for fix catalog rules on Mage/Rule/Model/Resource/Rule/Condition/SqlBuilder.php */

'website_id' => new Zend_Db_Expr($website->getId()),

'latest_start_date' => 'latest_start_date',

'earliest_end_date' => 'earliest_end_date',

)

->where(new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " >= from_time"))

->where(

$this->_connection->getCheckSql(

new Zend_Db_Expr('to_time = 0'),

new Zend_Db_Expr(1),

new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " <= to_time")

)

->group(array('customer_group_id', 'product_id', 'dates.rule_date'));

return $select;

}

← Previous Post Next Post →

Samples of catalog rules issues on Magento CE 1.9.1 & EE 1.14.1.0

Source code of patch for catalog price rules

Related posts