Blaze-Persistence GROUP BY in LEFT JOIN SUBQUERY with COALESCE in root query

I need statistics about alert tagged by codes. Alerts are visible for users with group based restrictions. In case when some tag code is only in alerts which are invisible for user then 0 should be displayed in statisitics.

Tables structure:

┌─────────────────┐      ┌───────────┐     ┌─────┐
│ ALERT           │      │ ALERT_TAG │     │TAG  │
│ id              ├──────┤ alertId   ├─────┤code │
│ finalized       │      │ tag_code  │     └─────┘
│ assigneeGroupId │      └───────────┘
└─────────────────┘

I'm using Blaze-Persistence and try to make GROUP BY in LEFT JOIN SUBQUERY with COALESCE in root query. Reason of using Blaze-Persistence has support for subquery in left join.

This is my code:

criteriaBuilderFactory.create(entityManager, javax.persistence.Tuple.class)
            .from(Tag.class)
            .select("code")
            .select("COALESCE(at.tagCount, 0)")
            .leftJoinOnSubquery(AlertTagCTE.class, "at")
            .from(AlertTag.class)
            .bind("tagCode").select("tag.code")
            .bind("tagCount").select("count(tag.code)")
            .join("alert", "a", JoinType.INNER)
            .where("a.finalized").eq(false)
            .where("a.assigneeGroupId").in(userGroups)
            .groupBy("tag.code")
            .end()
            .end()
            .getQuery()
            .getResultList();

@Entity
@CTE
public class AlertTagCTE {

  @Id private String tagCode;
  private Long tagCount;

}

During execution I expect to get this query:

select t.code, nvl(atj.tag_count, 0) 
from tag t
left join (
    select alert_tag.tag_code, count(alert_tag.tag_code) as tag_count 
    from alert_tag 
    join alert ata 
    ON alert_tag.alert_id = ata.id 
    WHERE
        ata.finalized = 0 
        AND ata.assignee_group_id in (37 , 38 , 39 , 44 , 12 , 14 , 18 , 19 , 20 , 22 , 23 , 25 , 26 , 30) 
    group by alert_tag.tag_code
) atj on t.code = atj.tag_code
order by t.code;

but I get this one:

sqlselect tag0_.code as col_0_0_, nvl(alerttagct1_.tag_count, 0) as col_1_0_ 
from tag tag0_ 
left outer join (
    select null tag_code,null tag_count from dual where 1=0 union all (
        select alerttag0_.tag_code as col_0_0_, count(alerttag0_.tag_code) as col_1_0_ 
        from alert_tag alerttag0_ 
        inner join alert alert1_ on alerttag0_.alert_id=alert1_.id 
        where 
        alert1_.finalized=false 
        and (alert1_.assignee_group_id in (37 , 38 , 39 , 44 , 12 , 14 , 18 , 19 , 20 , 22 , 23 , 25 , 26 , 30)) 
        group by alerttag0_.tag_code
    )
) alerttagct1_ on ((null is null));

Results for both queries are different.

1. Why this union apper?
2. Does it's possible to get query without union in Blaze persistence API?

You are missing your on-condition in the query builder:

criteriaBuilderFactory.create(entityManager, javax.persistence.Tuple.class)
        .from(Tag.class)
        .select("code")
        .select("COALESCE(at.tagCount, 0)")
        .leftJoinOnSubquery(AlertTagCTE.class, "at")
            .from(AlertTag.class)
            .bind("tagCode").select("tag.code")
            .bind("tagCount").select("count(tag.code)")
            .join("alert", "a", JoinType.INNER)
            .where("a.finalized").eqLiteral(false)
            .where("a.assigneeGroupId").in(userGroups)
            .groupBy("tag.code")
        .end()
            .on("at.tagCode").eqExpression("t.code") // This was missing
        .end()
        .getQuery()
        .getResultList();

The union is just used for naming the columns but it shouldn't be an issue since it doesn't produce a result and the optimizer should be able to optimize it away. Do you see problems with that? The main reason for doing this is, that naming the items of the subquery would otherwise require a parsing and adapting of the SQL which is avoided as much as possible.

Databases like e.g. PostgreSQL support the use of aliases after the table alias. Apparently you are using a database that does not support this like e.g. Oracle or MySQL?