I have quite confusing entity mapping, so i failed with reproducing it by Test case
It works on 6.2.2, but failed on 6.2.3
I build quiery by SpringData Specifications and following example generates wrong SQL
Below some kind of pseudo code, that you can’t run, but that could build some picture of entities links
@Entity
@Table(name = "USOI_TREE_ROOT")
@DiscriminatorColumn(name = "TYPE_ID")
public abstract class BaseTreeRoot extends CategoryMember implements Cloneable, Serializable {
}
@Entity
@Table(name = "USOI_ABONENT_TREES")
public class TreeVersion extends CategoryMember {
private VersionedTreeRoot root;
}
@Entity
@DiscriminatorValue(value = "1")
public class VersionedTreeRoot extends BaseTreeRoot {
private List<TreeVersion> versions;
}
@Entity
@DiscriminatorValue(value = "2")
public class DynamicTreeRoot extends BaseTreeRoot {
}
and code to build Specification
public class TreeRootSpecificationBuilder extends BaseSpecificationBuilder {
private String pattern;
private Collection<Long> ids;
private Long categoryId;
private String categoryCode;
private boolean includeFromGlobal;
private String rootType;
public TreeRootSpecificationBuilder setPattern(String pattern) {
this.pattern = pattern;
return this;
}
public TreeRootSpecificationBuilder setIds(Collection<Long> ids) {
this.ids = ids;
return this;
}
public TreeRootSpecificationBuilder setCategoryId(Long categoryId) {
this.categoryId = categoryId;
return this;
}
public TreeRootSpecificationBuilder setCategoryCode(String categoryCode) {
this.categoryCode = categoryCode;
return this;
}
public TreeRootSpecificationBuilder setIncludeFromGlobal(boolean includeFromGlobal) {
this.includeFromGlobal = includeFromGlobal;
return this;
}
public TreeRootSpecificationBuilder setRootType(String rootType) {
this.rootType = rootType;
return this;
}
public Specification<BaseTreeRoot> build() {
Specification<BaseTreeRoot> stringSpecification = getBuilder(StringPropertySpecificationBuilder.class)
.setPattern(pattern)
.build(Arrays.asList(BaseTreeRoot_.name, BaseTreeRoot_.code));
Specification<BaseTreeRoot> idSetSpecification = getBuilder(IdSetSpecificationBuilder.Long.class)
.setIds(ids)
.build(BaseTreeRoot_.id);
Specification<BaseTreeRoot> categorySpecification = getBuilder(CategoryPropertySpecificationBuilder.class)
.setCategoryId(categoryId)
.setCategoryCode(categoryCode)
.setIncludeFromGlobal(includeFromGlobal)
.build(BaseTreeRoot_.category);
Specification<BaseTreeRoot> rootTypeSpecification;
Specification<BaseTreeRoot> accessSpecification;
if (!StringUtils.isEmpty(rootType)) {
long type = switch (rootType) {
case "version" -> BaseTreeRoot.VersionedType;
case "dynamic" -> BaseTreeRoot.DynamicType;
default -> throw new IllegalArgumentException("Unsupported rootType " + rootType);
};
rootTypeSpecification = (root, query, cb) -> cb.equal(root.get(BaseTreeRoot_.type), type);
// При поиске рутов версионных деревьев, добавляем проверку на доступ пользователя абонентам из деревьев этих рутов
if (type == BaseTreeRoot.VersionedType) {
accessSpecification = (root, query, cb) -> {
Subquery<Long> accessNodes = query.subquery(Long.class);
Root<BaseTreeRoot> subQueryRoot = accessNodes.from(BaseTreeRoot.class);
accessNodes.select(subQueryRoot.get(BaseTreeRoot_.id));
Root<VersionedTreeRoot> treatedRoot = cb.treat(subQueryRoot, VersionedTreeRoot.class);
Join<VersionedTreeRoot, TreeVersion> joinTrees = treatedRoot.join(VersionedTreeRoot_.versions);
Join<TreeVersion, AbonentTreeNode> joinNodes = joinTrees.join(TreeVersion_.nodes);
accessNodes.where(
cb.equal(joinNodes.get(AbonentTreeNode_.treeVersion).get(TreeVersion_.root).get(BaseTreeRoot_.id), root.get(BaseTreeRoot_.id))
);
return cb.exists(accessNodes);
};
} else {
accessSpecification = null;
}
} else {
rootTypeSpecification = null;
accessSpecification = null;
}
return addBinarySortOrder(Specification.where(stringSpecification)
.and(idSetSpecification)
.and(categorySpecification)
.and(rootTypeSpecification)
.and(accessSpecification), BaseTreeRoot_.code);
}
}
When SpecificationBuilder has defined field type that equals BaseTreeRoot.VersionedType, Hibernate builds following SQL:
select b1_0.id,
b1_0.TYPE_ID,
b1_0.KVD_ID,
b1_0.code,
b1_0.EXTERNAL_ID,
b1_0.external_system_id,
b1_0.name
from USOI_TREE_ROOT b1_0
join USOI_KVDS c1_0
on c1_0.id = b1_0.KVD_ID
where (b1_0.KVD_ID = ? or c1_0.GLOBAL = ?)
and b1_0.TYPE_ID = ?
and exists
(select b2_0.id
from (select * from USOI_TREE_ROOT t where t.TYPE_ID = '1') b2_0
join USOI_ABONENT_TREES v1_0
on b2_0.id = v1_0.ROOT_ID
join USOI_ABONENT_TREE_NODE n1_0
on v1_0.id = n1_0.TREE_ID
join USOI_ABONENT_TREES t1_0
on t1_0.id = n1_0.TREE_ID
where t1_0.ROOT_ID = b1_0.id
and b2_0.TYPE_ID = '1')
and b2_0.TYPE_ID = '1'
order by nlssort(upper(b1_0.code), 'NLS_SORT = BINARY')
And error is that last condition “and b2_0.TYPE_DI=‘1’” is out of exists subselect and alias b2_0 is not valid there.