Service Builder finder results are not sorted consistently

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: Master
• Fix Version/s: 7.0.0 DXP FP87, 7.0.10.12 DXP SP12, 7.0.X, 7.1.10 DXP FP15, 7.1.X, 7.2.10 DXP FP3, 7.2.1 CE GA2, 7.2.X, 7.3.10 DXP GA1, Master
• Component/s: Core Infrastructure > Service Builder
• Labels:
  • ee-ts
  • liferay-dxp-70-sp12
  • liferay-fixpack-de-87-7010
  • liferay-fixpack-dxp-15-7110
  • liferay-fixpack-dxp-3-7210
• Branch Version/s:
  7.2.x, 7.1.x, 7.0.x
• Backported to Branch:
  Committed
• Fix Priority: 3
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/79326

Service Builder finder results are not consistent:

• in case you don't specify pagination parameters sort is done in java side
• in case you specify pagination parameters sort is done in database side

But sorting data in java side doesn't returns same results than database side:

• Database sort (MySQL or Postgresql) ignores accents (áááá = aaaa in sort time)
• Java sort doesn't ignore accents (áááá != aaaa in sort time)

Sorting large amount of data in java side, can also cause some throughput issues. It is better to rely in database side sorting as it is optimized doing that tasks.

Steps to reproduce (Wiki)

1. Create WikiPages with following names: ááá, aaaa, AAAAA, ÁÁÁÁÁÁ, BBBBBB
2. Query database using the finder with start = -1 and end = -1   (sort is done by ServiceBuilder code)

   for (def wikiPage : com.liferay.wiki.service.WikiPageLocalServiceUtil.getWikiPages(-1,-1)) {
       out.println(wikiPage.getTitle());
   }

3. Query database using the finder with start = 0 and end = 50   (sort is done in Database)

   for (def wikiPage : com.liferay.wiki.service.WikiPageLocalServiceUtil.getWikiPages(0,50)) {
       out.println(wikiPage.getTitle());
   }

4. Compare results:
   • Expected behavior: both queries returns wikipages in same order
   • Wrong behavior: queries returns wikipages in diferent order

Steps to reproduce (Organizations)

1. Create Organizations with following names: ááá, aaaa, AAAAA, ÁÁÁÁÁÁ, BBBBBB
2. Query database using the finder with start = -1 and end = -1 (sort is done by ServiceBuilder code)

   for (def organization : com.liferay.portal.kernel.service.OrganizationLocalServiceUtil.getOrganizations(-1,-1)) {
       out.println(organization.getName());
   }

3. Query database using the finder with start = 0 and end = 50 (sort is done in Database)

   for (def organization : com.liferay.portal.kernel.service.OrganizationLocalServiceUtil.getOrganizations(0,50)) {
       out.println(organization.getName());
   }

4. Compare results:
   • Expected behavior: both queries returns Organizations in same order
   • Wrong behavior: queries returns Organizations in diferent order