Magento 1 Admin Product Grid Column Value Disappears on Name Search

After digging very deep into Magento code and getting my hands dirty as I expected, I've found the root cause of the problem.

When the collection is loaded (implicitely via getFirstItem in @Simon test script), the _loadAttributes method from Mage_Eav_Model_Entity_Collection_Abstract is triggered.

The goal of this method is to load the attributes we've requested via the addAttributeToSelect method.

Here is a quick summary of the method code flow:

• Get the attributes type and attributes corresponding tables for each attributes to be selected (in our test script it would be int / catalog_product_entity_id and decimal / catalog_product_entity_decimal )
• Generate one SQL query per distinct attributes table (in our test, there is two different SQL queries : one for price attribute and one for the tax_class_id attribute)
• Finally it loops through the select groups previously generated and for each select group, proceed to an UNION ALL before setting the corresponding values to the entity.

That very last part of the code is very important, as this is where the bug is located (if you did not guess until now, it is a Magento bug).

Ready for some reading ?

So let's look at the code now, in our _loadAttributes method, we have the following code:

$selectGroups = Mage::getResourceHelper('eav')->getLoadAttributesSelectGroups($selects);
foreach ($selectGroups as $selects) {
    if (!empty($selects)) {
        try {
            $select = implode(' UNION ALL ', $selects);
            $values = $this->getConnection()->fetchAll($select);
        } catch (Exception $e) {
            Mage::printException($e, $select);
            $this->printLogQuery(true, true, $select);
            throw $e;
        }

        foreach ($values as $value) {
            $this->_setItemAttributeValue($value);
        }
    }
}

Problem is, right before the loop, the getLoadAttributesSelectGroups method from Mage_Eav_Model_Resource_Helper_Mysql4 looks like this:

public function getLoadAttributesSelectGroups($selects)
{
    $mainGroup  = array();
    foreach ($selects as $eavType => $selectGroup) {
        $mainGroup = array_merge($mainGroup, $selectGroup);
    }
    return array($mainGroup);
}

Did you spot the return statement ? We return an array of arrays. So the loop in our _loadAttributes method is never really used as there will always be one and only item (containing arrays) in the $selectGroups variable.

As this variable is not properly set, we keep going and reach the heart of the bug which is causing int being converted to decimals:

$select = implode(' UNION ALL ', $selects);

So our $selects variable contains two selects from two different tables of two different types (int and decimal).

What does the MySQL documentation has to tell us about this:

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements.

So, implicitely, MySQL converts our int values to decimal values as it is not possible to convert the other way around.

And that only happens if you try to select decimal and int attributes, as if you select varchar attributes for instance, the int and decimal results will be converted to varchar and the problem disappears.

So how do I fix that ?

First, we need to find what is buggy:

• Is it the _loadAttributes method ?
• Is it the getLoadAttributesSelectGroups method ?

Well, I have found only one other reference to the getLoadAttributesSelectGroups method, it is under the _loadModelAttributes of the Mage_Eav_Model_Entity_Abstract model and it looks like this:

$selectGroups = Mage::getResourceHelper('eav')->getLoadAttributesSelectGroups($selects);
foreach ($selectGroups as $selects) {
    if (!empty($selects)) {
        $select = $this->_prepareLoadSelect($selects);
        $values = $this->_getReadAdapter()->fetchAll($select);
        foreach ($values as $valueRow) {
            $this->_setAttributeValue($object, $valueRow);
        }
    }
}

So in Magento developers mind, that function should definitely be returning an array with several entries as everytime this function is called, a foreach statement is used to loop through it.

So I'll give you the fix I've applied:

In getLoadAttributesSelectGroups method of Mage_Eav_Model_Resource_Helper_Mysql4, replace:

return array($mainGroup);

With:

return $mainGroup;

In _loadAttributes method of Mage_Eav_Model_Entity_Collection_Abstract, replace:

$select = implode(' UNION ALL ', $selects);
$values = $this->getConnection()->fetchAll($select);

With:

if (is_array($selects)) {
    $select = implode(' UNION ALL ', $selects);
} else {
    $select = $selects;
}
$values = $this->getConnection()->fetchAll($select);

In the _loadModelAttribures of the Mage_Eav_Model_Entity_Abstract, replace:

$select = $this->_prepareLoadSelect($selects);
$values = $this->_getReadAdapter()->fetchAll($select);

With:

if (is_array($selects)) {
    $select = $this->_prepareLoadSelect($selects);
} else {
    $select = $selects;
}
$values = $this->_getReadAdapter()->fetchAll($select);

Well done you made it to the end and so did I.

Edit: regarding the bug on Magento 2, I've created a Pull Request that can be found here: https://github.com/magento/magento2/pull/3705 (related issue: https://github.com/magento/magento2/issues/3704)

Edit 2: regarding the bug on Magento 1, I've reported the bug you can find the report here: https://www.magentocommerce.com/bug-tracking/issue/index/id/1346