JQL filter for empty AND filled field

[sour_soeren]

 Hi there,

I'm trying to create a JQL filter inside one project, to filter all issues with an "empty" "assignee" AND with "assignee" "not in" (username, username).

But the following statement doesn't work:

project = "ABC" AND Sprint in (347, 331) AND assignee not in (username, username, username, username) AND assignee = EMPTY AND status not in (done, Storno) AND status not in (Done, Resolved) ORDER BY assignee ASC, cf[10004] ASC

What' wrong?

Thanks in advance.

[Nic Brough -Adaptavist- · Accepted solution]

You've defined a query that can not possibly return anything.

Just looking at the bit that causes the problem, and shortening it a bit), have a think through what this really says:

assignee not in (A, B, C, D) and assignee is empty

There's two possibilities there, the assignee is either filled with something, or it is empty.  If you AND those, you're always going to get nothing because it cannot be both values at the same time.

I think your query should be:

project = "ABC" AND Sprint in (347, 331) AND

( assignee not in (username, username, username, username) OR assignee is EMPTY )

AND status not in (done, Storno, Done, Resolved)

ORDER BY assignee ASC, cf[10004] ASC

[sour_soeren]

That was fast :)

Thank you very much and you're absolutely right, but sometimes you need a different person to check the work.

[Nic Brough -Adaptavist-]

I know what you mean.  JQL queries are one of the things I often need someone else to check!

[J_R_ Menzinger]

Hi Nic, 

but... what I found today is that if you filter for a property not being something (and the property can be empty) then the filter will not match the empty ones.

for example:

assignee != UserName 

will return less results than 

assignee != UserName  OR assignee is EMPTY

And this behaviour will also occur for labels , comments , etc.

labels != LabelName 

labels != LabelName OR labels is EMPTY

Comment !~ "WordToExclude"

Comment !~ "WordToExclude" OR comments is EMPTY

Is this a bug on JIRA?

[Darren Griffith]

Does anyone know if Atlassian has something like an ISEMPTY(assignee, "") = "Jane Smith" in the works? Something that works like ISNULL or NVL in the SQL languages.