SQL syntax error occurs when reindexing more than 10000 web contents and indexing all web content versions is disabled

• Type: Regression Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.2.X EE
• Fix Version/s: 6.2.X EE
• Component/s: Web Content > Web Content Administration
• Labels:
  • INACTIVE
  • QA-V
  • ee-6.2.x-only
  • ee-ts
  • liferay-fixpack-portal-136-6210
• Branch Version/s:
  6.2.x
• Backported to Branch:
  Committed
• Story Points: 1.25
• Fix Priority: 3
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal-ee/pull/15842

Steps to reproduce:

1. Add more than 10000 web content article on your database (or make sure your database has more than 10000 web contents in a single portal instance or import Journal-more-than-12000-articles.portlet.lar);
2. Add journal.articles.index.all.versions=false on your portal-ext.properties.
3. Reindex all search indexes

Result:
The following exception is raised in the logs:

17:11:07,619 ERROR [http-bio-8080-exec-9][LuceneIndexer:142] Error encountered while reindexing
com.liferay.portal.kernel.search.SearchException: com.liferay.portal.kernel.exception.SystemException: org.hibernate.exception.SQLGrammarException: could not execute query
        at com.liferay.portal.kernel.search.BaseIndexer.reindex(BaseIndexer.java:547)
        at com.liferay.portal.search.lucene.LuceneIndexer.reindex(LuceneIndexer.java:161)
        at com.liferay.portal.search.lucene.LuceneIndexer.doReIndex(LuceneIndexer.java:131)
        at com.liferay.portal.search.lucene.LuceneIndexer.reindex(LuceneIndexer.java:64)
        at com.liferay.portal.search.lucene.LuceneIndexer.reindex(LuceneIndexer.java:57)
        at com.liferay.portlet.admin.action.EditServerAction.reindex(EditServerAction.java:386)
        at com.liferay.portlet.admin.action.EditServerAction.processAction(EditServerAction.java:201)
        at com.liferay.portal.struts.PortletRequestProcessor.process(PortletRequestProcessor.java:166)
{...}
Caused by: org.postgresql.util.PSQLException: ERRO: coluna "this_.resourceprimkey" deve aparecer na cláusula GROUP BY ou ser utilizada em uma função de agregação_  Posição: 113 [Sanitized]
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
        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)

Note: the log messages may vary slightly depending on the DB that is being used.