Magento 1.9 Catalog_Product_Index_Eav Table – Purpose and Usage

Let's take them one by one.

1. You should use the int values for consistency. But (as you saw) it works with varchar also. I have a hunch it works with text too.

2. "how did this dropdown attribute get created as an int backend but ended up in the catalog_product_entity_varchar". That's a complete mystery. Only the original developer can tell you that (maybe not even him). But I can tell you how is possible to reference the eav_attribute_option table. It's because the attribute options do not care about the type of the attribute. If the attribute has a source_model it calls getAllOptions from that source model and matches the value set on the product with what it finds in the result of getAllOptions.

3. I think the answer to number 2 will answer number 3 also. In addition is because PHP is a loose type programming language and 3 == "3" evaluates to true.

I found the solution in the query logs and it's surprisingly logical.

Short Answer

The product in question was a bundle product and for bundle products all attributes of the bundle selections are taken into account as well. This way you also find bundles that may contain a product with the specified options in layered navigation.

Long Answer

This query runs after the first round of indexing:

INSERT IGNORE INTO `catalog_product_index_eav_idx` SELECT `l`.`parent_id`, `i`.`attribute_id`, `i`.`store_id`, `i`.`value` FROM `catalog_product_relation` AS `l`
CROSS JOIN `core_store` AS `cs`
INNER JOIN `catalog_product_index_eav_idx` AS `i` ON l.child_id = i.entity_id AND cs.store_id = i.store_id GROUP BY `l`.`parent_id`,
`i`.`attribute_id`,
`i`.`store_id`,
`i`.`value`;

If I run the SELECT subquery and limit it to the attribute and product from above, I get the following, which are exactly my mysterious values:

mysql> SELECT `l`.`parent_id`, `i`.`attribute_id`, `i`.`store_id`, `i`.`value` FROM `catalog_product_relation` AS `l`
    ->  CROSS JOIN `core_store` AS `cs`
    ->  INNER JOIN `catalog_product_index_eav_idx` AS `i` ON l.child_id = i.entity_id AND cs.store_id = i.store_id GROUP BY `l`.`parent_id`,
    ->         `i`.`attribute_id`,
    ->         `i`.`store_id`,
    ->         `i`.`value` having l.parent_id=10515 and i.attribute_id=274;
+-----------+--------------+----------+-------+
| parent_id | attribute_id | store_id | value |
+-----------+--------------+----------+-------+
|     10515 |          274 |       12 | 99437 |
|     10515 |          274 |       13 | 99437 |
|     10515 |          274 |       14 | 99437 |
|     10515 |          274 |       15 | 99437 |
|     10515 |          274 |       16 | 99437 |
|     10515 |          274 |       17 | 99437 |
|     10515 |          274 |       18 | 99437 |
|     10515 |          274 |       19 | 99437 |
|     10515 |          274 |       20 | 99437 |
|     10515 |          274 |       21 | 99437 |
|     10515 |          274 |       22 | 99437 |
+-----------+--------------+----------+-------+
11 rows in set (0.45 sec)

The code for this query is in Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract::_prepareRelationIndex()

catalog_product_relation contains the bundle relations, so the value comes from a bundle selection of product 10515.