I have a problem and can't find any solution since hours.
When I'm viewing a Category and click through the pages sometimes a product is showing multiple times e.g. on Page 1 and Page 2 - exactly the same article (sku).
So far I could discover this behavior with sort order: by name. I think the problem is that every execution of "ORDER BY name" returns a different subset of products because of many articles names a pretty much identical.
But I'm not sure if this is really the problem.
I deactivated all extension in community and local and reactivate the default Template ... the problem still exist (all indexes are refreshed).
Hope someone can help.
Many thanks
* Update 18.06.2013 *
Following the SQL-Query from Page 1 and Page 2
# Page 1
SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='130' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 LEFT JOIN `catalog_product_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = '65') AND `at_name_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '65') AND (`at_name`.`store_id` = 1) ORDER BY `name` asc LIMIT 5
Returning the following entity_id's
44419
36243
36235
29435
36386 [In Frontend 36330]
The Article with entity_id 36386 is "Bugatti Herren eleganter Schnürschuh" with SKU: 650243 which is also in the Category. There are 9 configurable products with this exact same name. (sku: 640735, 650243, 650244, 650245, 650246, 650248, 650249, 650250, 650251)
No idea why. The products are imported via 3rd-Party Interface. But can this be a problem for Magento/Pagination having multiple articles with the same name?
# Page 2
SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='130' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 LEFT JOIN `catalog_product_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = '65') AND `at_name_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '65') AND (`at_name`.`store_id` = 1) ORDER BY `name` asc LIMIT 5 OFFSET 5
Returning the following entity_id's
36330
36584
36354
28324
29175 [In Frontend 29183]
The Last Product in Query isn't the same as in the frontend.
* Update2 18.06.2013 *
Ok I executed the Page 1 SQL-Statement a few times now and the result differs:
Many-Times I get this: 44419 36243 36235 29435 36386
But Sometimes that: 44419 36235 36243 29435 36354
If I carry on I think more combinations would be possible. The ID's changed in that list are products with exacly the same name.
Could it be the problem that "ORDER BY name" couldn't get a unique order because name's are repeated so the order is a bit change? But if this is the problem two things are curious.
1) All articles with the same name (and in this category etc.) must listet together and not this OR that article.
2) This would be a known problem.
I hope someone can clear up this strange behaviour.
ORDER BY name
should return the same result every time. It is the same result if the names are equal. If there is no difference, the order is (I think) the insert order if not reordered. But the ordering is robust.