SQLSTATE[23000]: Integrity constraint violation when searching on frontend

Question

On my frontend when I type anything in the search box I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'position' in order clause is ambiguous, query was: SELECT `main_table`.`entity_type_id`, `main_table`.`attribute_code`, `main_table`.`attribute_model`, `main_table`.`backend_model`, `main_table`.`backend_type`, `main_table`.`backend_table`, `main_table`.`frontend_model`, `main_table`.`frontend_input`, `main_table`.`frontend_input_renderer`, `main_table`.`frontend_label`, `main_table`.`frontend_class`, `main_table`.`source_model`, `main_table`.`is_global`, `main_table`.`is_visible`, `main_table`.`is_required`, `main_table`.`is_user_defined`, `main_table`.`default_value`, `main_table`.`is_searchable`, `main_table`.`is_filterable`, `main_table`.`is_comparable`, `main_table`.`is_visible_on_front`, `main_table`.`is_html_allowed_on_front`, `main_table`.`is_unique`, `main_table`.`is_configurable`, `main_table`.`apply_to`, `main_table`.`position`, `main_table`.`note`, `main_table`.`is_visible_in_advanced_search`, `main_table`.`is_used_for_price_rules`, `main_table`.`is_filterable_in_search`, `main_table`.`used_in_product_listing`, `main_table`.`used_for_sort_by`, `additional_table`.*, `entity_attribute`.`attribute_id`, IFNULL(al.value, main_table.frontend_label) AS `store_label` FROM `eav_attribute` AS `main_table`
 INNER JOIN `catalog_eav_attribute` AS `additional_table` ON additional_table.attribute_id = main_table.attribute_id
 INNER JOIN `eav_entity_attribute` AS `entity_attribute` ON entity_attribute.attribute_id = main_table.attribute_id
 LEFT JOIN `eav_attribute_label` AS `al` ON al.attribute_id = main_table.attribute_id AND al.store_id = 1 WHERE (main_table.entity_type_id = 4) AND (`entity_attribute`.`attribute_set_id` IN('4')) AND (`additional_table`.`is_filterable` > 0) GROUP BY `entity_attribute`.`attribute_id` ORDER BY position ASC

Now, to overcome this I have amended the getFilterableAttributes() function found in ~/app/code/core/Mage/Catalog/Model/Layer.php to:

     public function getFilterableAttributes()
    {
//        $entity = Mage::getSingleton('eav/config')
//            ->getEntityType('catalog_product');

        $setIds = $this->_getSetIds();
        if (!$setIds) {
            return array();
        }
        /** @var $collection Mage_Catalog_Model_Resource_Product_Attribute_Collection */
        $collection = Mage::getResourceModel('catalog/product_attribute_collection');
        $collection
            ->setItemObjectClass('catalog/resource_eav_attribute')
            ->setAttributeSetFilter($setIds)
            ->addStoreLabel(Mage::app()->getStore()->getId())
            ->setOrder('main_table.position', 'ASC');
        $collection = $this->_prepareAttributeCollection($collection);
        $collection->load();

        return $collection;
    }

This fixes the issue, but now I want to correct the actual problem, I get the error is in laymen terms saying that the position column is present more than once in the query so doesn't know which once to select. But, I cannot see why this is happening. Running the query direct on the database I am able to see position is in the result twice. Would this indicate my database has the position as a duplicate?

Investigating further I found an anomaly, the eav_attribute table seems incorrect, as on a new install I have 17 fields and on my current db I have 33:

New install eav_attribute structure:

Normal installs eav_attribute structure:

Answer 1

After careful analysis I have arrived at the following: during a previous update one of the scripts was not successful ran.

In the file:

app/code/core/Mage/Catalog/sql/catalog_setup/mysql4-upgrade-0.7.73-1.4.0.0.0.php

You can see the extra fields I have are moved to a new table called catalog_eav_attributes and removed from eav_attributes.

To correct this, I ran the following alter statement on my database:

  ALTER TABLE `eav_attribute`
  DROP `frontend_input_renderer`,
  DROP `is_global`,
  DROP `is_visible`,
  DROP `is_searchable`,
  DROP `is_filterable`,
  DROP `is_comparable`,
  DROP `is_visible_on_front`,
  DROP `is_html_allowed_on_front`,
  DROP `is_configurable`,
  DROP `apply_to`,
  DROP `position`,
  DROP `is_visible_in_advanced_search`,
  DROP `is_used_for_price_rules`,
  DROP `is_filterable_in_search`,
  DROP `used_in_product_listing`,
  DROP `used_for_sort_by`;