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

Query error using DISTINCT and ORDER BY

    Details

    • Branch Version/s:
      6.0.x
    • Backported to Branch:
      Committed

      Description

      It is necessary to specify the table name for fields included in ORDER BY clause because a query error is thrown when the following preconditions are fulfilled:

      • Postgre Database
      • Select using DISTINCT
      • Use alias for fields
      • Use INNER JOIN
      • Use ORDER BY

      For example the following query causes an error:
      select distinct layout.plid as plid67_0_ from layout INNER JOIN group_ ON (layout.groupId = group_.groupId) where group_.groupId = 10162 order by plid

      Error displayed is:
      ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

      Postgre don't associate plid in ORDER BY clause with his alias in DISTINCT(plid67_0_). This query runs in other databases.

      Steps to reproduce (one of the cases)
      -------------------------------------
      1- Login with administrator user
      2- Go to control panel, in tags, create 2 tags, t1 and t2
      3- Associate this tags to two different blog entries
      4- Return to Guest and add Tag Navigation portlet
      5- Click in configuration and check "Show Asset Count" and select "Blog entry".

      Tags are not showed and an exception is displayed.

        Attachments

          Activity

            People

            Assignee:
            ryan.wan Ryan Wan (Inactive)
            Reporter:
            alberto.chaparro Alberto Chaparro
            Participants of an Issue:
            Recent user:
            Esther Sanz
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Days since last comment:
              10 years, 42 weeks, 2 days ago

                Packages

                Version Package
                6.0.X EE
                --Sprint 12/11
                6.1.0 CE RC1