Query does not give desired results

Question

If I use the following query...

filter = "X Y Z" AND Status in (Active, Unassigned, Submitted)

...I get 39 entries

If I use the following query...

filter = "X Y Z" AND Status in (Active, Unassigned, Submitted) AND System not in ("A B C")

...I get 13 entries, but I know for sure that there are only 3 entries that have System in ("A B C").  I would have expected 36 entries

Any thoughts on what I am doing wrong?

Trudy P Claspill · Answer

Hello @Gary Roscoe

Were you able to get the expected results based on the guidance I provide?

If so, please consider clicking on the Accept Answer button to mark your post as Solved.

If you have additional questions, please let us know.

Trudy P Claspill · Answer

Hello @Gary Roscoe

Welcome to the Atlassian community.

Of the 23 "missing" issues, is the System field empty?

When you use a criteria such as

System not in...

...the evaluation of System can be done only for the issues where System actually has a value.

If System has no value, the filter can't determine that its value is not one of the value set you specified. There is nothing in the field to be compared to the value set.

So, if you want the issues that have a value that is not in your value set and you want the issues that have no value in the System field you need to use this filter:

filter = "X Y Z" AND Status in (Active, Unassigned, Submitted) AND (System not in ("A B C") or System is EMPTY)