Fix status filter dropping matching items

What does this MR do and why?

Fixes a bug where filtering work items by a custom status name or id could return no results even though matching items exist and display that status correctly. On a board, a card dropped into a status list shows on drop, then disappears on refresh when the list re-queries.

Root cause: ee/app/finders/work_items/status_filter.rb, in apply_mapping_exclusion_condition, used where.not(column: value) to exclude items that a status mapping would move away from the filtered status. The current status of a work item lives in one of two nullable columns on work_item_current_statuses: custom_status_id or system_defined_status_id. When the exclusion negated the column the item does not use (which is NULL), SQL three-valued logic caused the predicate to evaluate to NULL, and WHERE drops NULL rows. Items whose status lived in the other column were wrongly excluded whenever their work item type matched a conflicting mapping.

This only surfaces in namespaces with a specific mapping history (a status converted from a system-defined status that maps into the filtered status for one work item type and away from it for another), which is why it could not be reproduced in a fresh namespace.

Fix: Replace the NULL-unsafe NOT (work_item_type_id = X AND column = value) with an inline, NULL-safe condition (work_item_type_id != X OR column IS NULL OR column != value) evaluated on the work_item_current_statuses row the query already joins. Rows whose status column is NULL are kept, so matching items are no longer dropped. Genuinely mapped-away items are still excluded, and the change adds no subqueries and no new index.

Database

This MR only changes the part of the status filter that excludes items a mapping moves away from the filtered status.

What changed

• The old condition could drop items that really match the status, because NOT (... AND column = value) becomes NULL in SQL when that column is empty, and WHERE throws NULL rows away. The new condition checks for NULL so those items stay.
• It is still one query, with no subqueries, and needs no new index. The check runs on the work_item_current_statuses row the query already joins.

Query plans

I can't share Database Lab plans for this one. Work item statuses only exist on root groups, and this case needs a specific setup (several lifecycles with custom statuses and mappings across work item types). I don't have Maintainer access to a production group where I could set that up and run the scenario. The change only tweaks a condition on already-joined data, so it adds no subqueries or indexes.

Before

Click to expand

SELECT issues.*
FROM issues
INNER JOIN projects ON projects.id = issues.project_id
LEFT OUTER JOIN work_item_current_statuses ON work_item_current_statuses.work_item_id = issues.id
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (
        EXISTS (
          SELECT 1 FROM project_authorizations
          WHERE project_authorizations.user_id = 1
            AND project_authorizations.project_id = projects.id
        )
        OR projects.visibility_level IN (0, 10, 20)
      )
  AND (project_features.issues_access_level > 0 OR project_features.issues_access_level IS NULL)
  AND projects.id = 27
  AND issues.work_item_type_id IN (2, 1, 5, 9)
  AND (
        work_item_current_statuses.custom_status_id IS NOT NULL AND work_item_current_statuses.custom_status_id = 39
        OR issues.work_item_type_id = 1001 AND (
             work_item_current_statuses.custom_status_id IS NOT NULL AND work_item_current_statuses.custom_status_id = 35
             OR work_item_current_statuses.system_defined_status_id IS NOT NULL AND work_item_current_statuses.system_defined_status_id = 2
           )
        OR issues.work_item_type_id = 1 AND (
             work_item_current_statuses.custom_status_id IS NOT NULL AND work_item_current_statuses.custom_status_id = 35
             OR work_item_current_statuses.system_defined_status_id IS NOT NULL AND work_item_current_statuses.system_defined_status_id = 2
           )
      )
  -- NULL-unsafe exclusion (this pair is emitted 4 times); drops rows where the column is NULL:
  AND NOT (issues.work_item_type_id = 5 AND work_item_current_statuses.custom_status_id = 35)
  AND NOT (issues.work_item_type_id = 5 AND work_item_current_statuses.system_defined_status_id = 2)
ORDER BY issues.created_at DESC, issues.id DESC
LIMIT 21

After

Click to expand

SELECT issues.*
FROM issues
INNER JOIN projects ON projects.id = issues.project_id
LEFT OUTER JOIN work_item_current_statuses ON work_item_current_statuses.work_item_id = issues.id
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (
        EXISTS (
          SELECT 1 FROM project_authorizations
          WHERE project_authorizations.user_id = 1
            AND project_authorizations.project_id = projects.id
        )
        OR projects.visibility_level IN (0, 10, 20)
      )
  AND (project_features.issues_access_level > 0 OR project_features.issues_access_level IS NULL)
  AND projects.id = 27
  AND issues.work_item_type_id IN (2, 1, 5, 9)
  AND (
        work_item_current_statuses.custom_status_id IS NOT NULL AND work_item_current_statuses.custom_status_id = 39
        OR issues.work_item_type_id = 1001 AND (
             work_item_current_statuses.custom_status_id IS NOT NULL AND work_item_current_statuses.custom_status_id = 35
             OR work_item_current_statuses.system_defined_status_id IS NOT NULL AND work_item_current_statuses.system_defined_status_id = 2
           )
        OR issues.work_item_type_id = 1 AND (
             work_item_current_statuses.custom_status_id IS NOT NULL AND work_item_current_statuses.custom_status_id = 35
             OR work_item_current_statuses.system_defined_status_id IS NOT NULL AND work_item_current_statuses.system_defined_status_id = 2
           )
      )
  -- NULL-safe exclusion (this pair is emitted 4 times, unchanged from master):
  AND (
        (issues.work_item_type_id != 5 OR work_item_current_statuses.custom_status_id IS NULL)
        OR work_item_current_statuses.custom_status_id != 35
      )
  AND (
        (issues.work_item_type_id != 5 OR work_item_current_statuses.system_defined_status_id IS NULL)
        OR work_item_current_statuses.system_defined_status_id != 2
      )
ORDER BY issues.created_at DESC, issues.id DESC
LIMIT 21

References

• https://gitlab.com/gitlab-com/request-for-help/-/work_items/5156

Screenshots or screen recordings

Scenario	Before	After
Work items list filtering
Board filtering

How to set up and validate locally

Click to expand

Work items list

1. Create a top-level group, a subgroup, and a project under the subgroup. The work items live in the project.
2. On the top-level group, go to Settings > Work items (custom statuses and lifecycles live on the root group).
3. On the Default lifecycle, Edit statuses, add a status named Triage under the Triage category, and save. This converts the group to custom statuses (In progress now carries converted_from_system_defined_status_identifier = 2).
4. Create lifecycle and move Task onto it via Change lifecycle using the identity mapping. Now Default is used by Issue and Task lifecycle by Task. Both contain Triage, To do, and In progress.
5. On the Default lifecycle, Edit statuses, remove In progress, remap to Triage. This creates In progress -> Triage for Issue.
6. On the Task lifecycle, Edit statuses, remove In progress, remap to To do. This creates In progress -> To do for Task. (Both targets are open statuses, which the app requires.)
7. In the project, create Item 1 (a Task) and Item 2 (an Issue), both set to Triage.
8. Open the project's work items list and filter by Status = Triage.
   • Before the fix: only Item 2 (Issue) shows, Item 1 (Task) is missing.
   • After the fix: both Item 1 and Item 2 show.

Board

1. Enable the board tasks flag: Feature.enable(:work_item_tasks_on_boards).
2. On a Task board with a Triage list, drag Item 1 into it. Before the fix the card appears on drop then disappears on refresh. After the fix it stays.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.