When a category is created whilst in a store scope, and a value is set against an attribute which has is_required set to false, and has a scope more specific than global, then a value is not set against the global scope, resulting in the store scope having a value, but the global scope having no value. This also has the effect of causing the flat category table for the store in question to have a null value for the attribute, even in the store scope that it is set in.
On this install, the attribute in question is display_as_link:
The value is stored as an int, here is what the EAV table looks like after save. You can see that a value exists only on the store scope:
Causing the index table for said store to be incorrect (you can see for the category test3, which is category 314, the value is NULL, not 1 as it should be):
You can see that if we add a value manually at the global scope, then the index table is correct:
Now if I remove this value, and instead set is_required on display_as_link to true, and create a new category in store 2, you can see a value is created at the global scope as well:
It feels to me as if a value should always exist in the global scope if one exists in the store scope, so I would say this is not a problem with the indexer, but with the EAV saving logic.
This is on Magento 1.7.0.2 - it might have been fixed since then.
