Open
Description
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 (*)
- Create !() or !{} condition that binds to a multiselect value that has multiple properties saved (in a comma separated list)
- Observe that it doesn't work correctly on the
developbranch (e.g. it leaves out values of6055, but not6055,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)