Message Board "My Subscriptions" tab fails to load when using Oracle database

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.3.X, Master
• Fix Version/s: 7.3.10 DXP FP1, 7.3.10.1 DXP SP1, 7.3.6 CE GA7, 7.3.X, 7.4.13 DXP GA1, Master
• Component/s: Subscription Manager
• Labels:
  • NAT
  • ee-ts
  • liferay-dxp-73-sp1
  • liferay-fixpack-dxp-1-7310
• Branch Version/s:
  7.3.x
• Backported to Branch:
  Committed
• Story Points: 1
• Fix Priority: 4
• Git Pull Request: https://github.com/liferay-lima/liferay-portal/pull/1513
• Bug Type: Regression Bug
• QA Test Name:
  • automated

Additional Info
java.sql.SQLSyntaxErrorException is also thrown in the logs:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression_ 

Steps to Reproduce

1. Setup Liferay with Oracle database
2. Sign in as Admin
3. Add a Message Board to a page
4. Click on the My Subscriptions tab

Expected Results:
User is taken to My Subscriptions and with no error/issue

Actual Results:
UI shows a portlet error: "Message Boards is temporarily unavailable. "
Log shows stack trace with error: java.sql.SQLSyntaxErrorException

Snippet of Stack Trace (Condensed)

021-02-09 18:55:16.993 ERROR [http-nio-8080-exec-5][JDBCExceptionReporter:234] ORA-00979: not a GROUP BY expression_ [Sanitized]
2021-02-09 18:55:16.998 ERROR [http-nio-8080-exec-5][PortletRequestDispatcherImpl:304] Unable to dispatch request: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
2021-02-09 18:55:17.004 ERROR [http-nio-8080-exec-5][PortletServlet:119] javax.portlet.PortletException: org.apache.jasper.JasperException: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
javax.portlet.PortletException: org.apache.jasper.JasperException: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
    at com.liferay.portlet.internal.PortletRequestDispatcherImpl.dispatch(PortletRequestDispatcherImpl.java:307)
    at com.liferay.portlet.internal.PortletRequestDispatcherImpl.include(PortletRequestDispatcherImpl.java:123)
    at com.liferay.portal.kernel.portlet.bridges.mvc.MVCPortlet.include(MVCPortlet.java:571)
    at com.liferay.portal.kernel.portlet.bridges.mvc.MVCPortlet.include(MVCPortlet.java:587)
    at com.liferay.portal.kernel.portlet.bridges.mvc.MVCPortlet.doDispatch(MVCPortlet.java:461)
    ...
    at org.apache.jsp.html.portal.render_005fportlet_jsp._jspService(render_005fportlet_jsp.java:1432)
    ...
    at com.liferay.taglib.portletext.RuntimeTag.doTag(RuntimeTag.java:349)
    at com.liferay.fragment.internal.renderer.FragmentPortletRendererImpl.renderPortlet(FragmentPortletRendererImpl.java:70)
    at com.liferay.fragment.entry.processor.portlet.PortletFragmentEntryProcessor._renderWidgetHTML(PortletFragmentEntryProcessor.java:401)
    at com.liferay.fragment.entry.processor.portlet.PortletFragmentEntryProcessor.processFragmentEntryLinkHTML(PortletFragmentEntryProcessor.java:154)
    at com.liferay.fragment.internal.processor.FragmentEntryProcessorRegistryImpl.processFragmentEntryLinkHTML(FragmentEntryProcessorRegistryImpl.java:159)
    at com.liferay.fragment.internal.renderer.FragmentEntryFragmentRenderer._renderFragmentEntryLink(FragmentEntryFragmentRenderer.java:314)
    at com.liferay.fragment.internal.renderer.FragmentEntryFragmentRenderer.render(FragmentEntryFragmentRenderer.java:100)
    at com.liferay.fragment.internal.renderer.FragmentRendererControllerImpl.render(FragmentRendererControllerImpl.java:111)
    at org.apache.jsp.render_005flayout_005fstructure.render_005flayout_005fstructure_jsp._jspService(render_005flayout_005fstructure_jsp.java:725)
    ...
    at com.liferay.portal.internal.servlet.MainServlet.doGet(MainServlet.java:207)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:626)
    at com.liferay.portal.internal.servlet.MainServlet.service(MainServlet.java:643)
    ...
Caused by: org.apache.jasper.JasperException: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:440)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:473)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:377)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
    at com.liferay.portal.osgi.web.servlet.jsp.compiler.internal.JspServlet.service(JspServlet.java:368)
    at com.liferay.portal.osgi.web.servlet.jsp.compiler.internal.JspServlet.service(JspServlet.java:380)
    at org.eclipse.equinox.http.servlet.internal.registration.EndpointRegistration.service(EndpointRegistration.java:153)
    at org.eclipse.equinox.http.servlet.internal.servlet.ResponseStateHandler.processRequest(ResponseStateHandler.java:62)
    at org.eclipse.equinox.http.servlet.internal.context.DispatchTargets.doDispatch(DispatchTargets.java:120)
    at org.eclipse.equinox.http.servlet.internal.servlet.RequestDispatcherAdaptor.include(RequestDispatcherAdaptor.java:48)
    at com.liferay.portlet.internal.PortletRequestDispatcherImpl.dispatch(PortletRequestDispatcherImpl.java:291)
    ... 251 more
Caused by: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
    at com.liferay.message.boards.service.persistence.impl.MBCategoryFinderImpl.doFindC_ByS_G_U_P(MBCategoryFinderImpl.java:579)
    at com.liferay.message.boards.service.persistence.impl.MBCategoryFinderImpl.filterFindC_ByS_G_U_P(MBCategoryFinderImpl.java:142)
    at com.liferay.message.boards.service.impl.MBCategoryServiceImpl.getSubscribedCategories(MBCategoryServiceImpl.java:442)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:50)
    at com.liferay.portal.spring.transaction.TransactionInterceptor.invoke(TransactionInterceptor.java:69)
    at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:57)
    at com.liferay.portal.kernel.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:64)
    at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:57)
    at com.liferay.portal.spring.aop.AopInvocationHandler.invoke(AopInvocationHandler.java:49)
    at com.sun.proxy.$Proxy2069.getSubscribedCategories(Unknown Source)
    at com.liferay.message.boards.service.MBCategoryServiceUtil.getSubscribedCategories(MBCategoryServiceUtil.java:298)
    at org.apache.jsp.message_005fboards.view_jsp._jspService(view_jsp.java:840)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:411)
    ... 261 more
Caused by: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
    at com.liferay.portal.dao.orm.hibernate.ExceptionTranslator.translate(ExceptionTranslator.java:39)
    at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:188)
    at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:168)
    at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:61)
    at com.liferay.message.boards.service.persistence.impl.MBCategoryFinderImpl.doFindC_ByS_G_U_P(MBCategoryFinderImpl.java:550)
    ... 279 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2545)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
    at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:176)
    ... 282 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression_ [Sanitized]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
    at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2542)
    ... 289 more
Caused by: Error : 979, Position : 50, Sql = SELECT MBCategory.categoryId AS categoryId884_0_, MBCategory.mvccVersion AS mvccVers2_884_0_, MBCategory.ctCollectionId AS ctCollec3_884_0_, MBCategory.uuid_ AS uuid4_884_0_, MBCategory.groupId AS groupId884_0_, MBCategory.companyId AS companyId884_0_, MBCategory.userId AS userId884_0_, MBCategory.userName AS userName884_0_, MBCategory.createDate AS createDate884_0_, MBCategory.modifiedDate AS modifie10_884_0_, MBCategory.parentCategoryId AS parentC11_884_0_, MBCategory.name AS name884_0_, MBCategory.description AS descrip13_884_0_, MBCategory.displayStyle AS display14_884_0_, MBCategory.lastPublishDate AS lastPub15_884_0_, MBCategory.status AS status884_0_, MBCategory.statusByUserId AS statusB17_884_0_, MBCategory.statusByUserName AS statusB18_884_0_, MBCategory.statusDate AS statusDate884_0_ FROM MBCategory INNER JOIN Subscription ON (Subscription.companyId = MBCategory.companyId) AND (Subscription.classNameId = :1 ) AND (Subscription.classPK = MBCategory.categoryId) LEFT JOIN MBMessage ON MBMessage.categoryId = MBCategory.categoryId AND (MBCategory.ctCollectionId = 0 OR MBCategory.ctCollectionId IS NULL) AND (Subscription.ctCollectionId = 0 OR Subscription.ctCollectionId IS NULL) AND (MBMessage.ctCollectionId = 0 OR MBMessage.ctCollectionId IS NULL) WHERE (MBCategory.groupId = :2 ) AND (MBCategory.parentCategoryId = 0) AND (Subscription.userId = :3 ) AND (MBCategory.status != :4 ) AND (MBCategory.ctCollectionId = 0 OR MBCategory.ctCollectionId IS NULL) AND (Subscription.ctCollectionId = 0 OR Subscription.ctCollectionId IS NULL) AND (MBMessage.ctCollectionId = 0 OR MBMessage.ctCollectionId IS NULL) GROUP BY MBCategory.ctCollectionId, MBCategory.categoryId ORDER BY (CASE WHEN MAX(MBMessage.modifiedDate) IS NULL THEN MBCategory.modifiedDate ELSE MAX(MBMessage.modifiedDate) END) DESC, OriginalSql = SELECT MBCategory.categoryId AS categoryId884_0_, MBCategory.mvccVersion AS mvccVers2_884_0_, MBCategory.ctCollectionId AS ctCollec3_884_0_, MBCategory.uuid_ AS uuid4_884_0_, MBCategory.groupId AS groupId884_0_, MBCategory.companyId AS companyId884_0_, MBCategory.userId AS userId884_0_, MBCategory.userName AS userName884_0_, MBCategory.createDate AS createDate884_0_, MBCategory.modifiedDate AS modifie10_884_0_, MBCategory.parentCategoryId AS parentC11_884_0_, MBCategory.name AS name884_0_, MBCategory.description AS descrip13_884_0_, MBCategory.displayStyle AS display14_884_0_, MBCategory.lastPublishDate AS lastPub15_884_0_, MBCategory.status AS status884_0_, MBCategory.statusByUserId AS statusB17_884_0_, MBCategory.statusByUserName AS statusB18_884_0_, MBCategory.statusDate AS statusDate884_0_ FROM MBCategory INNER JOIN Subscription ON (Subscription.companyId = MBCategory.companyId) AND (Subscription.classNameId = ?) AND (Subscription.classPK = MBCategory.categoryId) LEFT JOIN MBMessage ON MBMessage.categoryId = MBCategory.categoryId AND (MBCategory.ctCollectionId = 0 OR MBCategory.ctCollectionId IS NULL) AND (Subscription.ctCollectionId = 0 OR Subscription.ctCollectionId IS NULL) AND (MBMessage.ctCollectionId = 0 OR MBMessage.ctCollectionId IS NULL) WHERE (MBCategory.groupId = ?) AND (MBCategory.parentCategoryId = 0) AND (Subscription.userId = ?) AND (MBCategory.status != ?) AND (MBCategory.ctCollectionId = 0 OR MBCategory.ctCollectionId IS NULL) AND (Subscription.ctCollectionId = 0 OR Subscription.ctCollectionId IS NULL) AND (MBMessage.ctCollectionId = 0 OR MBMessage.ctCollectionId IS NULL) GROUP BY MBCategory.ctCollectionId, MBCategory.categoryId ORDER BY (CASE WHEN MAX(MBMessage.modifiedDate) IS NULL THEN MBCategory.modifiedDate ELSE MAX(MBMessage.modifiedDate) END) DESC, Error Msg = ORA-00979: not a GROUP BY expression_ [Sanitized]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
    ... 310 more