[BUG] Search query and filters produce wrong results

[martynaskre]

⚡ PowerGrid - Pull Request

Motivation

• [x] Bug fix
• [ ] Enhancement
• [ ] New feature
• [ ] Breaking change

Description

This Pull Request tackles an issue outlined below. Suppose we have a generated query as follows:

select * from `products` where ((`products`.`reference` LIKE '%700%' or `products`.`ean13` LIKE '%700%') or (exists (select * from `product_translations` where `products`.`id` = `product_translations`.`product_id` and `name` LIKE '%700%')) and (`active` = 1)) and `products`.`deleted_at` is null order by `id` asc limit 10 offset 0

Based on the applied filters, the query is intended to search for products using the query string and then apply filtering, such as active = 1. However it was observed that the query returns products matching the search query, but the filtering is entirely ignored.

This Pull Request resolves this issue by segregating the search and filtering into different WHERE statements. The modified query now appears as follows:

select * from `products` where ((`products`.`reference` LIKE '%700%' or `products`.`ean13` LIKE '%700%') or (exists (select * from `product_translations` where `products`.`id` = `product_translations`.`product_id` and `name` LIKE '%700%'))) and ((`active` = 1)) and `products`.`deleted_at` is null order by `id` asc limit 10 offset 0

Related Issue(s): None.

Documentation

This PR requires Documentation update?

• [ ] Yes
• [x] No
• [ ] I have already submitted a Documentation pull request.

[luanfreitasdev]

Hello! Thanks for the PR, but I can't reproduce this problem and I don't know if this is happening in the latest release. Can you send a video to understand what is happening?

[martynaskre]

I will try to demonstrate the issue by converting query into boolean expression. Lets convert this query:

((`products`.`reference` LIKE '%700%' or `products`.`ean13` LIKE '%700%') or (exists (select * from `product_translations` where `products`.`id` = `product_translations`.`product_id` and `name` LIKE '%700%')) and (`active` = 1))

Let's assume that the reference is a match, the exists subquery returns false, and active is false. These assumptions result in the following boolean expression:

(true || false && (false)) = true

If active equals to true, then the following expression happens:

(true || false && (true)) = true

Upon close inspection, it becomes apparent that regardless of whether active is true or false, the row will still be returned. The only way to address this issue would be to encapsulate the filters within another subquery or separate them into a different WHERE statement. I opted for the second option as it is easier to debug, if necessary.