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

Journal countByG_C_S and findByG_C_S methods use different sql which could generate different results

    Details

      Description

      the find method takes into account only the latest version through a left join, but the count method looks in all versions and then does a distinct on the article Id.

      			SELECT
      				COUNT(DISTINCT JournalArticle.articleId) AS COUNT_VALUE
      			FROM
      				JournalArticle
      			WHERE
      				(groupId = ?) AND
      				(classNameId = ?) AND
      				([$DDM_STRUCTURE_KEY$]) AND
      				([$STATUS$])
      

      finds any article (including version history) that match the where clause and then filters the article Ids to be distinct,

      but

      			SELECT
      				JournalArticle.*
      			FROM
      				JournalArticle
      			LEFT JOIN JournalArticle tempJournalArticle ON
      				[$STATUS_JOIN$] AND
      				(JournalArticle.groupId = tempJournalArticle.groupId) AND
      				(JournalArticle.articleId = tempJournalArticle.articleId) AND
      				(JournalArticle.version < tempJournalArticle.version)
      			WHERE
      				(JournalArticle.groupId = ?) AND
      				(JournalArticle.classNameId = ?) AND
      				([$DDM_STRUCTURE_KEY$]) AND
      				([$STATUS$]) AND
      				(tempJournalArticle.id_ IS NULL)
      			ORDER BY
      				JournalArticle.id_ ASC
      

      finds only the latest versions through the left inner join.

      the results should be the same between the count and find methods so the count should be changed to the following:

      			SELECT
      				COUNT(DISTINCT JournalArticle.articleId) AS COUNT_VALUE
      			FROM
      				JournalArticle
      			LEFT JOIN JournalArticle tempJournalArticle ON
      				[$STATUS_JOIN$] AND
      				(JournalArticle.groupId = tempJournalArticle.groupId) AND
      				(JournalArticle.articleId = tempJournalArticle.articleId) AND
      				(JournalArticle.version < tempJournalArticle.version)
      			WHERE
      				(JournalArticle.groupId = ?) AND
      				(JournalArticle.classNameId = ?) AND
      				([$DDM_STRUCTURE_KEY$]) AND
      				([$STATUS$]) AND
      				(tempJournalArticle.id_ IS NULL)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              felix.zhang Felix Zhang
              Reporter:
              andrew.betts Andrew Betts
              Participants of an Issue:
              Recent user:
              Marta Elicegui
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                5 years, 8 weeks, 3 days ago

                  Packages

                  Version Package
                  6.2.X EE
                  7.0.0 M6