M2.2.6 : "Order by price" not working in product listing

[kanduvisla]

I'm running into the issue on a Magento 2.2.6 installation the "order by price" option for a product listing is not working. Neither ascending or descending.

Preconditions

1. M2.2.6

Steps to reproduce

I'm not sure if it is part of the conditions to reproduce the problem but:

1. Some products have special prices with a from- and to-date or only a from-date, but most of them are regular prices.
2. Products have fixed product taxes.

Expected result

As a customer, If I order a product listing on price, the products should be ordered on price.

Actual result

They aren't

Own research

If I look at the query that is eventually created by \Magento\Catalog\Block\Product\ListProduct::initializeProductCollection(), I get the following result:

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`, 
       `stock_status_index`.`stock_status` AS `is_salable` 
FROM   `catalog_product_entity` AS `e` 
       INNER JOIN `catalog_category_product_index_store1` 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 = '26' 
       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 = '1' 
       LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` 
              ON e.entity_id = stock_status_index.product_id 
                 AND stock_status_index.website_id = 0 
                 AND stock_status_index.stock_id = 1 
ORDER  BY `price_index`.`min_price` ASC, 
          `e`.`entity_id` DESC 
LIMIT  12 

The main thing I notice here is that it gets ordered by price_index.min_price. But if I look at my price index, min_price is set to 0 everywhere, except for the products that have a special price set.

This caused me to look at the catalog_product_price_index-table where I saw the same: almost all products have a min_price of 0, except the products that have special price set.

A bin/magento indexer:reindex or a re-save of the product does not change the price index.

So the problem might be the product listing (should it perhaps use a different column from the price index?) or should the price index have a min_price of at least the default price for regular products?

[magento-engcom-team]

Hi @kanduvisla. 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-engcom-team give me $VERSION instance

where $VERSION is version tags (starting from 2.2.0+) or develop branches (for example: 2.3-develop).
For more details, please, review the Magento Contributor Assistant documentation.

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

• yes
• no

[magento-engcom-team]

Hi @engcom-backlog-nazar. 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-engcom-team 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. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

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

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

[kanduvisla]

I've did some more research and I found the following to trigger this bug:

• If you have a special date price from in the past, and your price field is empty, the indexer will set min_price to 0.0000.
• If you have a special date price to in the future, and your price field is empty, the indexer will set min_price to 0.0000.
• I haven't tested it but possibly if you define a valid from/to range, this will also trigger this issue.

So if you want to reproduce this issue:

• Create some products with different prices.
• Give them no special price (empty field), but give them a special price from date in the past and/or a special price to date in the future.
• Run the price reindexer.
• The products that have special price set to (empty field), are now set to 0.000 as min_price, resulting in a wrongly price sorting.

So the bug is probably in the indexing process.

I also tested with a special price of 0.0000 euro without any from/to date, and this also triggered the bug, although I'm not sure if this is the same bug: in my product listing and on the product detail page the regular price was shown (not 0.00 euro), but if I added the product to the cart the special (0.00) price was used. So this was kind of misleading because I did not notice that the products had a special price of 0.00 euro, until I looked at the extremely low price of my cart.

[xnhinzkyx]

I encountered this same issue.

[dudzio12]

Same for me.

Do you have any temporary fix for indexer?

[gediminaskv]

@dudzio12 https://magento.stackexchange.com/a/205894/46716

[kanduvisla]

If I look at the price indexing process, I see the following in \Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\Query\BaseFinalPrice::getQuery():

$select->columns(
    [
        //orig_price in catalog_product_index_price_final_tmp
        'price' => $connection->getIfNullSql($price, 0),
        //price in catalog_product_index_price_final_tmp
        'final_price' => $connection->getIfNullSql($finalPrice, 0),
        'min_price' => $connection->getIfNullSql($finalPrice, 0),
       'max_price' => $connection->getIfNullSql($finalPrice, 0),
        'tier_price' => $tierPrice,
    ]
);

If I understand this correctly, the final_price, min_price and max_price are set to a default value of 0 if $finalPrice is NULL. Shouldn't the default value be $price if this is the case?

$select->columns(
    [
        //orig_price in catalog_product_index_price_final_tmp
        'price' => $connection->getIfNullSql($price, 0),
        //price in catalog_product_index_price_final_tmp
        'final_price' => $connection->getIfNullSql($finalPrice, $price),
        'min_price' => $connection->getIfNullSql($finalPrice, $price),
       'max_price' => $connection->getIfNullSql($finalPrice, $price),
        'tier_price' => $tierPrice,
    ]
);

I did a very small local test, only with simple products, but this seems to fix the issue in my case. But then again: IMHO price indexing is one of the trickiest things in Magento and I don't feel comfortable by tinkering with it. Can anyone shine a bit more light on this?

[kanduvisla]

I found the core of the problem: It's a mixup between AND and OR-statements in the building of the SQL-query. And even better: I also found that this has been fixed in the upcoming 2.3-branch:

14ab8ac#diff-5a30a751305f50e9d7bbd8a2576a2b46

I'll backport this issue to the 2.2-dev branch as well and send a PR.

[kanduvisla]

I've created a PR for this issue for the 2.2 branch:

#18737