# Bug OP-14454 - Multi-value custom fields filtering with "is (AND)" and "is not" is affected by values in other CFs

Project: OpenProject
Status: closed
URL: https://community.openproject.org/work_packages/OP-14454

## Open points

_No work packages_

## Details

| Attribute | Value |
| --- | --- |
| Date |  |
| Priority | Medium |
| Module |  |
| Target versions | 13.4.0 |
| Observed in versions |  |
| Sprint | 13.4.0 |
| Backlog bucket |  |
| Bug found in version (deprecated) |  |
| Found in mobile app version |  |
| Follow-up feedback e-mail |  |
| Requires doc change | No |
| Accessibility | No |
| Pull preview |  |
| List |  |
| Labels |  |

## People

| Attribute | Value |
| --- | --- |
| Assignee |  |
| Accountable |  |
| Product manager |  |
| Developers |  |
| Designer |  |
| Ordered by |  |
| Signed off by |  |
| Interest by |  |

## Planning

| Attribute | Value |
| --- | --- |
| Relevance |  |

## Costs

| Attribute | Value |
| --- | --- |
| Votes |  |
| Work |  |
| Labor costs |  |

## Description

### Steps to reproduce

1.  Have two multi-value CFs, e.g. version. (Probably only the one used in the filter needs to be multi-value, but I tested with both multivals.) Let's call these "Backport version" and "Affects version/s".
2.  Let's have various versions for the project, among others 4.4.7 which will be used in the filter.
3.  Create WPs so that one has Affects versions set to 4.4.7 and the other has Backport version set to 4.4.7.
4.  Search for WPs with filter for Affects versions, is (AND) operator and 4.4.7 value.
5.  WP with Backport set to 4.4.7 is also shown. (Similarly, when "is not" is used, neither of the WPs is returned, because they both "match" + NOT.)

### What is the buggy behavior?

  - Wrong WPs with the expected value occurring in other CFs are also considered.

### What is the expected behavior?

1.  To see only the right WPs. :-)

### **Logs**

The reason is in the wrong generated SQL:

``` sql
SELECT "work_packages"."id"
FROM "work_packages" LEFT OUTER JOIN "projects" ON "projects"."id" = "work_packages"."project_id"
WHERE ((work_packages.id IN
    (SELECT work_packages.id
     FROM work_packages
              LEFT OUTER JOIN custom_values
     ON custom_values.customized_type = 'WorkPackage'
             AND custom_values.customized_id = work_packages.id
             AND custom_values.custom_field_id = 16
              JOIN custom_fields_types
     ON custom_fields_types.type_id = work_packages.type_id
             AND custom_fields_types.custom_field_id = 16
     WHERE EXISTS (SELECT 1 FROM custom_values 
     WHERE customized_type = 'WorkPackage' 
     AND customized_id = work_packages.id AND value ='108'))
    ) 
    AND (projects.id IN (7)))
AND (          work_packages.id IN -- this whole id IN section is duplicate of the one just after WHERE
    (SELECT work_packages.id
     FROM work_packages
              LEFT OUTER JOIN custom_values
     ON custom_values.customized_type = 'WorkPackage'
             AND custom_values.customized_id = work_packages.id
             AND custom_values.custom_field_id = 16
              JOIN custom_fields_types
     ON custom_fields_types.type_id = work_packages.type_id
             AND custom_fields_types.custom_field_id = 16
     WHERE EXISTS (SELECT 1 FROM custom_values WHERE customized_type = 'WorkPackage' AND customized_id = work_packages.id AND value ='108'))
    )
AND "work_packages"."id" IN (
    SELECT "work_packages"."id" 
       FROM "work_packages" 
           INNER JOIN "projects" ON "projects"."id" = "work_packages"."project_id" 
           INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id" 
               AND "enabled_modules"."name" IN ('work_package_tracking') AND "projects"."active" = TRUE 
       WHERE "projects"."active" = TRUE)
ORDER BY work_packages.id DESC
LIMIT 100 OFFSET 0
```

Here, the EXISTS check for custom\_values is missing **and custom\_field\_id = 16** (where 16 is CF id for the filtered condition). Also, strangely, the long part with **work\_packages.id IN ...** is repeated twice, but that is not causing the problem, it's just confusing the whole query.

### Screenshots and other files

The problem is also pretty obvious when compared with "is OR" filter. This is the good result:

<figure class="image op-uc-figure"><div class="op-uc-figure--content"><img class="op-uc-image" src="/attachments/93136/image.png"></div></figure>

And this is the wrong result with "is (AND)" - only one of many invalid results are marked:

<img class="op-uc-image op-uc-image_inline" src="/api/v3/attachments/93137/content">
