Using certain JQL functions causes a ClauseTooComplexSearchException when many values exist in the database

Issue Summary

Certain JQL functions can trigger a ClauseTooComplexSearchException if too many search results are returned when using the functions, including (but not limited to):

• worklogDate
• worklogAuthor
• fixVersion
• closedSprints() - JSWSERVER-20283

In the UI, the search fails, and this error is presented:

By default, Jira is configured with a limit of up to 65000 search clauses - if the function will return more than this number of issues, the exception will be thrown. This is true even if the overall JQL query will not return less than 65000 issues; for example, the overall query can be constrained with a term such as (issuekey = TEST-123), which should only return 1 issue, and it will still fail.

Environment

Tested in Jira 7.6.9 and Jira 7.13.6

Steps to Reproduce

1. Create at least 65000 issues which have worklog entries. The Data Generator for Jira is effective for creating these issues, but should never be used in a Production environment.
2. Re-index the environment as needed to ensure that all issues are searchable
3. Perform a query that uses the affected function (i.e. worklogDate < now()) so that over 65000 issues will be returned.

Expected Results

The search succeeds, and the full result set is returned.

Actual Results

The below exception is thrown in the atlassian-jira.log file:

2019-09-05 16:38:55,419 http-nio-8041-exec-20 ERROR kliou 998x2294x2 rdp65t 0:0:0:0:0:0:0:1 /rest/issueNav/1/issueTable [c.a.j.p.i.service.issuetable.DefaultIssueTableService] com.atlassian.jira.issue.search.ClauseTooComplexSearchException: A the following query was too complex to generate a query from: {issuekey in (100000, 1000001, 1000002, 1000003, 1000004
...
999996, 999997, 999998, 999999)}

The following error is shown in the UI, and the search does not return any values:

An unknown error occurred while trying to perform a search.

Notes

This is very similar, or identical, to JSWSERVER-16401 : Having a huge set of versions causes Boards to break, which is the same problem when using fixVersion.

Workaround

It is possible to work around this issue by increasing the number of allowable clauses used in searches.

• Not recommended. Increase the default limit of 65000, but it is not known how this will affect future performance of the system. This can be done by increasing the value for jira.search.maxclauses in jira-application.properties file located in following directory:

  <JIRA-INSTALL-DIRECTORY><atlassian-jira>/WEB-INF/classes/