[Issue] Sql Builder: Correctly handle multiselect value with negative conditions

[m2-assistant]

This issue is automatically created based on existing pull request: #32124: Sql Builder: Correctly handle multiselect value with negative conditions

---

Description (*)

This fixes building SQL for a multiselect attribute with a !() or !{} condition. While () and {} conditions correctly use FIND_IN_SET() (see the code immediately preceding this diff), no code was provided to handle () and {} before.

Differences in generated SQL (with property 193 ("promoties") being a multiselect):

Before:

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`, `at_turnover_group`.`value` AS `turnover_group`, `at_promoties`.`value` AS `promoties` 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 = '1' AND price_index.customer_group_id = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_turnover_group` ON (`at_turnover_group`.`entity_id` = `e`.`entity_id`) AND (`at_turnover_group`.`attribute_id` = '182') AND (`at_turnover_group`.`store_id` = 0)
 LEFT JOIN `catalog_product_entity_varchar` AS `at_promoties` ON (`at_promoties`.`entity_id` = `e`.`entity_id`) AND (`at_promoties`.`attribute_id` = '193') AND (`at_promoties`.`store_id` = 0) WHERE ((((IFNULL(`at_turnover_group`.`value`, 0) = '5688') AND(IFNULL(`at_promoties`.`value`, 0) NOT IN ('6055')) ) ));

After:

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`, `at_turnover_group`.`value` AS `turnover_group`, `at_promoties`.`value` AS `promoties` 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 = '1' AND price_index.customer_group_id = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_turnover_group` ON (`at_turnover_group`.`entity_id` = `e`.`entity_id`) AND (`at_turnover_group`.`attribute_id` = '182') AND (`at_turnover_group`.`store_id` = 0)
 LEFT JOIN `catalog_product_entity_varchar` AS `at_promoties` ON (`at_promoties`.`entity_id` = `e`.`entity_id`) AND (`at_promoties`.`attribute_id` = '193') AND (`at_promoties`.`store_id` = 0) WHERE ((((IFNULL(`at_turnover_group`.`value`, 0) = '5476') AND(IFNULL(`at_promoties`.`value`, 0) NOT IN ('6055') AND (IFNULL(FIND_IN_SET ('6055', `at_promoties`.`value`), 0) = 0)) ) ));

Related Pull Requests

Fixed Issues (if relevant)

The issue does not appear to be reported to Magento's bug tracker.

Manual testing scenarios (*)

1. Create !() or !{} condition that binds to a multiselect value that has multiple properties saved (in a comma separated list)
2. Observe that it doesn't work correctly on the develop branch (e.g. it leaves out values of 6055, but not 6055,6155), but works correctly on this branch.

Questions or comments

Contribution checklist (*)

• Pull request has a meaningful description of its purpose
• All commits are accompanied by meaningful commit messages
• All new or changed code is covered with unit/integration tests (if applicable)
• All automated tests passed successfully (all builds are green)

[m2-assistant]

Hi @engcom-Hotel. 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).
• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue.
• 3. Add Area: XXXXX label to the ticket, indicating the functional areas it may be related to.
• 4. Verify that the issue is reproducible on 2.4-develop branch
  Details

  - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.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.

[engcom-Hotel]

Hello @Gymnasiast,

Thanks for the report and collaboration!

But we need more information on the issue of reproduction steps. Can you please provide us with the same in a detailed format?

Thanks