6.2.3 brokes query building

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.

Can you please tell me what ((SqmSelectStatement) criteriaQuery).toHqlString() reports and share the model?

Hi. There is sqm

select alias_933501602
from com.asuproject.usoi.model.domain.trees.BaseTreeRoot alias_933501602
where (alias_933501602.category.id = 1594212 or alias_933501602.category.global = true)
  and alias_933501602.type = 1
  and exists(select alias_462250925.id
             from com.asuproject.usoi.model.domain.trees.BaseTreeRoot alias_462250925
             where alias_936627642.treeVersion.root.id = alias_933501602.id)
order by nlssort() asc nulls last

I even removed order by custom function nlssort, but got the same error.

Alias “alias_936627642” appeared fron nowhere. If this is not enought, i will send model tomorrow

Best regards. Alexey

Looks like there is a bug in the toHqlString method, but I just fixed it now: HHH-16676 Handle treat expressions in toHqlString by beikov · Pull Request #6623 · hibernate/hibernate-orm · GitHub

The query seems to be something like this though:

select base
from com.asuproject.usoi.model.domain.trees.BaseTreeRoot base
where (base.category.id = 1594212 or base.category.global = true)
  and base.type = 1
  and exists(
	select subBase.id
	from com.asuproject.usoi.model.domain.trees.BaseTreeRoot subBase
	join treat(subBase as VersionedTreeRoot).versions v
	join v.nodes n
	where n.treeVersion.root.id = base.id
)

I would very much appreciate if you could try to create a reproducer test based on this query and try to reduce it further, as well as attach that to a newly created JIRA issue.

Hi. I reproduced it and added testcase to task

https://hibernate.atlassian.net/browse/HHH-16676

1 Like

I created a new JIRA issue for the actual problem. You can track the progress here: [HHH-16680] - Hibernate JIRA

Hi. Is there some expectation about this issue?

I wonder if this is the same problem as HHH-16642 . Treated joins in update statements give very similar invalid SQL.