JQL inconsistent result by status exclusion

Question

I posted a similar question: JQL WAS IN and WAS NOT IN clause getting different results and improved the query thanks to the response received, but still I am getting a different jira issue count and result. I would like to find all story and bugs that were in the pipeline (ready for the teams to work) at a given time. In our project we have the following statuses: To Do, In Progress, In QA, ON HOLD/BLOCK, Completed, DONE and BACKLOG.

The following two queries should give the same result, because everything that is not in the BACKLOG or DONE is ready for the teams to work. Similarly If I check for issues in valid statuses: To Do, In Progress, In QA, ON HOLD/BLOCK, Completed should produce the same result. The query combines several jira projects which is a mix of Scrum and Kanban type of project. My intention with the query is to identify the work that was planned (pending and ready to work) at the beginning of the iteration 2023-01-18.

Query1: Not Done not in the Backlog

PROJECT IN (PPC, CATR, DET,BIA) AND FIXVERSION IN (Devas) 
 and ISSUETYPE IN (Story, Bug) AND STATUS WAS NOT IN (DONE, BACKLOG) 
 ON '2023-01-18' AND CREATED < '2023-01-18' ORDER BY CREATED DESC

Query2: In valid status ready for the team to work

PROJECT IN (PPC, CATR, DET,BIA) AND FIXVERSION IN (DEVAS) 
and ISSUETYPE IN (Story, Bug) 
AND STATUS WAS IN ('To Do', 'In Progress', 'In QA', 'ON HOLD/BLOCK', Completed) 
ON '2023-01-18' AND CREATED < '2023-01-18' ORDER BY CREATED DESC

Here is the result for the Query1:

Here is the result for Query2:

As you can see the total count is not the same, but not just that, there are 10 issues in Query1 not in Query2 result and 6 issues in Query2 not in Query1  result.

As a workaround I was trying to combine both conditions in an OR statement, but I am not getting the expected result.

PROJECT IN (PPC, CATR, DET,BIA) AND FIXVERSION IN (Devas) 
 and ISSUETYPE IN (Story, Bug) AND ( 
  (STATUS WAS NOT IN (DONE, BACKLOG) ON '2023-01-18') 
 OR (STATUS WAS IN ('To Do', 'In Progress', 'In QA', 'ON HOLD/BLOCK', Completed) ON '2023-01-18') 
) AND CREATED < '2023-01-18' ORDER BY CREATED DESC

I get the counts of Query1 and if I put the Query2 first, then the count of the second one.

Am I doing something wrong here, or is it a bug, if so has it being reported?

Thanks,

David

David Leal_28ge8x · Answer

Thanks @Vamsi Kandala I think this is what I need, I wanted to find issues that were in a specific status in the past (at the beginning of the iteration), I am not interested in the actual status, but mainly in a set of possible statuses in the past. Per my understanding this the clause to use, but maybe I am wrong. Is there any other way to achieve it?

Actually if I try to use it with DURING predicate, for the last minute the day before it gives a different count for the Query1 search:

PROJECT IN (PPC, CATR, DET,BIA) AND FIXVERSION IN (Devas) 
and ISSUETYPE IN (Story, Bug) AND STATUS WAS NOT IN (DONE, BACKLOG) 
DURING('2023-01-17 23:58', '2023-01-17 23:59') 
  AND CREATED < '2023-01-18' ORDER BY CREATED DESC

I am getting 98 counts, before I was getting for a similar query 92.

Which is the same number of counts (98) if I add the timestamp to my original Query1:

PROJECT IN (PPC, CATR, DET,BIA) AND FIXVERSION IN (Devas) 
and ISSUETYPE IN (Story, Bug) AND STATUS WAS NOT IN (DONE, BACKLOG) 
ON '2023-01-18 00:00' AND CREATED < '2023-01-18 00:00' ORDER BY CREATED DESC

it is really weird, this behavior. I assumed that a date with no timestamp is equivalent. to: 'yyyy-mm-dd 00:00' but it is not.

Vamsi Kandala · Answer

Hi @David Leal

Just a general question.  What is the purpose of using 'WAS' in your query?

Are you aware that the 'WAS' operator is used to find issues that currently have or previously had the specified value for the specified field?

That means, if you use

STATUS WAS IN ('To Do', 'In Progress')

It searches for issues that had the above statuses at some point in the transition cycle and not just what the current status of the issue is.

I think you should use the query without 'WAS' to get accurate and matching results.

Hope this helps.

Thanks,
Vamsi