ERROR: aggregate functions are not allowed in UPDATE Position: 54 when upgrading a PostgreSQL database

Issue

• During the upgrade progress to 7.4 a fatal error occurs in process com.liferay.portal.upgrade.v7_4_x.UpgradeCompanyId:

INFO  [main][UpgradeProcess:145] Failed upgrade process com.liferay.portal.upgrade.v7_4_x.UpgradeCompanyId in 76 ms
INFO  [main][PortalUpgradeProcess:190] Failed upgrade process com.liferay.portal.upgrade.PortalUpgradeProcess in 23167 ms
ERROR [main][DBUpgrader:79] null
com.liferay.portal.kernel.log.LogSanitizerException: com.liferay.portal.kernel.upgrade.UpgradeException: com.liferay.portal.kernel.upgrade.UpgradeException: org.postgresql.util.PSQLException: ERROR: aggregate functions are not allowed in UPDATE  Position: 54

•  In PostgreSQL's log you can fin this error.

  ERROR:  aggregate functions are not allowed in UPDATE at character 54
  STATEMENT:  update PortalPreferenceValue set companyId = (select max(companyId) from PortalPreferences where PortalPreferences.portalPreferencesId > 0 and PortalPreferences.portalPreferencesId = PortalPreferenceValue.portalPreferencesId)

 

Environment

• Upgrade to Liferay 7.4.
• Java 8

Resolution

• A workaround to avoid this error is to use a Java 11.
• It will be further investigated in https://liferay.atlassian.net/browse/LPS-186837