fetchResults() wrong?

[shuaichouchou]

QueryDSL: 4.0.0

TerDefine.java

@entity
@table(name = "ter_define")
public class TerDefine {

@id
@GeneratedValue
private Integer id;

@column(nullable = false, length = 50, unique = true)
@Queryable(keyword = true)
private String terId;

@column(nullable = false, length = 200)
private String terDesc;
...
}

CheckRecord.java

@entity
@table(name="mkt_check_record")
public class CheckRecord {

@id
@GeneratedValue
private Integer seqNo;

@column(length = 50, nullable = false)
private String salesId;

@column(length = 50, nullable = false)
private String terId;

@column(length = 10, nullable = false)
private String checkInDate;

@column(length = 10)
private String checkOutDate;
...
}

When i try to do an aggregation query with some group by:

JPAQuery jpaQuery = new JPAQuery(em);
jpaQuery.select(qTerDefine.terId, qTerDefine.terDesc, qCheckRecord.seqNo.count(), qCheckRecord.checkOutDate.max());
jpaQuery.from(qCheckRecord, qTerDefine);
jpaQuery.where(qCheckRecord.salesId.eq('someone'), qCheckRecord.checkInDate.goe('2017/01/01'), qCheckRecord.checkOutDate.loe('2017/01/05'), qCheckRecord.terId.eq(qTerDefine.terId));
jpaQuery.groupBy(qTerDefine.terId, qTerDefine.terDesc);
jpaQuery.limit(1);
jpaQuery.offset(1);
jpaQuery.orderBy(qTerDefine.terId.asc(), qTerDefine.terDesc.asc());
QueryResults result = jpaQuery.fetchResults();

result :
terId terDesc seqNo.count checkOutDate.max
A A 1 2017/01/03
B B 2 2017/01/03
C C 2 2017/01/05

it return a wrong count query in fetchResults:

SELECT
COUNT (checkrecor0_.seq_no) AS col_0_0_
FROM
mkt_check_record checkrecor0_
CROSS JOIN ter_define terdefine1_
WHERE
checkrecor0_.sales_id ='someone'
AND checkrecor0_.check_in_date >='2017/01/01'
AND checkrecor0_.check_out_date <='2017/01/05'
AND checkrecor0_.ter_id = terdefine1_.ter_id
GROUP BY
terdefine1_.ter_id,
terdefine1_.ter_desc;

result total : 1

I hope that the results are as follows:

SELECT COUNT(col_0_0_)
FROM (
SELECT
COUNT (checkrecor0_.seq_no) AS col_0_0_
FROM
mkt_check_record checkrecor0_
CROSS JOIN ter_define terdefine1_
WHERE
checkrecor0_.sales_id ='someone'
AND checkrecor0_.check_in_date >='2017/01/01'
AND checkrecor0_.check_out_date <='2017/01/05'
AND checkrecor0_.ter_id = terdefine1_.ter_id
GROUP BY
terdefine1_.ter_id,
terdefine1_.ter_desc;
)

result total : 3

[Shredder121]

Could you make a reproduction project that we can investigate?

[jwgmeligmeyling]

Count query support has always been limited in QueryDSL and this behaviour is to be expected. As a result, count query is nominated for deprecation.