[CAY-2356] EJBQL: Incorrect COUNT() on outer joined table

• 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
• Assignee: Nikita Timofeev
• Reporter: Nikita Timofeev
• Created: 29/Aug/17 07:32
• Updated: 10/Sep/17 11:42
• Resolved: 30/Aug/17 15:01

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.