EAV ResourceModel not loading default values correctly

[ochnygosch]

EAV ResourceModel not loading attribute default values correctly.

Preconditions

1. Magento 2.1.5

Steps to reproduce

1. Create a category named Testcat in "All Store Views"
2. Rename category to "Testcat Default Store" in "Default Store View"
3. Remove row in database table catalog_category_entity_varchar with attribute_id = 45 (Name) and store_id = 0
4. Recreate the deleted row, so that it gets a higher value_id then the name attribute value of the default store (store_id = 1)

Expected result

1. The name of the category in the backend for Default Store View should be displayed as "Testcat Default Store"

Actual result

1. The value of "All Store Views" is displayed as name in "Default Store View"
   

I've tracked the bug down to the file Magento\Eav\Model\ResourceModel\ReadHandler . In its execute function a sql query like the following is build:

(SELECT `t`.`value`, `t`.`attribute_id` FROM `catalog_category_entity_varchar` AS `t` WHERE (entity_id = '3') AND (`store_id` IN (1, 0)) ORDER BY `t`.`store_id` DESC)
UNION ALL(SELECT `t`.`value`, `t`.`attribute_id` FROM `catalog_category_entity_int` AS `t` WHERE (entity_id = '3') AND (`store_id` IN (1, 0)) ORDER BY `t`.`store_id` DESC)
UNION ALL(SELECT `t`.`value`, `t`.`attribute_id` FROM `catalog_category_entity_text` AS `t` WHERE (entity_id = '3') AND (`store_id` IN (1, 0)) ORDER BY `t`.`store_id` DESC)
UNION ALL(SELECT `t`.`value`, `t`.`attribute_id` FROM `catalog_category_entity_datetime` AS `t` WHERE (entity_id = '3') AND (`store_id` IN (1, 0)) ORDER BY `t`.`store_id` DESC)
UNION ALL(SELECT `t`.`value`, `t`.`attribute_id` FROM `catalog_category_entity_decimal` AS `t` WHERE (entity_id = '3') AND (`store_id` IN (1, 0)) ORDER BY `t`.`store_id` DESC)

The mysql reference manual states in 14.2.9.3 UNION Syntax:

Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows.

So the order of the returned rows is random, but the read handler uses the last occurrence of an attribute row as its final value, so if there is a store specific value assigned it has to be returned after the default value (the value of the storeId = 0). So the sql query should have just one order by statement at the end and it should sort the store_id ASCENDING not descending.

[Andrii-Deineha]

@ochnygosch Hi, unfortunately, I could not reproduce the issue as you described it. Describe in more detail about Steps 3 and 4, your removal requests in the database and how many columns to delete th attribute_id = 45 (Name) and store_id = 0(one or all). Screenshots would be helpful, too. Thank you