NOT IN operator is not returning proper results

[Arkadeep Kundu]

In our system, "Product Division" custom field is set with multiple values.

project = PSRC AND issuetype = Incident AND "Product Division" = "Services & Digital"

is returning results as expected, i.e. all tickets with Product Division = Services & Digital.

When I am using != operator,

project = PSRC AND issuetype = Incident AND "Product Division" != "Services & Digital"

it is returning only the tickets with Product Division = Unknown, where as there are multiple other product divisions.

same issue with

project = PSRC AND issuetype = Incident AND "Product Division" NOT IN ("Services & Digital")

What am I doing wrong here. My intent is to filter out all issues across product divisions but "Services & Digital".

Using Jira v7.11.0

[Dave Theodore [Coyote Creek Consulting]]

Check the docs. The NOT IN operator is intended for a list of two or more values within parenthesis, delimited by commas. By encapsulating things in double quotes, you are indicating that it is a single value.

For example:

project = PSRC AND issuetype = Incident AND "Product Division" NOT IN ("Services & Digital","Value 2",Value3) 

Should result in a valid response.

[Arkadeep Kundu]

Thanks for your prompt response Dave!

I tried project = PSRC AND issuetype = Incident AND "Product Division" NOT IN ("Services & Digital","Web") and getting empty result set now.

tried project = PSRC AND issuetype = Incident AND "Product Division" NOT IN ("Services & Digital",empty) is also returning empty result set.

Any thoughts on the != operator?

[Dave Theodore [Coyote Creek Consulting]]

The JQL is doing exactly what you are asking it to, but it's not clear what you are expecting it to do.  It sounds like you are looking for a list of results where only one of the values in the parenthesis is present?

In your top JQL statement, you are telling Jira to return results where both Services & Digital AND Web are not present. Furthermore, "empty," from your second JQL statement has a special meaning when using the IS and IS NOT operators. Essentially it means that the database table has no value set (ie: the cell contains nothing.) It is not legal when using the IN or NOT IN operators unless "empty" is an actual value in the select list, where the database table contains "empty" as a value.

If you want to see issues where either Services & Digital OR Web are not present, you will need to use something like this:

project = PSRC AND issuetype = Incident AND "Product Division" != "Services & Digital" OR "Product Division" != Web

Note: Since "Web" is a single word, the double quotes are not necessary, but they are not illegal. The double quotes are required when there is a space in the value.

[Arkadeep Kundu]

There are 25 product divisions listed in the project. In short, I am trying to list out all issues not pertaining to "Services & Digital". Hence I tried - 

project = PSRC AND issuetype = Incident AND "Product Division" != "Services & Digital"

it is returning only the tickets with Product Division = Unknown. Then I tried - 

project = PSRC AND issuetype = Incident AND "Product Division" NOT IN ("Services & Digital")

It is also giving similar results. Hence, I was trying to understand, what I am missing.

[Dave Theodore [Coyote Creek Consulting]]

What type of field is "Product Division?" Did auto-complete show this value? If auto-complete isn't displaying the value, it's a good indication that something is wrong with your syntax.

[Arkadeep Kundu]

Thanks for your prompt responses Dave.

It's appearing in auto-complete

This is a custom field.

Please mind that project = PSRC AND issuetype = Incident AND "Product Division" = "Services & Digital" is working as expected. The moment I add the ! operator, the problem arises.