Problem

Users belonging to too many groups can cause the search results to fail

Environment

Reproduced in Confluence Data Center 8.5 installed with an underlying Microsoft SQL server Database

Steps to Reproduce

1. Create a Confluence Data Center 8.5.x instance with an underlying Microsoft SQL server Database
2. Create a test user
3. Create 2100+ groups in the site
4. Add the test user to more than 2100 groups
5. Perform a search request, for instance, accessing URL below in your web browser directly <confluenceBaseURL>/rest/api/search?cql=siteSearch%20~%20%22freedom%22&start=0&limit=20&excerpt=highlight&expand=space.icon&includeArchivedSpaces=false&src=next.ui.search

Expected Results

Confluence will return the search results corresponding to this call

Actual Results

The search is not working and will return an error :

• {"statusCode":500,"message":"","reason":"Internal Server Error"} in the web browser
• Stack Trace below can be observed in the backend logs
  atlassian-confluence.log

  [...]
  2024-11-20 04:14:15,324 WARN [http-nio-8090-exec-2 url: /rest/api/search; user: testuser] [engine.jdbc.spi.SqlExceptionHelper] logExceptions SQL Error: 8003, SQLState: S0001
   -- url: /rest/api/search | userName: testuser | traceId: efbb5d96cf5c0332
  2024-11-20 04:14:15,325 ERROR [http-nio-8090-exec-2 url: /rest/api/search; user: testuser] [engine.jdbc.spi.SqlExceptionHelper] logExceptions The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
   -- url: /rest/api/search | userName: testuser | traceId: efbb5d96cf5c0332
  2024-11-20 04:14:15,339 ERROR [http-nio-8090-exec-2 url: /rest/api/search; user: testuser] [rest.api.model.ExceptionConverter] convertServiceException No status code found for exception, converting to internal server error : 
   -- url: /rest/api/search | userName: testuser | traceId: efbb5d96cf5c0332
  org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
  	at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:195)
  	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:374)
  	at org.springframework.orm.hibernate5.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:334)
  	at com.atlassian.confluence.impl.search.v2.lucene.filter.HibernateSpacePermissionsFilterDao.getPermittedSpaceKeys(HibernateSpacePermissionsFilterDao.java:56)
  	at com.atlassian.confluence.impl.search.v2.lucene.filter.SpacePermissionsFilterFactory.createPermittedSpacesFilter(SpacePermissionsFilterFactory.java:65)
  [...]
  

Workaround

• Reduce the number of groups the user belongs to below 2099 and the Search Query will work

Notes

Some scripts is being discussed internally to reproduce the steps