magento2: Catalog product list widget sort order configured in backend does not match in frontend

emphasized text<!— Please review our guidelines before adding a new issue: https://github.com/magento/magento2/wiki/Issue-reporting-guidelines Fields marked with (*) are required. Please don’t remove the template. –] When configuring products in Catalog->Category->Products in Category in the admin, the same products are not sorted by their configured order.

When setVisibility is called (Magento\CatalogWidget\Block\Product\ProductsList::createCollection() line 308), it sets cat*index.category*id=3.

Generated query is as followed:

 SELECT DISTINCT  `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` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index_store2` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=2 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=3 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 WHERE (((IFNULL(`e`.`entity_id`, 0) IN (SELECT `catalog_category_product`.`product_id` FROM `catalog_category_product` WHERE (category_id IN ('5244')))) )) ORDER BY `e`.`created*at` desc

Preconditions (*)

[!— Provide the exact Magento version (example: 2.3.2) and any important information on the environment where bug is reproducible. –]

1. Magento 2.3.4
2. PHP 7.2

Steps to reproduce (*)

[!— Important: Provide a set of clear steps to reproduce this bug. We can not provide support without clear instructions on how to reproduce. –>

1. Create category ( Catalog -> Categories -> Add Root Category)

2. Rename category to TestCategory

3. Add 3 or more products to Category (Catalog-> Products -> Select/Edit -> Categories -> TestCategory -> Done -> Save)

4. Open Catalog -> Categories -> TestCategory -> Products in Category

5. Set position on productX to 1, Set position on productY to 2 , Set position on productZ to 3 and Save

6. Add CMS Page (Content -> Page -> Add New Page -> PageTitle = TestPage)

7. Set Content on TestPage to the following (change the category ID; template can be chosen freely) widget type="Magento\CatalogWidget\Block\Product\ProductsList" show*pager="0" products_count="10" template="Magento_CatalogWidget::product/widget/content/home-grid.phtml" **conditions_encoded="<sup>[{{1:[type:Magento](|CatalogWidget||Model||Rule||Condition||Combine,aggregator:all,value:1,new_child:``^],1--1:[type:Magento||CatalogWidget||Model||Rule||Condition||Product,attribute:category*ids,operator:==,value:1^])"}}

8. Insert widget with widget type - Catalog Product List and condition - Category is <id>

Expected result (*)

1. productZ (position3) is first, productX (position 1) is second, productY (position 2) is last