jql Inconsistent Behavior Filtering on Updated and Created Dates

[Thomas DeVoe]

I am attempting to return Jira issues via jql for issues created within the past # of days.

I specify the following which works. Note that it looking at the updated date as part of the criteria.

filter = 'Ops Team - All Tickets' and issuetype = "Hard Copy Request" and updated >= -111d

I specify the following which does not work. Note that in this case, I am looking at the created date as part of the criteria.

filter = 'Ops Team - All Tickets' and issuetype = "Hard Copy Request" and created >= -111d

The first version (with updated) returns some issues which have been created within the  last 111 days and should come back in the results when executing the 2nd jql (with created).

I would like to mention that these issues were imported from an outside data source and the created date was set as part of the process.

See screenshot below which shows the created and updated dates columns that are returned when executing the jql with "updated" as part of the criteria. Notice the updated date is the same for all since they were imported as a batch.

Thanks.

[Nic Brough -Adaptavist-]

Hmm.  One thing I want to rule out - is the "Created" date there the system created date or could it be a custom field of the same name?

[Radek Dostál]

Either that (could use updatedDate and createdDate instead), or it might get fixed by running a full re-index.

Which is a bit strange because I don't see a scenario where updated date would be in index, but created would not, but you never know these days.

[Thomas DeVoe]

I double checked and the created field is not a custom field. I also tried using createdDate and got the same results. I will ask that a re-index be done.

I took a look at the Jira database schema and the jiradb.jiraissue table has CREATED and UPDATED columns which I would think the jql would use. If this were an application that used SQL Server (which is our primary database), I would start a profiler to see the query that runs. I'm not sure that PostGreSQL has something like that.

[Radek Dostál]

JQL search doesn't usually query DB directly. Some functions do, but fields are apart from several exceptions all based on indexes. No indexes - no jql results, and it will not go into db just to check if it's index is right or wrong. Again though I find this scenario rather weird, but if it's not a custom field, then it surely must be missing indexes. Let's see if it helps though once you can verify it.

[Thomas DeVoe]

I'm going to request a re-index. It may take some time for someone to do it but I will update the ticket once it's done and I retry the jql.

Thanks for the assistance!