Having more than 65,000 closed sprints breaks the JQL function closedSprints()

Issue Summary

Having more than 65,000 closed sprints breaks the JQL function closedSprints() with an com.atlassian.jira.issue.search.ClauseTooComplexSearchException

Steps to Reproduce

1. install Jira Software
2. create a Scrum Development Project 'Test'
3. create sample issues and 65,001 closed sprints
4. test the JQL "project = Test AND sprint in closedSprints()"

Expected Results

issues are listed without errors

Actual Results

JQL fails with error

 com.atlassian.jira.issue.search.ClauseTooComplexSearchException: A the following query was too complex to generate a query from: {sprint in closedSprints()}

Notes

Even if there is a sensible workaround as stated below, similar bugs like JSWSERVER-16401 were resolved with optimizations in the JQL function because using the workaround may impact the performance of the system.

Workaround

• Delete closed sprints. You may consider deleting your older closed sprints to keep the number of closed sprints under 65,000. You may determine the oldest closed Sprints that are not needed anymore for reporting purposes, and remove them using the "Delete Sprint" operation (deleting closed sprints is possible after Jira version 7.9, ref. JSWSERVER-9220)
  • Checking the number of closed sprints
    Any DB:

    SELECT count(*) FROM "AO_60DB71_SPRINT" where "CLOSED" = true 
    

    Oracle DB:

    SELECT * FROM AO_60DB71_SPRINT where CLOSED = 1;
    

  • Figuring out the oldest closed sprints
    Any DB:

    SELECT * FROM "AO_60DB71_SPRINT" where "CLOSED" = true order by "END_DATE"
    

    Oracle DB:

    SELECT * FROM AO_60DB71_SPRINT where CLOSED = 1 order by END_DATE
    

• Increase the default limit of 65,000 
  Information

  This can be done by increasing the value for jira.search.maxclauses in jira-config.properties.
   Setting the parameter too high may have a performance impact on the system, hence please take care to increase the limit gradually (for example, if we're at 65,000 closed sprints already and currently closing 10,000K sprints/year, setting the limit to 85,000 will give us enough room for a couple of years already.)