Eav sort order by attribute option_id

[tnsezer]

Preconditions (*)

1. Magento 2.3.2
   2.Php 7.2

Steps to reproduce (*)

1. We set an attribute for a grouped list to order products by attributes
   https://ibb.co/fQCNbVy

But the query orders it by option_id of eav_attribute_option_value table. but it must be ordered by sort_order of eav_attribute_option table. but it doesn't add into the join.

the query is produced by \Magento\Eav\Model\Entity\Attribute\Source\Table::addValueSortToCollection method.

Expected result (*)

It must be ordered by sort_order of eav_attribute_option table

Actual result (*)

It is ordered by option_id of eav_attribute_option_value table to check the query output below

SELECT `e`.*, `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(a5637144578_option_value_t2.value_id IS NULL, a5637144578_option_value_t1.option_id, a5637144578_option_value_t2.option_id) AS `a5637144578`, 
IF(a5637144609_option_value_t2.value_id IS NULL, a5637144609_option_value_t1.option_id, a5637144609_option_value_t2.option_id) AS `a5637144609`, IF(a5637144582_option_value_t2.value_id IS NULL, a5637144582_option_value_t1.option_id, a5637144582_option_value_t2.option_id) AS `a5637144582`, IF(a5637144596_option_value_t2.value_id IS NULL, a5637144596_option_value_t1.option_id, a5637144596_option_value_t2.option_id) AS `a5637144596`, IF(a5637144607_option_value_t2.value_id IS NULL, a5637144607_option_value_t1.option_id, a5637144607_option_value_t2.option_id) AS `a5637144607` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '3' AND price_index.customer_group_id = 0
 LEFT JOIN `catalog_product_entity_int` AS `a5637144578_t1` ON e.row_id=a5637144578_t1.row_id AND a5637144578_t1.attribute_id='371' AND a5637144578_t1.store_id=0
 LEFT JOIN `catalog_product_entity_int` AS `a5637144578_t2` ON e.row_id=a5637144578_t2.row_id AND a5637144578_t2.attribute_id='371' AND a5637144578_t2.store_id='4'
 LEFT JOIN `eav_attribute_option_value` AS `a5637144578_option_value_t1` ON a5637144578_option_value_t1.option_id=IF(a5637144578_t2.value_id > 0, a5637144578_t2.value, a5637144578_t1.value) AND a5637144578_option_value_t1.store_id=0
 LEFT JOIN `eav_attribute_option_value` AS `a5637144578_option_value_t2` ON a5637144578_option_value_t2.option_id=IF(a5637144578_t2.value_id > 0, a5637144578_t2.value, a5637144578_t1.value) AND a5637144578_option_value_t2.store_id=4
 LEFT JOIN `catalog_product_entity_int` AS `a5637144609_t1` ON e.row_id=a5637144609_t1.row_id AND a5637144609_t1.attribute_id='367' AND a5637144609_t1.store_id=0
 LEFT JOIN `catalog_product_entity_int` AS `a5637144609_t2` ON e.row_id=a5637144609_t2.row_id AND a5637144609_t2.attribute_id='367' AND a5637144609_t2.store_id='4'
 LEFT JOIN `eav_attribute_option_value` AS `a5637144609_option_value_t1` ON a5637144609_option_value_t1.option_id=IF(a5637144609_t2.value_id > 0, a5637144609_t2.value, a5637144609_t1.value) AND a5637144609_option_value_t1.store_id=0
 LEFT JOIN `eav_attribute_option_value` AS `a5637144609_option_value_t2` ON a5637144609_option_value_t2.option_id=IF(a5637144609_t2.value_id > 0, a5637144609_t2.value, a5637144609_t1.value) AND a5637144609_option_value_t2.store_id=4
 LEFT JOIN `catalog_product_entity_int` AS `a5637144582_t1` ON e.row_id=a5637144582_t1.row_id AND a5637144582_t1.attribute_id='366' AND a5637144582_t1.store_id=0
 LEFT JOIN `catalog_product_entity_int` AS `a5637144582_t2` ON e.row_id=a5637144582_t2.row_id AND a5637144582_t2.attribute_id='366' AND a5637144582_t2.store_id='4'
 LEFT JOIN `eav_attribute_option_value` AS `a5637144582_option_value_t1` ON a5637144582_option_value_t1.option_id=IF(a5637144582_t2.value_id > 0, a5637144582_t2.value, a5637144582_t1.value) AND a5637144582_option_value_t1.store_id=0
 LEFT JOIN `eav_attribute_option_value` AS `a5637144582_option_value_t2` ON a5637144582_option_value_t2.option_id=IF(a5637144582_t2.value_id > 0, a5637144582_t2.value, a5637144582_t1.value) AND a5637144582_option_value_t2.store_id=4
 LEFT JOIN `catalog_product_entity_int` AS `a5637144596_t1` ON e.row_id=a5637144596_t1.row_id AND a5637144596_t1.attribute_id='369' AND a5637144596_t1.store_id=0
 LEFT JOIN `catalog_product_entity_int` AS `a5637144596_t2` ON e.row_id=a5637144596_t2.row_id AND a5637144596_t2.attribute_id='369' AND a5637144596_t2.store_id='4'
 LEFT JOIN `eav_attribute_option_value` AS `a5637144596_option_value_t1` ON a5637144596_option_value_t1.option_id=IF(a5637144596_t2.value_id > 0, a5637144596_t2.value, a5637144596_t1.value) AND a5637144596_option_value_t1.store_id=0
 LEFT JOIN `eav_attribute_option_value` AS `a5637144596_option_value_t2` ON a5637144596_option_value_t2.option_id=IF(a5637144596_t2.value_id > 0, a5637144596_t2.value, a5637144596_t1.value) AND a5637144596_option_value_t2.store_id=4
 LEFT JOIN `catalog_product_entity_int` AS `a5637144607_t1` ON e.row_id=a5637144607_t1.row_id AND a5637144607_t1.attribute_id='390' AND a5637144607_t1.store_id=0
 LEFT JOIN `catalog_product_entity_int` AS `a5637144607_t2` ON e.row_id=a5637144607_t2.row_id AND a5637144607_t2.attribute_id='390' AND a5637144607_t2.store_id='4'
 LEFT JOIN `eav_attribute_option_value` AS `a5637144607_option_value_t1` ON a5637144607_option_value_t1.option_id=IF(a5637144607_t2.value_id > 0, a5637144607_t2.value, a5637144607_t1.value) AND a5637144607_option_value_t1.store_id=0
 LEFT JOIN `eav_attribute_option_value` AS `a5637144607_option_value_t2` ON a5637144607_option_value_t2.option_id=IF(a5637144607_t2.value_id > 0, a5637144607_t2.value, a5637144607_t1.value) AND a5637144607_option_value_t2.store_id=4 WHERE ((e.entity_id IN (59266, 59149, 58435, 58400, 58398, 58397, 58396, 58395, 58394, 58393, 58251, 58250, 58022, 58021, 58020, 58019, 58018, 53572, 53216, 53215, 51289, 50996, 50946, 50945, 50944, 48833, 48832, 48831, 48830, 48364, 48363, 46791, 46790, 46789, 46529, 46528, 44364, 43767, 43766, 43765, 43764, 43763, 39717, 39572, 39571, 39570, 39569, 39568, 39567, 39566, 34930))) AND (e.created_in <= 1) AND (e.updated_in > 1) ORDER BY `a5637144578` ASC, `a5637144609` ASC, `a5637144582` ASC, `a5637144596` ASC, `a5637144607` ASC

You can see the query orders like
ORDER BY a5637144578 ASC

But the data comes from option_id
IF(a5637144578_option_value_t2.value_id IS NULL, a5637144578_option_value_t1.option_id, a5637144578_option_value_t2.option_id) AS a5637144578

[m2-assistant]

Hi @tnsezer. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento give me 2.3-develop instance - upcoming 2.3.x release

For more details, please, review the Magento Contributor Assistant documentation.

@tnsezer do you confirm that you were able to reproduce the issue on vanilla Magento instance following steps to reproduce?

• yes
• no

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.

[m2-assistant]

Hi @engcom-Charlie. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 5. Add label Issue: Confirmed once verification is complete.

• 6. Make sure that automatic system confirms that report has been added to the backlog.

[VladimirZaets]

Hi @tnsezer. Thank you for your report.
The issue has been fixed in #24360 by @tnsezer in 2.4-develop branch
Related commit(s):

• 459c596a74c209b001b1afcfcde8717f46132fec
• a047aca104cb76d743bb318887f6ff7530175d5d
• 952d12e423f2a02c0ead3a7fd8cd7588a15d0760
• 400045977e2ec951e92579d7584fb2ce4ad3d9f9
• 49738bd2356e9520b696cfa0656d13885a209c30
• d0006b954311b57d70edd2bd5a1b434dcb31081a

The fix will be available with the upcoming 2.4.0 release.