JPQL with join fetch returns the parent rows multiplied by how many children there are #3796
Closed as not planned
Description
Hello...im very confused about one thing, i try to explain simple:
I have two entities: TableA and TableB.
@Entity
@Table(name = "TableA")
@Data
public class TableA {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@OneToMany(mappedBy = "tableA",fetch = FetchType.LAZY)
private List<TableB> tableB;
}
package it.qrcode.common.entity;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
import lombok.Data;
@Entity
@Table(name = "TableB")
@Data
public class TableB{
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne
@JoinColumn(name = "id_tableA")
private TableA tableA;
}
Now the query that i execute is a JPQL with spring boot data jpa and it's very simple:
@Query("SELECT a from TableA a left join fetch a.tableB")
List<TableA> testWithJpqlJoinAndFetch();
Now imagine that there is one row in database of TableA and two rows of TableB that have the foreign key linking to that one row of TableA.
I thought that if i execute that query, i get one object of TableA, with the inner list of TableB with two objects.
But the error is that i get two rows of TableA because there are two rows of TableB,like a simple left join that you execute on database.
Why? I searched on google and someone said to use DISTINCT. With the DISTINCT it works, but seems to be a "cheat". What do you think? Thank you!!