Issues fail to appear in JQL sprint queries

[Peter McCann]

I am trying to create a query that will be used to drive what issues appear in the plans view for some stakeholders. 

We have some issues already committed to and this view needs to focus on future work that has not been assigned to those sprints. 

I thought this would be easy enough. Just query what sprints we want to exclude for stories. Weirdly, the query is not returning some issues that have never been in a sprint before (their history confirms this). 

Original Query: 

> ( project in (MRIPROJ) and status not in (Done, Closed) and sprint not in (7051, 7052, 7053, 7739)) or (project = MRIPROJ and issuetype in (Initiative, Epic) and status not in (Done, Closed)) 

This resolves some issues I would expect it to return, but not all. 

Several such issues missing from the data set are stories without any past sprint assignment, and are not in any active or future sprint. 

To be sure I was not missing them with mk 1 eyeball, I changed the query to return just that one and found even more odd results. 

> ( project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and sprint NOT IN openSprints() and issuekey = MRIPROJ-3661) 

This returns no issues. Which is weird since it is a story in the backlog. 

So one would expect that maybe I am somehow wrong and the issues was assigned to an open sprint. If you inverse that part of the query, it also returns no results. 

> ( project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and sprint IN openSprints() and issuekey = MRIPROJ-3661) 

I am perplexed how queries for both the issue inside any open sprint or not in any open sprint would both return no results. 

To be sure I was not crazy, I removed the sprint portion all together. Returned the issue in the issuekey. 

So obviously the sprint portion is excluding it somehow, but the history shows no sprints and the sprint field on the issue shows no sprint assigned. It is empty. 

What am I missing here that would cause this? 

[Taylor Whited]

Hi Peter, 

If you want to get all work items not in any sprint, including ones that have never been a part of any completed (formerly active) sprint, you will want to include OR Sprint IS EMPTY into your JQL. Make sure you wrap it in brackets with your other sprint condition too!

Adding the empty sprint clause to your first two JQL statements would look like this:

( project in (MRIPROJ) and status not in (Done, Closed) and (sprint IS EMPTY OR sprint not in (7051, 7052, 7053, 7739))) or (project = MRIPROJ and issuetype in (Initiative, Epic) and status not in (Done, Closed))

 and

( project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and (sprint IS EMPTY OR sprint NOT IN openSprints()) and issuekey = MRIPROJ-3661)

[Peter McCann]

I am not making the problem clear. 

I can use ANY sprint query here and it does not return the issue. I can query for specific sprints, I can query NOT IN future sprints() for something in the backlog and it returns no issues. I can query for NOT IN open sprints() and it doesn't return the issue. 

I can query for sprint is empty, and it will absolutely return this issue at hand, but if sprint is empty, it should also evaluate as true for NOT IN FutureSprints(). It does not. 

[Taylor Whited]

The work item would only evaluate as true for Sprint NOT IN futureSprints() if the Sprint value is not empty and not in a future sprint. Empty fields are not considered in the results:

Unless specified in the search query, note that JQL searches do not return empty fields in results.

https://support.atlassian.com/jira-software-cloud/docs/jql-functions/

Work in the backlog is not considered to belong to a future sprint. Work items can find their way into the backlog with non-empty and empty Sprint values.

See how the different queries below return the issues:

Backlog

Sprint IN futureSprints()

Sprint NOT IN (openSprints(), futureSprints()) (notice the None +1 for Sprint in the resulting work item)

Sprint IS EMPTY OR Sprint NOT IN (openSprints(), futureSprints())

[Peter McCann]

So a query to look for things NOT IN a specific set of sprints by ID, is not expected to return the items NOT IN those sprints if sprint field is empty? 

Do I have that correct? 

[Taylor Whited]

@Peter McCann that's correct. On the docs page I shared previously, there is an example provided for the assignee field as another perspective:

To include empty fields (e.g. unassigned work items) when searching for work items that are not assigned to the current user, you would enter (assignee != currentUser() OR assignee is EMPTY) to include unassigned work items in the list of results.

Hope the inclusion of the EMPTY conditions can help recover the missing work items in your queries.

[Marc - Devoteam]

Hi @Peter McCann 

The JQL clause openSprints() means the sprint is active.

If the issue is in a sprint on the backlog you would need to include the JQL clause futureSprints() as well.

[Peter McCann]

Sure, but the negation of the open sprints should return anything not in the active sprint. it does not return the issue. 

I have also used future sprints(), and it returns nothing both when saying in FutureSprints() as well as NOT IN futuresprints(). 

[Marc - Devoteam]

Hi @Peter McCann 

Work you way back in the JQL, piec by piece.

Does this part give you all the right issue, without using any sprint related keywords

( project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed)

[Peter McCann]

The original query I used gives me some of the right issues, but misses others that should be included. I was using the issuekey = {issue_ID} above follow on queries to illustrate how 1 issue of the several that should appear is missing. The cause is 100% the sprint clause. It returns a result of that issue when I query: 

>  project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and issuekey = MRIPROJ-3661 

1 issue returned as expected. 

Add in the sprint clause with examining empty sprint and it returns 1 issue as expected: 

>  project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and sprint is empty and issuekey = MRIPROJ-3661 

If I query on sprint in Futuresprints() it should return no issues since the issue is not in any future sprint. 

>  project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and sprint not in FutureSprints() and issuekey = MRIPROJ-3661 

Returns no issues as expected. 

HOWEVER, when I negate that logic, it should absolutely return 1 issue and it returns no issues. 

> >  project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and sprint not in FutureSprints() and issuekey = MRIPROJ-3661 

NO ISSUES RETURNED?!?! 

Same is true for negating Opensprints() in the "NOT IN OpenSprints()" clause. No issues returned, but we KNOW it is not in an open sprint. 

Even more strange is if I query based on specific sprints I know it has never been in, and negate that clause, IT STILL DOES NOT RETURN THE ISSUE. 

>  project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and sprint not in (7051, 7052, 7053, 7739) and issuekey = MRIPROJ-3661 

These are all valid sprint IDs that return issues. Yet that query does not return this issue or others I have tried that seem to be defying the set theory behind the query. 

So, I know the query SHOULD pick up the issue, but it doesn't, and I wonder if there is something about the configuration of the instance or something about how the issue is defined that would impact it that I am not aware of. 

[Marc - Devoteam]

Hi @Peter McCann 

It correct that JQL returns nothing as all clauses need to be true based on AND

HOWEVER, when I negate that logic, it should absolutely return 1 issue and it returns no issues.

> > project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and sprint not in FutureSprints() and issuekey = MRIPROJ-3661

NO ISSUES RETURNED?!?!

This because MRIPROJ-3661 is in "project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and issuekey = MRIPROJ-3661

but adding "and sprint not in FutureSprints()" makes the result negative as MRIPROJ-3661 doesn't match this clause.

So the JQL should be:

project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and sprint not in FutureSprints() or project in (MRIPROJ, RRM, "Enterprise Secure File Transfer") and status not in (Done, Closed) and issuekey = MRIPROJ-3661

Does this give the correct result?

[Peter McCann]

No. 

We are missing the whole premise of the problem I think. 

Set theory says if something is not in a set, then it has to be in the set. If something is in the set, it can't be outside the set. 

In this case, we are seeing queries failing to return ANY results for both "IN " as well as "NOT IN" open, future, and specific sprints. 

That should not be possible.