You cannot define a correlation name '*_idx' more than once

Question

I have a Magento CE-1.9.2.4 installation and when accessing category lists sometimes I get this error when clicking on the color filter or size filter:

You cannot define a correlation name 'size_idx' more than once

#0 /lib/Varien/Db/Select.php(281): Zend_Db_Select->_join('inner join', Array, 'size_idx.entity...', Array, NULL)
#1 /app/code/core/Zend/Db/Select.php(352): Varien_Db_Select->_join('inner join', Array, 'size_idx.entity...', Array, NULL)
#2 /app/code/core/Zend/Db/Select.php(331): Zend_Db_Select->joinInner(Array, 'size_idx.entity...', Array, NULL)
#3 /app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Attribute.php(70): Zend_Db_Select->join(Array, 'size_idx.entity...', Array)
#4 /app/code/core/Mage/Catalog/Model/Layer/Filter/Attribute.php(94): Mage_Catalog_Model_Resource_Layer_Filter_Attribute->applyFilterToCollection(Object(Mage_Catalog_Model_Layer_Filter_Attribute), '14')
#5 /app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(91): Mage_Catalog_Model_Layer_Filter_Attribute->apply(Object(Mage_Core_Controller_Request_Http), Object(Mage_Catalog_Block_Layer_Filter_Attribute))
#6 /app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(73): Mage_Catalog_Block_Layer_Filter_Abstract->_initFilter()
#7 /app/code/core/Mage/Catalog/Block/Layer/View.php(136): Mage_Catalog_Block_Layer_Filter_Abstract->init()
#8 /app/code/core/Mage/Core/Block/Abstract.php(293): Mage_Catalog_Block_Layer_View->_prepareLayout()
#9 /app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#10 /app/code/core/Mage/Core/Model/Layout.php(472): Mage_Core_Model_Layout->createBlock('catalog/layer_v...', 'catalog.leftnav')
#11 /app/code/core/Mage/Core/Model/Layout.php(239): Mage_Core_Model_Layout->addBlock('catalog/layer_v...', 'catalog.leftnav')
#12 /app/code/core/Mage/Core/Model/Layout.php(205): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#13 /app/code/core/Mage/Core/Model/Layout.php(210): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#14 /app/code/core/Mage/Core/Controller/Varien/Action.php(344): Mage_Core_Model_Layout->generateBlocks()
#15 /app/code/core/Mage/Catalog/controllers/CategoryController.php(148): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#16 /app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Catalog_CategoryController->viewAction()
#17 /app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('view')
#18 /app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#19 /app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#20 /app/Mage.php(684): Mage_Core_Model_App->run(Array)
#21 /index.php(83): Mage::run('', 'store')
#22 {main}

I have read the following posts:

• You cannot define a correlation name 'enable_googlecheckout_idx' more than once
• https://stackoverflow.com/questions/10524017/magento-layered-navigation-you-cannot-define-a-correlation-name-mycustomattrib
• https://support.amasty.com/index.php?/Knowledgebase/Article/View/12/5/you-cannot-define-a-correlation-name-_idx-more-than-once

and searched my installation for type="catalog/layer_view to see if there is more than once in the same .xml file (there isn't).

The most "strange" thing is that when I refresh cache from:

Magento Admin Panel > System > Cache Management

the error disappears!

Is there something else I must look for?

UPDATE (2016-12-08)
I have replaced the code inside the file

/app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Attribute.php

with this

/**
 * Magento
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://opensource.org/licenses/osl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@magento.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade Magento to newer
 * versions in the future. If you wish to customize Magento for your
 * needs please refer to http://www.magento.com for more information.
 *
 * @category    Mage
 * @package     Mage_Catalog
 * @copyright  Copyright (c) 2006-2016 X.commerce, Inc. and affiliates (http://www.magento.com)
 * @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 */

/**
 * Catalog Layer Attribute Filter Resource Model
 *
 * @category    Mage
 * @package     Mage_Catalog
 * @author      Magento Core Team <core@magentocommerce.com>
 */
class Mage_Catalog_Model_Resource_Layer_Filter_Attribute extends Mage_Core_Model_Resource_Db_Abstract
{
   /**
    * Initialize connection and define main table name
    *
    */
   protected function _construct()
   {
       $this->_init('catalog/product_index_eav', 'entity_id');
   }

   /**
    * Apply attribute filter to product collection
    *
    * @param Mage_Catalog_Model_Layer_Filter_Attribute $filter
    * @param int $value
    * @return Mage_Catalog_Model_Resource_Layer_Filter_Attribute
    */
   public function applyFilterToCollection($filter, $value)
   {

       $filterSingleton = FilterSingleton::singleton();

       if (!isset($filterSingleton->return)) {

           $collection = $filter->getLayer()->getProductCollection();
           $attribute  = $filter->getAttributeModel();
           $connection = $this->_getReadAdapter();
           $tableAlias = $attribute->getAttributeCode() . '_idx';
           $conditions = array(
               "{$tableAlias}.entity_id = e.entity_id",
               $connection->quoteInto("{$tableAlias}.attribute_id = ?", $attribute->getAttributeId()),
               $connection->quoteInto("{$tableAlias}.store_id = ?", $collection->getStoreId()),
               $connection->quoteInto("{$tableAlias}.value = ?", $value)
           );

           $collection->getSelect()->join(
               array($tableAlias => $this->getMainTable()),
               join(' AND ', $conditions),
               array()
           );

           $filterSingleton->return = $this;

           return $this;

       } else {
           return $filterSingleton->return;
       }
   }

   /**
    * Retrieve array with products counts per attribute option
    *
    * @param Mage_Catalog_Model_Layer_Filter_Attribute $filter
    * @return array
    */
   public function getCount($filter)
   {
       // clone select from collection with filters
       $select = clone $filter->getLayer()->getProductCollection()->getSelect();
       // reset columns, order and limitation conditions
       $select->reset(Zend_Db_Select::COLUMNS);
       $select->reset(Zend_Db_Select::ORDER);
       $select->reset(Zend_Db_Select::LIMIT_COUNT);
       $select->reset(Zend_Db_Select::LIMIT_OFFSET);

       $connection = $this->_getReadAdapter();
       $attribute  = $filter->getAttributeModel();
       $tableAlias = sprintf('%s_idx', $attribute->getAttributeCode());
       $conditions = array(
           "{$tableAlias}.entity_id = e.entity_id",
           $connection->quoteInto("{$tableAlias}.attribute_id = ?", $attribute->getAttributeId()),
           $connection->quoteInto("{$tableAlias}.store_id = ?", $filter->getStoreId()),
       );

       $select
           ->join(
               array($tableAlias => $this->getMainTable()),
               join(' AND ', $conditions),
               array('value', 'count' => new Zend_Db_Expr("COUNT({$tableAlias}.entity_id)")))
           ->group("{$tableAlias}.value");

       return $connection->fetchPairs($select);
       }
   }
   class FilterSingleton {

   static private $instance;

   public $return = null;

       private function __construct() {

   }

   static public function singleton() {
        if (!isset(self::$instance)) {
           $c = __CLASS__;
           self::$instance = new $c;
       }

       return self::$instance;
   }
}

and I don't get the error anymore, but now when selecting to filter by size (or color) I get the filter twice ( screenshot: http://imgur.com/a/JFEeR ).

Answer 1

Be sure you are not adding the same attribute twice with addAttributeToSort in any of your collections. As you already mentioned be sure none of your layout's are including the catalog/layer_view block types.

<catalog_category_layered>         
 <reference name="left">
    <block type="mymodule/catalog_layer_view" name="mymodule.navleft" after="currency" template="catalog/layer/view.phtml"/> 
 </reference> 
 </catalog_category_layered>

... and the same block type included again within another layout XML, remember that the layout can be injected by code as well so be sure to look for 3rd party or local custom code that is injecting blocks into the layout for the layered navigation.

As a last resort you can try:

• https://stackoverflow.com/a/14238169/158325

But I will definitely scour your code base as much as possible for any re setting of orders in collection or the double inclusion of layered navigation blocks within the layout XML files or possibly in 3rd party code.

Any list of 3rd party layered navigation or search modules you are using would also help in determining a fix possibly, as your stack dump doesn't show any local or 3rd party community code.

Hope this helps.

Answer 2

I can reproduce this error sometimes (but very rarely) after clearing the cache. Almost like the Cache clearing failed for whatever reason. I get this on 2 separate installations of Magento.

The only obvious thing that both stacks have in common is that they both use Varnish Cache. Because I've never had this issue with Varnish turned off.

Quick Solution

I've found that you only need to clear Layouts under the Cache to resolve it.

System > Cache Management > Layouts > Refresh

Answer 3

Failing all of the aforementioned options that you have mentioned in your question, a final approach would be worth commenting out the below line within this file:

Target File

/app/design/frontend/base/default/layout/catalog.xml

Comment Out

<reference name="left"><block type="catalog/layer_view" name="catalog.leftnav" after="currency" template="catalog/layer/view.phtml"/></reference>

Explanation

After executing Clear Cache within Magento, sometimes it can merge parts of the BASE Config.xml and THEME Config.xml. Why this is, I don't know. However, it does appear a few people are affected by this phenomenon. Commenting out this one line will ensure that the merge is incapable of happening.