Limit Annotation with FetchStrategy.SUBSELECT and Lateral Join support issues about blaze-persistence

[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