Wrong entity reference order in JPQL from clause, when using any() in select

[tucek]

QLocationEntity l = QLocationEntity.locationEntity;

JPAQuery<LocationResult> query = queryFactory.select(
    Projections.constructor(LocationResult.class,
        Projections.bean(LocationEntity.class,
            l.id, l.name, l.title, l.municipalityName, l.municipalityId, l.type,
            l.coordName, l.wgs84Lat, l.wgs84Lon
        ),
        l.lines.any().lineType.sortWeight.sum()
    )
);

BooleanExpression ftsExpression = LocationPredicates.fullTextSearch(searchTerms);
BooleanExpression coordinateNotNull = LocationPredicates.isCoordinateNotNull();

query.from(l);
query.where(ftsExpression, coordinateNotNull);
query.groupBy(l.id, l.name, l.title, l.municipalityName, l.municipalityId, l.type, l.coordName, l.wgs84Lat, l.wgs84Lon, l.municipality.order, l.municipality.id);
query.orderBy(l.municipality.order.asc().nullsLast());
query.orderBy(l.municipality.id.desc().nullsLast());
query.orderBy(new Coalesce<Integer>(l.lines.any().lineType.sortWeight.sum()).add(0).desc().nullsLast());
query.orderBy(l.title.asc());
query.limit(limit);
return query.fetch();

produces the query:

select locationEntity.id, locationEntity.name, locationEntity.title, locationEntity.municipalityName, locationEntity.municipalityId, locationEntity.type, locationEntity.coordName, locationEntity.wgs84Lat, locationEntity.wgs84Lon, sum(locationEntity_lines_0.lineType.sortWeight)
from locationEntity.lines as locationEntity_lines_0, com.xxx.LocationEntity locationEntity
left join locationEntity_lines_0.lineType as locationEntity_lines_0_lineType
where ftsOnVector('german', locationEntity.textSearchable, ftsCleanText_german(?1)) = true and (locationEntity.wgs84Lat is not null and locationEntity.wgs84Lon is not null)
group by locationEntity.id, locationEntity.name, locationEntity.title, locationEntity.municipalityName, locationEntity.municipalityId, locationEntity.type, locationEntity.coordName, locationEntity.wgs84Lat, locationEntity.wgs84Lon, locationEntity.municipality.order, locationEntity.municipality.id
order by locationEntity.municipality.order asc nulls last, locationEntity.municipality.id desc nulls last, coalesce(sum(locationEntity_lines_0_lineType.sortWeight), ?2) desc, locationEntity.title asc

and therefore the exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: locationEntity.lines is not mapped 

because of

l.lines.any().lineType.sortWeight.sum()

in select clause.

When calling from, shouldn't the order be corrected?

[tucek]

If you need any further information, please let me know.

[timowest]

Thanks for the issue. I will try to replicate it and see what I can do.

[tucek]

If necessary, i can provide a working (failing) sample. Thanks in advance!

[johnktims]

That would be helpful!

[timowest]

I am not sure if this is possible to fix, since there is an implied aggregation here and it might be too much magic if we automatically add a group by if we encounter an any() expression in a aggregate expression.

[Shredder121]

Is it possible to validate this then?

[timowest]

We could maybe prohibit any() usage in the select part. It's primarily meant for usage in predicates.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.