Limit Annotation with FetchStrategy.SUBSELECT and Lateral Join support issues

[mj-p]

Hi, there is again something going on when using @Limit and this time when using it with FetchStrategy.SUBSELECT while having support for lateral joins like with Postgres. The example is the same like I provided in #1875 only changing the fetch type:

@EntityView(Person.class)
public interface PersonView {
    @IdMapping
    Long getId();

    String getName();

    AView getA();

    @Limit(limit = "4", order =  {"age DESC", "id DESC"})
    @Mapping(fetch = FetchStrategy.SUBSELECT)
    List<CatView> getKittens();
}

Now this works fine and would even be a valid workaround for the problem I described in #1875 if you have no lateral support like with H2. With lateral support the subquery will be inlined using FullSelectCTECriteriaBuilder resulting in a query like this:

SELECT c1_0.id,
       a3_0.id,
       count(DISTINCT b1_0.id),
       a3_0.name,
       c1_0.name,
       NULL,
       p1_0.id
FROM person p1_0
JOIN LATERAL
  (SELECT c2_0.a_id,
          c2_0.age,
          c2_0.father_id,
          ft1_0.id,
          c2_0.mother_id,
          c2_0.name,
          c2_0.owner_id
   FROM cat c2_0
   LEFT JOIN toy ft1_0 ON c2_0.id=ft1_0.cat_id
   WHERE c2_0.owner_id=p1_0.id
   ORDER BY c2_0.age DESC NULLS LAST, c2_0.id DESC 
   FETCH FIRST 4 ROWS ONLY) 
   c1_0(a_id, age, father_id, id, mother_id, name, owner_id) ON (1=1)
LEFT JOIN a a3_0 ON a3_0.id=c1_0.a_id
LEFT JOIN b b1_0 ON a3_0.id=b1_0.a_id
GROUP BY a3_0.id,
         c1_0.id,
         a3_0.id,
         a3_0.name,
         c1_0.name,
         p1_0.id

If you look closely not only is the inlined query overfetching but also binding the id of c1_0 to ft1_0.id which is a @OneToOne relation on the entity! So there are two issues when using @Limit with SUBSELECT while having lateral support.

1. Overfetching (here: father_id, mother_id)
2. @OneToOne relations on the entity may introduce unnecessary joins and then bind the id wrongly.

Disabling the lateral support via PostgreSQLDbmsDialect does help here but is not really a nice workaround.

Version: 1.6.11 JPA-Provider: Hibernate 6.4.1 DBMS: Postgres 16

[beikov]

If you look closely not only is the inlined query overfetching

Overfetching in a from-clause subquery should not be a problem. Optimizers usually prune away unnecessary select items. There is unfortunately no way to get rid of the overfetching here, because the subquery must produce something that is compatible with an entity fetch.

but also binding the id of c1_0 to ft1_0.id which is a @OneToOne relation on the entity!

I'm not sure I understand that. Could you be more specific? Also, I don't know the entity model you're using, so it's hard to understand what this "toy" table is representing.

[mj-p]

The relation looks like this: For Cat

@OneToOne(mappedBy = "cat")
private Toy favouriteToy;

For Toy

@Id
@GeneratedValue
private Long id;

@OneToOne
private Cat cat;

Now from the lateral join the binding for the Cat is this line with id being the 4th param c1_0(a_id, age, father_id, id, mother_id, name, owner_id)

And the fetched fields are those with the 4th field being ft1_0.id SELECT c2_0.a_id, c2_0.age, c2_0.father_id, ft1_0.id, c2_0.mother_id, c2_0.name, c2_0.owner_id

Which effectively sets the id of c1_0 to the id of ft1_0 and therefore results in a CatView with a wrong id. Meaning for example if your Cat Entity has id 1 and the Toy Entity has id 2 the query results in CatView with id 2.

[beikov]

Ok, this looks like a bug to me. Could you please try to create a reproducer for this? You can use a quickstart project for this purpose and attach it here.

[mj-p]

I hope this is enough to work with. I replaced the DbmsDialect of H2 with the PostgreSQL one in the Configuration to make sure the buggy query gets created. This will throw an exception in the end. If this is not enough, a real Postgres should be used.

blaze-persistence-reproducer-1876