Uploaded image for project: 'Cayenne'
  1. Cayenne
  2. CAY-2356

EJBQL: Incorrect COUNT() on outer joined table

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 4.0.B1, 4.1.M1
    • Fix Version/s: 4.0.B2, 4.1.M1
    • Component/s: Core Library
    • Labels:
      None

      Description

      This query:

      EJBQLQuery asc = new EJBQLQuery("select a, count(p) from Artist a LEFT JOIN a.paintingArray p GROUP BY a order by count(p) DESC");
      

      Produces following SQL:

      SELECT
          t0.ARTIST_NAME AS ec0_0, 
          t0.DATE_OF_BIRTH AS ec0_1,
          t0.ARTIST_ID AS ec0_2, 
          COUNT(*) AS sc1
      FROM
          ARTIST t0
          LEFT OUTER JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID)
      GROUP BY t0.ARTIST_NAME, t0.DATE_OF_BIRTH, t0.ARTIST_ID
      ORDER BY COUNT(*)
      

      Note COUNT( * ) that gives unexpected result, if there are no paintings it will return 1 instead of 0, COUNT(t1.PAINTING_ID) should be used instead.

        Attachments

          Activity

            People

            • Assignee:
              ntimofeev Nikita Timofeev
              Reporter:
              ntimofeev Nikita Timofeev
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: