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

UserLocalServiceUtil.search() with order by for status fails in DB2

    Details

      Description

      Description:
      The customer is using the following method to search users based on keywords and order by "status" column. The SQL query behind the API is not working against the DB2 when the order by "status" is passed.

      LinkedHashMap<String, Object> params = new LinkedHashMap<>();
      if(!isTvlAdmin(companyId, loggedInUser)) {
       params.put("usersOrgs", ArrayUtils.toObject(loggedInUser.getOrganizationIds()));
      }
      OrderByComparator orderByComparator = OrderByComparatorFactoryUtil.create("USER_", "status", false);
      int total = UserLocalServiceUtil.searchCount(companyId, query, statusIds, params);
       List<User> users = UserLocalServiceUtil.search(companyId, query, statusIds, params, 0, total,    orderByComparator);

      A test portlet has been provided and this issue has been reproduced using it.

      Steps to Reproduce:
      1. Configure Liferay to use DB2 10.1
      2. Set dialect in portal-ext: hibernate.dialect=org.hibernate.dialect.DB2400Dialect
      3. Start Liferay
      4. Deploy attached NewPortlet
      5. Add 'New' portlet to a page

      Expected:
      Query executes successfully.
      Actual:
      Query does not execute successfully with "org.hibernate.exception.SQLGrammarException: could not execute query" in logs. (Full stacktrace is attached as db2_order_by_error.txt)

      For master / ee-7.0.x, this portal property must be set, as the default behavior is to do an index search:

      users.search.with.index=false

      The error code that is reported says:

      DISTINCT is specified in the SELECT clause, and either a column name in the ORDER BY clause cannot be matched exactly with a column name in the select list, or a sort-key-expression is specified in the ORDER BY clause. This reason code occurs only when clause-type is ORDER BY.
      

      (from https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.codes/src/tpc/n214.html)

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  3 years, 13 weeks, 6 days ago

                  Packages

                  Version Package
                  6.2.X EE
                  7.0.0 DXP FP1
                  7.0.2 CE GA3
                  7.0.0 DXP SP1
                  7.0.3 CE GA4
                  7.1.X
                  Master