I am trying to implement annotation based soft delete in hibernate, in this case on a many-to-many relationship. Up until now it has worked very well, but I encountered an odd condition that I am wondering if it's a bug or I am simply misinterpreting the usage.

The problem comes when I try to execute a find on an entity (A) and the soft-delete-where for the child (B) is injected to the overall where for the base entity in the actual SQL output of the find. The end result of this is if the A does not have any Bs, the soft-delete-where causes the main find query to return no As, even if they exist.

Here is an example of the annotations used in A (simplified for readability):

@Entity
@SQLDelete(sql = "UPDATE List SET isActive = 0 WHERE id = ? AND version = ?")
@Where(clause = "isActive = 1")
public class List extends AbstractEntity

And here are the annotations used in B (also simplified for readability):

@Entity
@SQLDelete(sql = "UPDATE AuthGroup SET isActive = 0 WHERE id = ? AND version = ?")
@Where(clause = "isActive = 1")
public class AuthGroup extends AbstractEntity {

@ManyToMany(cascade =

, mappedBy = "authGroups")
@JsonIgnore
private Set<List> lists = new HashSet<List>();
}

Here is the SQL output from logs from a simple getSession().get(List.class, id);

select list0_.id as id1_13_0_, list0_.createdAt as createdA2_13_0_, list0_.createdBy as createdB3_13_0_, list0_.isActive as isActive4_13_0_, list0_.modifiedAt as modified5_13_0_, list0_.updatedBy as updatedB6_13_0_, list0_.version as version7_13_0_, list0_.bounceLimit as bounceLi8_13_0_, list0_.code as code9_13_0_, list0_.defaultFrom as default10_13_0_, list0_.description as descrip11_13_0_, list0_.listType as listTyp12_13_0_, list0_.name as name13_13_0_, list0_.owningOrgId as owningO20_13_0_, list0_.seed as seed14_13_0_, list0_.seedThreshold as seedThr15_13_0_, list0_.sendingDomain as sending16_13_0_, list0_.trackerDomain as tracker17_13_0_, list0_.unsubDomain as unsubDo18_13_0_, list0_.xBinding as xBindin19_13_0_, authgroups1_.listId as listId1_14_1_, authgroup2_.id as authGrou2_14_1_, authgroup2_.id as id1_2_2_, authgroup2_.createdAt as createdA2_2_2_, authgroup2_.createdBy as createdB3_2_2_, authgroup2_.isActive as isActive4_2_2_, authgroup2_.modifiedAt as modified5_2_2_, authgroup2_.updatedBy as updatedB6_2_2_, authgroup2_.version as version7_2_2_, authgroup2_.code as code8_2_2_, authgroup2_.name as name9_2_2_, owningorg3_.id as id1_18_3_, owningorg3_.createdAt as createdA2_18_3_, owningorg3_.createdBy as createdB3_18_3_, owningorg3_.isActive as isActive4_18_3_, owningorg3_.modifiedAt as modified5_18_3_, owningorg3_.updatedBy as updatedB6_18_3_, owningorg3_.version as version7_18_3_, owningorg3_.code as code8_18_3_, owningorg3_.companyId as company11_18_3_, owningorg3_.description as descript9_18_3_, owningorg3_.name as name10_18_3_, company4_.id as id1_4_4_, company4_.createdAt as createdA2_4_4_, company4_.createdBy as createdB3_4_4_, company4_.isActive as isActive4_4_4_, company4_.modifiedAt as modified5_4_4_, company4_.updatedBy as updatedB6_4_4_, company4_.version as version7_4_4_, company4_.description as descript8_4_4_, company4_.name as name9_4_4_

from List list0_

left outer join ListAuthGroup authgroups1_ on list0_.id=authgroups1_.listId

left outer join AuthGroup authgroup2_ on authgroups1_.authGroupId=authgroup2_.id

left outer join OwningOrg owningorg3_ on list0_.owningOrgId=owningorg3_.id

left outer join Company company4_ on owningorg3_.companyId=company4_.id

where ( authgroup2_.isActive = 1) and list0_.id=? and ( list0_.isActive = 1)

##########

I don't know if maybe I'm looking at it all wrong, but just because a child is not active should not mean a parent doesn't exist. My expectation would be one of two things, either it should be putting the active in the ON of the join, or it should be splitting this into two queries (one for the List and one for the children).

I tested removing the ( authgroup2_.isActive = 1) from the WHERE and it returns results, where this returns none.

Any help in either understanding that I am using this wrong, or a possible future fix would be greatly appreciated.