Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-69935

UserLocalService getSocialUsers (UserFinder findBySocialUsers) generates error: "SELECT DISTINCT, ORDER BY expressions must appear in select list_" on PostgreSQL

    Details

      Description

      Steps to reproduce:

      1. Start the portal with PostgreSQL DB (tested with PostgreSQL 9.3)
      2. Get the userId of the Test Test user (check its Account page or look for it in the DB)
      3. Go to Control Panel/Server Administration/Script
      4. Execute the following script (replace 20154 with your userId):
        import com.liferay.portal.kernel.service.UserLocalServiceUtil;
        import com.liferay.portal.kernel.util.StringPool;
        import com.liferay.portal.kernel.util.comparator.UserFirstNameComparator;
        import com.liferay.social.kernel.model.SocialRelationConstants;
        
        		UserLocalServiceUtil.getSocialUsers(
        			20154, SocialRelationConstants.TYPE_BI_CONNECTION,
        			StringPool.NOT_EQUAL, 0, 10,
        			new UserFirstNameComparator(true));
        

      Result: You get the following error:

      23:59:13,328 ERROR [http-nio-8080-exec-3][JDBCExceptionReporter:234] ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list_  Position: 1871 [Sanitized
      

      The error doesn't occur when using com.liferay.portal.kernel.dao.orm.QueryUtil.ALL_POS because the querying logic will be different in that case and UserFinderImpl.findBySocialUser is not invoked. That's why there is no such error when running com.liferay.portlet.social.service.SocialRelationLocalServiceTest on PostgreSQL as all of the test methods are using ALL_POS.

      Similar issues: LPS-49320, LPS-62423


      Full Log
          [junit] ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list_  Position: 1871 [Sanitized]
          [junit] 22:50:09,041 ERROR [main][JDBCExceptionReporter:234] ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list_  Position: 1871 [Sanitized]
          [junit] Tests run: 9, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 653.914 sec
          [junit] Tests run: 9, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 653.914 sec
          [junit] 
          [junit] Testcase: testSocialUsersOnPostgres(com.liferay.portlet.social.service.SocialRelationLocalServiceTest):	Caused an ERROR
          [junit] com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
          [junit] com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
          [junit] 	at com.liferay.portal.service.persistence.impl.UserFinderImpl.findBySocialUsers(UserFinderImpl.java:695)
          [junit] 	at com.liferay.portal.service.impl.UserLocalServiceImpl.getSocialUsers(UserLocalServiceImpl.java:2543)
          [junit] 	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:153)
          [junit] 	at com.liferay.portal.spring.transaction.DefaultTransactionExecutor.execute(DefaultTransactionExecutor.java:54)
          [junit] 	at com.liferay.portal.spring.transaction.TransactionInterceptor.invoke(TransactionInterceptor.java:58)
          [junit] 	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:127)
          [junit] 	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
          [junit] 	at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:127)
          [junit] 	at com.liferay.portal.spring.aop.ServiceBeanAopProxy.invoke(ServiceBeanAopProxy.java:174)
          [junit] 	at com.sun.proxy.$Proxy78.getSocialUsers(Unknown Source)
          [junit] 	at com.liferay.portal.kernel.service.UserLocalServiceUtil.getSocialUsers(UserLocalServiceUtil.java:2077)
          [junit] 	at com.liferay.portlet.social.service.SocialRelationLocalServiceTest.testSocialUsersOnPostgres(SocialRelationLocalServiceTest.java:506)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$1.evaluate(BaseTestRule.java:61)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$1.evaluate(BaseTestRule.java:61)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$1.evaluate(BaseTestRule.java:61)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$1.evaluate(BaseTestRule.java:61)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$1.evaluate(BaseTestRule.java:61)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$1.evaluate(BaseTestRule.java:61)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$1.evaluate(BaseTestRule.java:61)
          [junit] 	at com.liferay.portal.test.rule.LiferayIntegrationTestRule$1$1.evaluate(LiferayIntegrationTestRule.java:146)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$1.evaluate(BaseTestRule.java:61)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$2.evaluate(BaseTestRule.java:81)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$2.evaluate(BaseTestRule.java:81)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$2.evaluate(BaseTestRule.java:81)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$2.evaluate(BaseTestRule.java:81)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$2.evaluate(BaseTestRule.java:81)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$2.evaluate(BaseTestRule.java:81)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$2.evaluate(BaseTestRule.java:81)
          [junit] 	at com.liferay.portal.test.rule.LiferayIntegrationTestRule$1$1.evaluate(LiferayIntegrationTestRule.java:146)
          [junit] 	at com.liferay.portal.kernel.test.rule.BaseTestRule$2.evaluate(BaseTestRule.java:81)
          [junit] Caused by: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
          [junit] 	at com.liferay.portal.dao.orm.hibernate.ExceptionTranslator.translate(ExceptionTranslator.java:33)
          [junit] 	at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:194)
          [junit] 	at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:173)
          [junit] 	at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:83)
          [junit] 	at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:51)
          [junit] 	at com.liferay.portal.service.persistence.impl.UserFinderImpl.findBySocialUsers(UserFinderImpl.java:692)
          [junit] Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
          [junit] 	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
          [junit] 	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
          [junit] 	at org.hibernate.loader.Loader.doList(Loader.java:2545)
          [junit] 	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
          [junit] 	at org.hibernate.loader.Loader.list(Loader.java:2271)
          [junit] 	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
          [junit] 	at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
          [junit] 	at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
          [junit] 	at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
          [junit] 	at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:182)
          [junit] Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
          [junit]   Position: 1871
          [junit] 	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
          [junit] 	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
          [junit] 	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
          [junit] 	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
          [junit] 	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
          [junit] 	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
          [junit] 	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
          [junit] 	at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
          [junit] 	at org.hibernate.loader.Loader.doQuery(Loader.java:802)
          [junit] 	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
          [junit] 	at org.hibernate.loader.Loader.doList(Loader.java:2542)
      

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  2 years, 24 weeks, 6 days ago

                  Packages

                  Version Package
                  6.2.X EE
                  7.0.0 DXP FP11
                  7.0.0 DXP SP2
                  7.0.3 CE GA4
                  7.1.X
                  Master