LEFT JOIN FETCH returns empty child list while running the query logged to console in DB client retu-CodePudding

I'm trying to fetch the data of parents along with all the children eagerly. I'm using LEFT JOIN FETCH. For some of the children, data is retrieved but there are a few for which an empty list is returned though there is data in the DB. In one use case, for one of the children, I get an empty list but there are records for a child in DB.

For sake of simplicity, I have removed other children from the below code.

Database: DB2

Parent Entity

@Entity
@Table(name = "INVENTORY_DATA", schema = "PREDICTIVE")
@Getter
@Setter
@NoArgsConstructor
public class InventoryDataEntity implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "INVENTORY_DATA_ID", insertable = false, updatable = false)
private Integer inventoryDataId;

@Embedded
@NaturalId
private InventoryDataEntityPK inventoryDataNaturalKey;

 // Some other Fields

@OneToMany(mappedBy = "inventoryDataEntity", cascade = {CascadeType.ALL}, orphanRemoval = true)
private Set<DriveEntity> driveEntities;

    // equals and hashcode
}

The Child Entity which has issues

@Entity
@Table(name = "DRIVE", schema = "COMP")
@Getter
@Setter
public class DriveEntity implements Serializable {

@EmbeddedId
private DriveEntityPK driveNaturalKey = new DriveEntityPK();

@Column(name = "DISKSERIAL")
private String diskSerial;

@Column(name = "FIRMWARE")
private String firmware;

// Other fields

@ManyToOne
@MapsId("inventoryDataId")
@JoinColumn(name = "INVENTORY_DATA_ID", referencedColumnName = "INVENTORY_DATA_ID")
protected InventoryDataEntity inventoryDataEntity;

@Override
public boolean equals(Object o) {
    if (this == o) {
        return true;
    }
    if (o == null || getClass() != o.getClass()) {
        return false;
    }
    DriveEntity that = (DriveEntity) o;
    return Objects.equals(driveNaturalKey, that.driveNaturalKey);
}

@Override
public int hashCode() {
    return Objects.hash(driveNaturalKey);
}
}

Query:

@Override
public List<InventoryDataEntity> findPaginatedDataList(int dataPageSize, int offset) {

    var inventoryDataIds = entityManager.createQuery(JPQLQueries.INVENTORY_DATA_ID, Integer.class)
            .setFirstResult(offset)
            .setMaxResults(dataPageSize).getResultList();

  **The issue comes in below part**

    var inv =  entityManager.createQuery("select distinct invd from InventoryDataEntity invd LEFT JOIN FETCH invd.driveEntities de where invd.inventoryDataId in (:ids)", InventoryDataEntity.class).setParameter("ids", inventoryDataIds).getResultList();

    return inv;
}

Query logged to the console:

select
distinct inventoryd0_.inventory_data_id as inventor1_13_0_,
inventoryd0_.machine_type as machin117_13_0_,
inventoryd0_.manufacturer as manufa118_13_0_,
inventoryd0_.model as model119_13_0_,
inventoryd0_.serial as serial120_13_0_,
driveentit1_.device_type as device_t1_5_1_,
driveentit1_.fru_pn as fru_pn2_5_1_,
driveentit1_.hardware_id as hardware3_5_1_,
driveentit1_.inventory_data_id as inventor4_5_1_,
driveentit1_.machine_type as machine_5_5_1_,
driveentit1_.model as model6_5_1_,
driveentit1_.ntap_pn as ntap_pn7_5_1_,
driveentit1_.part_number as part_num8_5_1_,
driveentit1_.product_id as product_9_5_1_,
driveentit1_.serial as serial10_5_1_,
driveentit1_.active_cluster as active_11_5_1_,
driveentit1_.brandcode_30 as brandco12_5_1_,
driveentit1_.critical_min_code_level as critica13_5_1_,
driveentit1_.critical_min_level as critica14_5_1_,
driveentit1_.disk_model as disk_mo15_5_1_,
driveentit1_.diskserial as diskser16_5_1_,
driveentit1_.drive_age_month as drive_a17_5_1_,
driveentit1_.drivename as drivena18_5_1_,
driveentit1_.firmware as firmwar19_5_1_,
driveentit1_.hb_status_code as hb_stat20_5_1_,
driveentit1_.heartbeatdate as heartbe21_5_1_,
driveentit1_.insert_ts as insert_22_5_1_,
driveentit1_.is_replaced as is_repl23_5_1_,
driveentit1_.is_replaced_ts as is_repl24_5_1_,
driveentit1_.king_cobra_installation_perc as king_co25_5_1_,
driveentit1_.min_code_level as min_cod26_5_1_,
driveentit1_.min_level as min_lev27_5_1_,
driveentit1_.right_diskserial as right_d28_5_1_,
driveentit1_.source_name as source_29_5_1_,
driveentit1_.target_code_level as target_30_5_1_,
driveentit1_.target_level as target_31_5_1_,
driveentit1_.update_ts as update_32_5_1_,
driveentit1_.inventory_data_id as inventor4_5_0__,
driveentit1_.device_type as device_t1_5_0__,
driveentit1_.fru_pn as fru_pn2_5_0__,
driveentit1_.hardware_id as hardware3_5_0__,
driveentit1_.machine_type as machine_5_5_0__,
driveentit1_.model as model6_5_0__,
driveentit1_.ntap_pn as ntap_pn7_5_0__,
driveentit1_.part_number as part_num8_5_0__,
driveentit1_.product_id as product_9_5_0__,
driveentit1_.serial as serial10_5_0__ 
from
   predictive.inventory_data inventoryd0_ 
left outer join
  comp.drive driveentit1_ 
    on inventoryd0_.inventory_data_id=driveentit1_.inventory_data_id 
where
  inventoryd0_.inventory_data_id in (
    ?
)
o.h.t.d.s.BasicBinder                    : binding parameter [1] as [INTEGER] - [223998]

Running same query in DB Client:

If anyone can please help me connect the dot that I'm missing over here.

CodePudding user response：

Update: We found the issue. The issue was The primary key of the Child Entity was composite. And one of the fields was null for All entries in DB. Hibernate marks a row null, even if a single column of the composite key is null. Data Team has changed the composite key and now all is good for me.