Asset Publisher can't send subscription e-mails if dynamic contents are ordered by structure fields

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.2.X EE, Master
• Fix Version/s: 7.0.0 DXP FP23, 7.0.X EE, 7.0.4 CE GA5, 7.1.X, Master
• Component/s: Content Display Widgets > Asset Publisher widget, WCM > Publication
• Labels:
  • liferay-digital-enterprise-70-sp5
  • liferay-fixpack-de-23-7010
• Branch Version/s:
  7.0.x
• Backported to Branch:
  Committed
• Story Points: 1.25
• Fix Priority: 3
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/48067

Steps to reproduce

1. Add asset.publisher.check.interval=1 to your portal-ext.properties so that the behavior can be observed within 1 hour
2. Create a web content structure S1 with a date field named D
3. Create a template T1 based for S1
4. Add an Asset Publisher portlet AP to a page
5. Configure AP to show only Web Content asset types, with structure S1 and order by field D, then save configuration
6. Still in the configuration mode, go to Subscriptions tab and enable mail subscription, then save configuration and exit configuration mode
7. Click on Subscribe on AP
8. Create a web content W1 based on structure S1
9. Wait roughly 1 hour for the e-mail to be sent.

Expected behavior

Mail will be sent successfully

Current behavior

Mail won't be sent and the following stacktrace is seen in the logs

20:13:07,784 ERROR [liferay/scheduler_dispatch-4][JDBCExceptionReporter:82] Unknown column 'AssetEntry.ddm' in 'order clause'
20:13:07,788 ERROR [liferay/scheduler_dispatch-4][ParallelDestination:74] Unable to process message {destinationName=liferay/scheduler_dispatch, response=null, responseDestinationName=null, responseId=null, payload=null, values={GROUP_NAME=com.liferay.portlet.assetpublisher.messaging.CheckAssetEntryMessageListener, companyId=0, MESSAGE_LISTENER_UUID=a37a0c9e-ef48-b663-79bf-a4fd219cec26, groupId=0, DESTINATION_NAME=liferay/scheduler_dispatch, MESSAGE_LISTENER_CLASS_NAME=com.liferay.portlet.assetpublisher.messaging.CheckAssetEntryMessageListener, EXCEPTIONS_MAX_SIZE=0, [email protected], RECEIVE[email protected]102186d0, STORAGE_TYPE=MEMORY_CLUSTERED, JOB_NAME=com.liferay.portlet.assetpublisher.messaging.CheckAssetEntryMessageListener}}
com.liferay.portal.kernel.messaging.MessageListenerException: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
	at com.liferay.portal.kernel.messaging.BaseMessageListener.receive(BaseMessageListener.java:32)
	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.kernel.bean.ClassLoaderBeanHandler.invoke(ClassLoaderBeanHandler.java:67)
	at com.sun.proxy.$Proxy299.receive(Unknown Source)
	at com.liferay.portal.kernel.scheduler.messaging.SchedulerEventMessageListenerWrapper.receive(SchedulerEventMessageListenerWrapper.java:77)
	at com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:72)
	at com.liferay.portal.kernel.messaging.ParallelDestination$1.run(ParallelDestination.java:71)
	at com.liferay.portal.kernel.concurrent.ThreadPoolExecutor$WorkerTask._runTask(ThreadPoolExecutor.java:682)
	at com.liferay.portal.kernel.concurrent.ThreadPoolExecutor$WorkerTask.run(ThreadPoolExecutor.java:593)
	at java.lang.Thread.run(Thread.java:745)
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.portlet.asset.service.persistence.AssetEntryFinderImpl.findEntries(AssetEntryFinderImpl.java:100)
	at com.liferay.portlet.asset.service.impl.AssetEntryLocalServiceImpl.getEntries(AssetEntryLocalServiceImpl.java:212)
	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.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:115)
	at com.liferay.portal.spring.transaction.DefaultTransactionExecutor.execute(DefaultTransactionExecutor.java:62)
	at com.liferay.portal.spring.transaction.TransactionInterceptor.invoke(TransactionInterceptor.java:51)
	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
	at com.liferay.portal.cache.ThreadLocalCacheAdvice.invoke(ThreadLocalCacheAdvice.java:51)
	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
	at com.liferay.portal.spring.aop.ServiceBeanAopProxy.invoke(ServiceBeanAopProxy.java:175)
	at com.sun.proxy.$Proxy144.getEntries(Unknown Source)
	at com.liferay.portlet.asset.service.AssetEntryLocalServiceUtil.getEntries(AssetEntryLocalServiceUtil.java:618)
	at com.liferay.portlet.assetpublisher.util.AssetPublisherImpl.getAssetEntries(AssetPublisherImpl.java:437)
	at com.liferay.portlet.assetpublisher.util.AssetPublisherImpl._checkAssetEntries(AssetPublisherImpl.java:1324)
	at com.liferay.portlet.assetpublisher.util.AssetPublisherImpl.access$0(AssetPublisherImpl.java:1300)
	at com.liferay.portlet.assetpublisher.util.AssetPublisherImpl$2.performAction(AssetPublisherImpl.java:318)
	at com.liferay.portal.kernel.dao.orm.BaseActionableDynamicQuery.performActionsInSingleInterval(BaseActionableDynamicQuery.java:324)
	at com.liferay.portal.kernel.dao.orm.BaseActionableDynamicQuery.performActions(BaseActionableDynamicQuery.java:46)
	at com.liferay.portlet.assetpublisher.util.AssetPublisherImpl.checkAssetEntries(AssetPublisherImpl.java:324)
	at com.liferay.portlet.assetpublisher.util.AssetPublisherUtil.checkAssetEntries(AssetPublisherUtil.java:87)
	at com.liferay.portlet.assetpublisher.messaging.CheckAssetEntryMessageListener.doReceive(CheckAssetEntryMessageListener.java:29)
	at com.liferay.portal.kernel.messaging.BaseMessageListener.receive(BaseMessageListener.java:26)
	... 12 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:30)
	at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:189)
	at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:168)
	at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:194)
	at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:162)
	at com.liferay.portlet.asset.service.persistence.AssetEntryFinderImpl.findEntries(AssetEntryFinderImpl.java:96)
	... 52 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:177)
	... 56 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'AssetEntry.ddm' in 'order clause'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418)
	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)
	... 63 more

It is not reproducible at master @0aee5c9 because of LPS-68604, but, once it is fixed, it will be reproducible.