When using QueryDSL+CTE+LeftJoin, the CTE part isn't parsed correctly as a subquery while executing fetchCount()

[junhanlin]

Description

@Entity
@Table(name = "offers")
public class Offer {
    @Id
    @Column(name = "id")
    private String id;

    @Column(name = "base_unit_id")
    private String baseUnitId;

    @Column(name = "seq")
    private Integer seq;
}

@Entity
@Table(name = "units")
public class Unit {
    @Id
    @Column(name = "id")
    private String id;
}

@CTE
@Entity
public class UnitIdMaxSeqCTE {
    @Id
    private String unitId;
    private Integer maxSeq;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class TestResult {
    private Unit unit;
    private Integer maxSeq;
}

// Query that can reproduce the error:
long count = new BlazeJPAQueryFactory(em, cbf)
                .with(maxCte, new BlazeJPAQueryFactory(em, cbf)
		        .select(JPQLNextExpressions.bind(maxCte.unitId, of.baseUnitId),
                                JPQLNextExpressions.bind(maxCte.maxSeq, of.seq.max()))
                        .from(of)
                        .groupBy(of.baseUnitId))
                .select(Projections.constructor(TestResult.class, u, maxCte.maxSeq))
                .from(u)
                .leftJoin(maxCte).on(maxCte.unitId.eq(u.id))
                .orderBy(maxCte.maxSeq.asc())
                .fetchCount();

Expected behavior

It should returns the row count of the query.

Actual behavior

When the code reaches fetchCount(), an error message will be received:

Caused by: java.sql.SQLSyntaxErrorException: Table 'my_db.UnitIdMaxSeqCTE' doesn't exist
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
	... 127 common frames omitted

Here's the generated native SQL:

select count(*) as col_0_0_
from units unit0_
left outer join (
    select *
    from UnitIdMaxSeqCTE
) unitidmaxs1_ on ((null is null) and 999 = 999 and (unitidmaxs1_.unitId is null) and unitidmaxs1_.unitId = unit0_.id)

Another strange thing is that if you replace leftJoin in the syntax with innerJoin, it works fine.

Environment

Version: 1.6.9
JPA-Provider: Hibernate 5.4.27.Final
DBMS: MySQL 8
Application Server: Java SE
QueryDSL: 4.1.4

Additional Info

In order to simplify the reproduction steps for the error, the query syntax above has been simplified. The entire query may seem a bit confusing, for example, it uses complex Projection, CTE, and sorting, but in the end, only to fetchCount 😂

This is done because many people who use the QueryDSL + Spring Data JPA framework may use the frameworks’s applyPagination method for data pagination. Therefore, the complete usage scenario is actually like this:

public Page<TestResult> test(PageRequest pageRequest) {
        QOffer of = QOffer.offer;
        QUnit u = QUnit.unit;
        QUnitIdMaxSeqCTE maxCte = QUnitIdMaxSeqCTE.unitIdMaxSeqCTE;
        BlazeJPAQuery<TestResult> query = new BlazeJPAQueryFactory(em, cbf)
                .with(maxCte, new BlazeJPAQueryFactory(em, cbf)
                        .select(JPQLNextExpressions.bind(maxCte.unitId, of.baseUnitId),
                                JPQLNextExpressions.bind(maxCte.maxSeq, of.seq.max()))
                        .from(of)
                        .groupBy(of.baseUnitId))
                .select(Projections.constructor(TestResult.class, u, maxCte.maxSeq))
                .from(u)
                .leftJoin(maxCte).on(maxCte.unitId.eq(u.id))
                .orderBy(maxCte.maxSeq.asc());
        
        List<TestResult> result = new Querydsl(em, new PathBuilderFactory().create(TestResult.class))
                .applyPagination(pageRequest, query)
                .fetch();
        return new PageImpl<>(result, pageRequest, query.fetchCount());
    }

[jwgmeligmeyling]

@beikov this appears to be an issue with the multiplicity determination for CTE's during group by elimination for count queries in core, and not the Querydsl integration.

[beikov]

A reproducer that uses just the core CriteriaBuilder API would be very helpful.