Distinct criteria query stops working w/ 3.4.3

[jochenberger]

I have a criteria query that returns distinct results and sorts by an associated entity's property (e.g. owner.name) with a respective PageRequest.
When I upgrade to Spring Boot (Data JPA) 3.4.3, I get ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list from PostgreSQL.
If I downgrade Hibernate to a version before 6.6.6.Final, the error does not occur.

[jochenberger]

This may be related to https://hibernate.atlassian.net/issues/HHH-19034. My query uses root.fetch("owner"). Without that, the error also occurs with earlier Hibernate versions.

[mp911de]

Hibernate has made a few changes to fetch joins. We've seen a few tickets reporting that change in the past. Care to post the full criteria query (a simplified variant that is able to reproduce the issue?

[jochenberger]

I'll see what I can do to create a minimal example. Do you mean just the generated SQL query? Or the code that creates it using CriteriaBuilder?

[jochenberger]

The point is that I'm trying to sort by a property of an associated entity. That property does not end up in the select list that's apparently being auto-determined.

[mp911de]

the code that creates it using CriteriaBuilder. It might be that we could be able to reuse fetch joins by a small change in our QueryUtils helper that creates the sort order expressions.

[jochenberger]

Alright, this is a reduced example:

public class ControlImplementationQueryImpl implements ControlImplementationQuery {

  private final ControlImplementationDataRepository repo;
  protected Specification<ControlImplementationData> spec;

  public ControlImplementationQueryImpl(
      ControlImplementationDataRepository repo, Client notUsedInRepro1, UUID notUsedInRepro2) {
    this.repo = repo;
    spec =
        (root, query, criteriaBuilder) -> {
          query.distinct(true);
          return criteriaBuilder.isNotNull(root.get("owner"));
        };
  }

  @Override
  @Transactional(readOnly = true)
  public PagedResult<ControlImplementation, String> execute(
      PagingConfiguration<String> pagingConfiguration) {
    var items = repo.findAll(spec, PageRequest.of(0, 10, Direction.ASC, "owner.name"));
    return new PagedResult<>(
        pagingConfiguration,
        items.stream().map(ControlImplementation.class::cast).toList(),
        items.getTotalElements(),
        items.getTotalPages());
  }
}

It generates the following query:

select distinct cid1_0.db_id,cid1_0.control_id,cid1_0.description,cid1_0.id,cid1_0.owner_db_id,cid1_0.requirement_implementations,cid1_0.person_id 
  from control_implementation cid1_0 
  join (select * from element t where t.dtype in ('process', 'asset', 'scope')) o1_0 
    on o1_0.db_id=cid1_0.owner_db_id where o1_0.db_id is not null
  order by o1_0.name offset ? rows fetch first ? rows only

Let me know if you need anything else.

[jochenberger]

I did not include the root.fetch("owner") part. If I add that, it works with Hibernate < 6.6.6.

[mp911de]

Thank you. I think that you have to use fetch joins to compare collections properly as those need to be fully fetched. It then makes sense in our QueryUtils to verify for fetch joins now that Hibernate enforces fetch join usage. See also https://hibernate.atlassian.net/browse/HHH-13434 for a similar discussion.

[jochenberger]

Alright, I added it back. Now it looks like this:

public class ControlImplementationQueryImpl implements ControlImplementationQuery {

  private final ControlImplementationDataRepository repo;
  protected Specification<ControlImplementationData> spec;

  public ControlImplementationQueryImpl(
      ControlImplementationDataRepository repo, Client notUsedInRepro1, UUID notUsedInRepro2) {
    this.repo = repo;
    spec =
        (root, query, criteriaBuilder) -> {
          query.distinct(true);
          root.fetch("owner");
          return criteriaBuilder.isNotNull(root.get("owner"));
        };
  }

  @Override
  @Transactional(readOnly = true)
  public PagedResult<ControlImplementation, String> execute(
      PagingConfiguration<String> pagingConfiguration) {
    var items = repo.findAll(spec, PageRequest.of(0, 10, Direction.ASC, "owner.name"));
    return new PagedResult<>(
        pagingConfiguration,
        items.stream().map(ControlImplementation.class::cast).toList(),
        items.getTotalElements(),
        items.getTotalPages());
  }
}

and it generates that:

SELECT DISTINCT 
    cid1_0.db_id,
    cid1_0.control_id,
    cid1_0.description,
    cid1_0.id,
    cid1_0.owner_db_id,
    o1_0.db_id,
    o1_0.dtype,
    o1_0.abbreviation,
    o1_0.change_number,
    o1_0.created_at,
    o1_0.created_by,
    o1_0.description,
    o1_0.designator,
    CASE 
        WHEN o1_0.abbreviation IS NULL 
        THEN CONCAT(o1_0.designator, ' ', o1_0.name) 
        ELSE CONCAT(o1_0.designator, ' ', o1_0.abbreviation, ' ', o1_0.name) 
    END AS full_name,
    o1_0.name,
    o1_0.owner_id,
    o1_0.updated_at,
    o1_0.updated_by,
    o1_0.version,
    cid1_0.requirement_implementations,
    cid1_0.person_id 
FROM control_implementation cid1_0
JOIN (
    SELECT * FROM element t 
    WHERE t.dtype IN ('process', 'asset', 'scope')
) o1_0 ON o1_0.db_id = cid1_0.owner_db_id
JOIN (
    SELECT * FROM element t 
    WHERE t.dtype IN ('process', 'asset', 'scope')
) o2_0 ON o2_0.db_id = cid1_0.owner_db_id
WHERE o1_0.db_id IS NOT NULL
ORDER BY o2_0.name 
OFFSET ? ROWS 
FETCH FIRST ? ROWS ONLY;

With S-B 3.4.2, it generates

SELECT DISTINCT 
    cid1_0.db_id,
    cid1_0.control_id,
    cid1_0.description,
    cid1_0.id,
    cid1_0.owner_db_id,
    o1_0.db_id,
    o1_0.dtype,
    o1_0.abbreviation,
    o1_0.change_number,
    o1_0.created_at,
    o1_0.created_by,
    o1_0.description,
    o1_0.designator,
    CASE 
        WHEN o1_0.abbreviation IS NULL 
        THEN CONCAT(o1_0.designator, ' ', o1_0.name) 
        ELSE CONCAT(o1_0.designator, ' ', o1_0.abbreviation, ' ', o1_0.name) 
    END AS full_name,
    o1_0.name,
    o1_0.owner_id,
    o1_0.updated_at,
    o1_0.updated_by,
    o1_0.version,
    cid1_0.requirement_implementations,
    cid1_0.person_id 
FROM control_implementation cid1_0
JOIN (
    SELECT * FROM element t 
    WHERE t.dtype IN ('process', 'asset', 'scope')
) o1_0 ON o1_0.db_id = cid1_0.owner_db_id
WHERE o1_0.db_id IS NOT NULL
ORDER BY o1_0.name 
OFFSET ? ROWS 
FETCH FIRST ? ROWS ONLY;

So, yes, it does look as if the join isn't being re-used. Can I do something to influence that?

[mp911de]

Neither Spring Data nor Spring Boot generates SQL from JPA/JPQL queries, Hibernate does and therefore, we do not have direct influence on the generated SQL statements. Also, Hibernate is the component to consider SQL constraints for the individual databases.

If you replace the sort part in PageRequest.of(…) (i.e. remove , Direction.ASC, "owner.name" ) and add query.orderBy(cb.asc((((Path) root.fetch("owner")).get("name")))) instead to your specification, does it render a working query?