JQL Search by ID across projects is inconsistent

[Zach Gutterman]

I'm working on a JIRA integration that would allow me to log new accepted issues using camel across all of our team's projects. To do this, the camel component looks for the most recent issue ID (not key) and then does a JQL search for issues that are accepted and have an ID > the most recent id when the camel route is started. The weird thing is that the JQL ID > search provides really inconsistent results. For example, searching our team's issues in the browser with JQL for issue id > 1200 gives me 15 results for one project, searching for issue id > 1500 gives me 26 results for yet another project and so on. It seems like IDs are only consistently sequenced within a project but not across projects. Is that the case? Is there a way to change this sequencing? Why does a JQL ID search not recognize that an id of 2702 is greater than 1500?

[Nic Brough -Adaptavist-]

The ID of an issue is the unique key for it in the database.  This table is not divided by project, every new issue gets a key id a bit higher than the last one, irrespective of the project or anything else.  Also, they're not sequential, you will find you've got gaps in the numbers.

The ID is simply the wrong thing to be searching on, you should assume it is a simple unique key and nothing more.

This cannot change, you'd have to rewrite Jira to do it.

[Zach Gutterman]

Appreciate the reponse. Why does the JQL search results look like this? First image looks for ids of a lower id number but returns fewer results AND all of the results are always limited to a single project.

[Zach Gutterman]

Another example of this issue is hitting the following endpoint (this time i'm testing on JIRA cloud and seeing the same problem): 

/rest/api/latest/search?jql=ORDER+BY+id+desc

And getting the following results. You can see that the ids are not returned in order.

[Zach Gutterman]

It looks to me that sorting by id actually is sorting by key perhaps?

[Nic Brough -Adaptavist-]

Ok, there's a couple of things here.

First,  your queries are for "ID >", which is simply going to return a list of issues created since an unknown point in time. 

Your report specifies no order, so the issue navigator is defaulting to sorting by the issue key.

The search and the report are separate things (although there is some crossover - you can put "order by" in a filter, and some reports, such as the navigator, will sort the results by it)

But I think you might want to take a step back - why are you even looking at the issue ID like this?  It's not something routinely exposed to the user, and your sort might not work 100% of the time (it's possible to get issues created with IDs lower than the latest one)

[Zach Gutterman]

If I set an order (such as Order by id) i still get the same result. Looking at the docs, it appears that id = key when it is being used with jql.

The reason i'm looking at this is because the camel-jira component is using the id to try and find the latest issue. The way it works is to find the most recent issue id, then poll the search endpoint for issues > that id. The problem is that Jira apparently does not expose ID to be searched for, it instead is actually using key. So this works, but not across multiple projects. 

[Nic Brough -Adaptavist-]

The order you are getting when you sort by ID is mostly going to be the same order as the keys when you look only at a single project.

Again, the ID is not the key, it is not purely sequential, and may not even be in creation order in some cases

With your limited data set (single project, nothing clever that might be muddling the ID numbers so they don't broadly come out in the same order as the issue key), you're just not seeing it.

You need to stop looking at the id of the issue in your reporting, it's a waste of your time and can be wrong. 

You should also stop using it in camel-jira, as it's wrong.  That should be looking for the created date/time on the issues, not the id.

[Zach Gutterman]

I'm not using it in camel-jira, the camel-jira is using it by default in it's "newIssue" endpoint. I was having problems tracking new issues across categories or multiple projects and was investigating why.

I think the ID order actually IS correct. What doesn't work is that jql does not actually search by the issue id when you ask it to. It is using id as an alias for key and therefore only searching by key issues. If i'm understanding this correctly, jql doesn't actually support searches by id 

[Nic Brough -Adaptavist-]

Ah, hang on, I thought you'd exposed the actual issue id in a field, which would override the alias here.

So, we are looking at the key, in which case, I'm stuck on what the problem is, the JQL is returning the right data, sorted in the right order.

What you're asking for is still going to be incorrect, as looking at the project sequence is not going to tell you about other issues.

Consider this list:

Summary	ID (database)	Key	Created time (same day)
An issue	10010	Cat-1	9:00 am
Some other one	10020	Dog-2	10:15 am
Something about kittens	10030	Cat-2	10:30 am
Get a dog	10040	Dog-1	9:15 am
Vet time!	10050	Cat-3	11:30 am

This is real data, but anonymised and simplified to make it easier to read.  You'll note the only query ordering that can give you the last created issue is one ordering by creation time.

But, if you stay in one project, then a sort on key will answer the question.  Also, the JQL is returning the right thing because the > comparison is alphanumberic!

[Zach Gutterman]

Right, so it is not safe to assume that the database ID is always sequential based on creation time? Regardless, it seems like you cannot actually query with JQL to order by the id. I've opened an issue with Camel about their component as this seems like an oversight in the way it was designed.

[Nic Brough -Adaptavist-]

Correct.  It's not actually a bad assumption, especially for a Jira that hasn't had many "clever" things done to it, either by code, apps, or even database hacking.

But most of the ones I work with have had something "clever" done, and we often find the ID is not a reliable sort order for all sorts of bits of them.  Inside a project, you can rely on the issue-key though, and globally, you'd only want to be looking at the creation date/time.

Edit - and the bit I missed:

I would not actually be worried about what camel is doing in this case.  The question "what was the last issue created globally now" will almost always be answered fine by "highest issue id from the database".  The tiny handful of edge cases where it will not are not worth the worry.

I went off down the rabbit-hole of looking at the history, where you absolutely can not infer anything from the issue id other than "it is a unique immutable identity for an issue" because I focussed on your searching, rather than camel's question of "last issue at this tim"