jql Inconsistent Behavior Filtering on Updated and Created Dates

Question

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.

Thomas DeVoe_mdjcj0 · Answer

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!

Radek Dostál · Answer

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_mdjcj0 · Answer

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.