JQL doesn't work correct with "labels is not (...)"

[stephan71]

Hello,

we have a project DP with some issues labeled with "CR".

If I use the JQL query

project = DP AND type = Requirement AND labels in (CR)

I get the correct result. But if I use the JQL query

project = DP AND type = Requirement AND not labels in (CR)

I get a empty result, which isn't true.

I can't see an error in the log.

Could you help, please?

Kind regards
Stephan

[Nic Brough -Adaptavist- · Accepted solution]

My guess is that it is working fine, but you are not looking for issues that do not have any label (which is not the same as "has a label which is not X", which is what your search says)

I think you want to search for both cases:

and (labels != CR or labels is empty)

[stephan71]

Sorry, your guess isn't true, there could be also other labels.

[stephan71]

Sorry, Nic, your answer ist right, thank you very much!

[Anna Hindley]

thanks - that sorted my issue :-) @Nic Brough -Adaptavist- 

[Monica Brazelton]

Thanks @Nic Brough -Adaptavist- , this fixed my issue as well. I always forget about the possibility of the Labels field being empty, and the "not" criteria on the JQL filtering that out too.

[stephan71 · Accepted solution]

Sorry, you are all right! But I made a mistake, I used "labels not in (CR)" and I get no result.

[Sachin Dhamale]

@stephan71 

Just try following JQL and see the result

labels not in (CR)

project = DP AND labels not in (CR)

type = Requirement AND labels not in (CR)

And also try to perform the indexing on Project DP and then check again

Might be indexing issue.

[Sachin Dhamale]

Does it help?

[stephan71]

I hoped also it is an indexing issue, but after indexing there is no change.

I tried some tests with different JQL queries:

1. [empty query -> all issues] --> 142159 issues
2. labels not in (CR) --> 6414 issues
3. labels in (CR) --> 443 issues
4. project = DP AND labels not in (CR) --> 388 issues
5. project = DP and labels in (CR) --> 427 issues
6. type = Requirement AND labels not in (CR) --> 219 issues
7. type = Requirement AND labels in (CR) --> 433 issues
8. type = Requirement --> 88468 issues
9. project = DP --> 35119 issues
10. project = DP and type = Requirement --> 30527 issues

Queries 2., 4. and 6. (all with negation ... not in...") are wrong. Until now I don't know why.

[Andy Heinzer]

Hi @stephan71 

The JQL query of

labels not in (CR)

is only going to return to your issues that have some value for labels and does not contain the CR label. It actually is not returning issues that have no value (otherwise known a null value) for that labels field.

In your case, try changing the query above to be

(labels not in (CR) OR labels is EMPTY)

and see if that returns the expected number of issues.

I hope this helps.

Andy

[stephan71]

Thank you very much for your further explanation, now I understand. Then the answer from Nic Brough _Adaptavist_ was also correct.

[Mirek]

Hi @stephan71 

I think the correct format would be

labels not in ("CR")

or

labels != CR

[Sachin Dhamale]

@stephan71 ,

Try this one

labels not in (CR)

[Jakal Krook]

If you run a search query on JIRA and JQL, it returns the search result. One of the fields displayed after the search result is "Labels" which displays all the labels attached to issues. If you search for issues that are created by a particular assignee, then the "Labels" field will contain a list of labels attached to issues created by that particular assignee.