Possible "ORA-01795 maximum number of expressions in a list is 1000" error during UpgradeResourcePermission

• Type: Regression Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.0.X EE, Master
• Fix Version/s: 7.0.0 DXP FP30, 7.0.X EE, 7.0.4 CE GA5, 7.1.X, Master
• Component/s: Database Upgrade Framework
• Labels:
  • QA-NA
  • liferay-digital-enterprise-70-sp5
  • liferay-fixpack-de-30-7010
• Branch Version/s:
  7.0.x
• Backported to Branch:
  Committed
• Story Points: 1.5
• Fix Priority: 5
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/51293

Description
In LPS-73051, we modified the UpgradeResourcePermission logic to add an IN clause to a SQL query which consists of every unique primKey that does not parse to a number. This is very dangerous because it is likely that many production databases will have too many distinct primKeys that are not numbers, since production databases tend to have large numbers of instanceable portlets across many sites and pages, many of which may have their own set of resource permissions. Adding an IN clause that is too large causes errors with several databases.

Steps to Reproduce
1. Start up Liferay 6.2 on an Oracle database.
2. Place over 1000 instances of instanceable portlets (such as the Asset Publisher portlet) on the page (you can spread them out across several pages if it makes the page easier to render).
3. Assign each of these portlet instances permissions other than the default permissions.
4. Shut down Liferay and upgrade to DXP.

Expected Results: The upgrade completes successfully with no errors.
Actual Results: The following exception is thrown during the upgrade, causing it to fail:

com.liferay.portal.kernel.upgrade.UpgradeException: com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:93)
	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil._upgradeProcess(UpgradeProcessUtil.java:175)
	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:143)
	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:125)
	at com.liferay.portal.events.StartupHelper.upgradeProcess(StartupHelper.java:164)
	at com.liferay.portal.events.StartupHelperUtil.upgradeProcess(StartupHelperUtil.java:81)
	at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:153)
	at com.liferay.portal.tools.DBUpgrader.main(DBUpgrader.java:99)
Caused by: com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:93)
	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:117)
	at com.liferay.portal.upgrade.UpgradeProcess_7_0_0.doUpgrade(UpgradeProcess_7_0_0.java:99)
	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:90)
	... 7 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at com.liferay.portal.upgrade.v7_0_0.UpgradeResourcePermission._updatePrimKeyIds(UpgradeResourcePermission.java:120)
	at com.liferay.portal.upgrade.v7_0_0.UpgradeResourcePermission.upgradeResourcePermissions(UpgradeResourcePermission.java:96)
	at com.liferay.portal.upgrade.v7_0_0.UpgradeResourcePermission.doUpgrade(UpgradeResourcePermission.java:47)
	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:90)
	... 10 more