We're searching for issues that don't have labels in a specified list.

At first we used this query:

project = BLAHBLAHBLAH and (labels not in (bucket-1, bucket-2, bucket-3))

And we were pleasantly surprised to find only 16 issues, which we reviewed and triaged.

Then we got a report from a developer saying there were a bunch of issues we missed.

I had a hunch and tried this query:

project = BLAHBLAHBLAH and (labels not in (bucket-1, bucket-2, bucket-3) or labels is empty)

Sure enough there were over 600 items returned.

Somehow the NOT IN keyword doesn't take into account null values. This needs to be clearly documented. It probably makes sense at the SQL level but it is totally counterintuitive at the end-user level.

Notes

This is expected behaviour but continues to cause confusion so this ticket should remain open.

Documented here: JQL operators
 

Note that typing field != value is the same as typing NOT field = value, and that field != EMPTY is the same as field IS_NOT EMPTY.