Price Filter Issue in Search criteria not returning correct product

[wittyvishwas]

Preconditions

1. Magento 2.4-develop installed with sample data
2. Create admin integration token that would be used to hit the products API

Steps to reproduce

1. Hit the products REST api using any client with searchCriteria Category id as 23 and price filter as price between $40 and $50 or between $70 and $80. Here is the URL that i built

rest/V1/products?searchCriteria[filter_groups][1][filters][0][field]=category_id&searchCriteria[filter_groups][1][filters][0][value]=23&searchCriteria[filter_groups][1][filters][0][condition_type]=eq&searchCriteria[filter_groups][2][filters][0][field]=price&searchCriteria[filter_groups][2][filters][0][value]=40&searchCriteria[filter_groups][2][filters][0][condition_type]=from&searchCriteria[filter_groups][3][filters][0][field]=price&searchCriteria[filter_groups][3][filters][0][value]=50&searchCriteria[filter_groups][3][filters][0][condition_type]=to&searchCriteria[filter_groups][2][filters][1][field]=price&searchCriteria[filter_groups][2][filters][1][value]=70&searchCriteria[filter_groups][2][filters][1][condition_type]=from&searchCriteria[filter_groups][3][filters][1][field]=price&searchCriteria[filter_groups][3][filters][1][value]=80&searchCriteria[filter_groups][3][filters][1][condition_type]=to

2. Check the response of the Call

Expected result

1. We should receive products that are in Jacket Category ( i.e id 23) which have price lie between $40 and $50 or $70 and $80

Actual result

1. Products with price $69 appear in result

When i tried debugging it to a query level where the query is created (Magento/Catalog/Model/ProductRepository in getList)

The actual query that is created was (Which roughly translates to products between $40 and $80)
LEFT JOIN catalog_product_entity_decimalASat_price ON (at_price.entity_id=e.entity_id) AND (at_price.attribute_id = '77') AND (at_price.store_id= 1) WHERE (e.entity_id IN((SELECTcat.product_idFROMcatalog_category_productAScat WHERE (cat.category_id IN('23'))))) AND ((IF(at_price.value_id > 0, at_price.value, at_price_default.value) >= '40') OR (IF(at_price.value_id > 0, at_price.value, at_price_default.value) >= '70')) AND ((IF(at_price.value_id > 0, at_price.value, at_price_default.value) <= '50') OR (IF(at_price.value_id > 0, at_price.value, at_price_default.value) <= '80'))

but it should created like this (Which roughly translates to products between $40 and $50 or $70 and $80)

LEFT JOIN catalog_product_entity_decimalASat_price ON (at_price.entity_id=e.entity_id) AND (at_price.attribute_id = '77') AND (at_price.store_id= 1) WHERE (e.entity_id IN((SELECTcat.product_idFROMcatalog_category_productAScat WHERE (cat.category_id IN('23'))))) AND ((IF(at_price.value_id > 0, at_price.value, at_price_default.value) >= '40') AND (IF(at_price.value_id > 0, at_price.value, at_price_default.value) <= '50')) OR ((IF(at_price.value_id > 0, at_price.value, at_price_default.value) >= '70') AND (IF(at_price.value_id > 0, at_price.value, at_price_default.value) <= '80'))

Hi @wittyvishwasthank you for your report.
We've acknowledged the issue and added to our backlog.