Sprint NOT IN doesn't appear to be working JQL

[Bruce_ Luke]

I am using JQL to find issues that are in a certain status see below.

project = UBT AND status IN ("Awaiting Test", "In Test") AND (labels is EMPTY OR labels NOT IN (FRONTEND)) AND (Sprint is EMPTY OR Sprint NOT IN ('Payment Engine Vertical Slice')) ORDER BY labels ASC, status DESC, created DESC

The first part on labels works perfect (tip previously given from community. However tghe second part where I am trying to use the same method for sprint doesn't. It does return items that are not in a sprint but it still returns tickets that are in the sprint i've specified in the NOT IN clause.

Can someone help?

P.S I've also tried the above with ID for the sprint and also

project = UBT AND status IN ("Awaiting Test", "In Test") AND (labels is EMPTY OR labels NOT IN (FRONTEND)) AND (Sprint is EMPTY OR Sprint != 9917) ORDER BY labels ASC, status DESC, created DESC

[Bill Sheboy]

Hi @Bruce_ Luke 

I tried your query, with my own project/sprint/labels/etc. values and it worked for me.

Let's try this: Please look at an example issue which is returned unexpectedly for the sprint which was excluded (i.e., sprint 9917).  Now look at the issue history to see if the issue was perhaps removed from the sprint when it was in progress.

Kind regards,
Bill

[Bruce_ Luke]

Thanks for responding @Bill Sheboy I've had a look and all the ones that are incorrectly showing have the same history in regards to sprint. The sprint was originally blank, then it was added, removed then added again within a few minutes. The timings are the same so I assume some sort of bulk work was being done.

Im not quite sure why this would cause an issue though. I am expecting my JQL queries to return data based on the information on the tickets at the time of querying and not historically. Is that not the case?

[Bill Sheboy]

This is not a SQL, as Atlassian likes to remind me ;^)

And while JQL does include operators like WAS NOT IN AND CHANGED, those do not work with the sprint field yet.  Here is a suggestion to add those history operators for the sprint field, which you can watch/vote for to see progress: https://jira.atlassian.com/browse/JRACLOUD-78362

If you do not need this query often, a couple of work-arounds would be:

• export the issues to a spreadsheet, which will split the sprint field, and then you can filter
• create an automation rule, and use conditional logic to strip out the issues with that sprint

If you need this frequently, you could investigate the marketplace for JQL addons

[Bruce_ Luke]

@Bill Sheboy Thanks again for the reply. I use the filter all the time. I agree JQL is not SQL but I would expect JQL queries to work for all fields it's very poor from Atlassian if thats not the case. 

I've looked at the link you sent and according to them the operators I am using ARE available on Sprint so I still dont see why it doesn't work.

It says "Actually, the sprint field only supports the operators = , !=
IS , IS NOT, IN , NOT IN
as per Advanced search reference - JQL fields."

I have tried the != and the NOT IN operators with no luck. I am not searching for any history, I am looking to filter on the tickets current state. This works on all aspects of the query except the != or NOT IN part. If it works for you does this not suggest there is potentially a bug?

[Bruce_ Luke]

@Bill Sheboy Just looking at this a little further. If I run this it doesn't work

project = UBT AND status IN ("Awaiting Test", "In Test") AND (labels is EMPTY OR labels NOT IN (FRONTEND)) AND (Sprint is EMPTY OR Sprint != 9917) ORDER BY labels ASC, status DESC, created DESC

If I simply change the != for the sprint to = (as below) it does work it only finds stuff in sprint 9917. This suggests there is a specific problem with the != and NOT IN operators as I see the same issue between IN and NOT IN. Hope this helps.

project = UBT AND status IN ("Awaiting Test", "In Test") AND (labels is EMPTY OR labels NOT IN (FRONTEND)) AND (Sprint is EMPTY OR Sprint = 9917) ORDER BY labels ASC, status DESC, created DESC

[Bill Sheboy]

Something seems different for your query or project, as that one works for me with Jira Cloud.  I checked again in the public backlog and didn't find any related defects.

At this point, I recommend working with your site admin to submit a ticket to Atlassian support.  You appear to be on a paid license, and so they can do that here: https://support.atlassian.com/contact/#/

Once you hear back from them, please post what you learn to benefit the community.  Thanks!

[Trudy Claspill]

Hello @Bruce_ Luke 

I thought I had reproduced what you saw, but what I discovered was that I actually had two sprints with identical names. While I thought I was excluding issues in "CRS Sprint 2", I still saw issues in the result saying they were assigned to "CRS Sprint 2". I looked more closely at the list of sprints that showed when I was typing in the sprint name in the JQL and saw that two matches were shown.

Can you confirm that you don't have multiple sprints (9917 plus another) that have identical names?

[Bruce_ Luke]

Thanks both. 9917 is the sprint ID so this is unique as are your IDs shown above so this unfortunately isn't the cause either. Sounds like we'll need to raise support ticket.