User search can return duplicate results when using custom joins

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.0.X, Master
• Fix Version/s: 7.0.0 DXP FP37, 7.0.0 DXP SP7, 7.0.5 CE GA6, 7.0.X, 7.1.0 M1, 7.1.X, Master
• Component/s: User Management > Users & Organizations
• Labels:
  • liferay-digital-enterprise-70-sp7
  • liferay-fixpack-de-37-7010
• Branch Version/s:
  7.0.x
• Backported to Branch:
  Committed
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/54456, https://github.com/brianchandotcom/liferay-portal-ee/pull/19218

For User search, when you are doing custom sql joins that can return duplicate results, the search results return the duplicates rather than the distinct set of users. The results returned back should be a distinct set of user objects.

This only occurs when:

1. users.search.with.index=false (doing a database search)
2. you are invoking UserLocalServiceUtil.search(..) where the params references a custom join that returns duplicate results

This ticket LPS-66811 removed the "DISTINCT" because of a DB2 issue where ORDER BY columns did not match up with the SELECT columns. However, code has changed where ORDER by cols now match SELECT columns, so that is no longer an issue to include DISTINCT back in.