[hibernate-issues] [JIRA] (BVAL-787) Alias XXX used for multiple from-clause elements error when executing query with 2 level join

Oleksii Miroshnyk ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a157d9... ) *created* an issue Bean Validation ( https://hibernate.atlassian.net/browse/BVAL?atlOrigin=eyJpIjoiY2E1NjljMmZ... ) / Bug ( https://hibernate.atlassian.net/browse/BVAL-787?atlOrigin=eyJpIjoiY2E1Njl... ) BVAL-787 ( https://hibernate.atlassian.net/browse/BVAL-787?atlOrigin=eyJpIjoiY2E1Njl... ) Alias XXX used for multiple from-clause elements error when executing query with 2 level join ( https://hibernate.atlassian.net/browse/BVAL-787?atlOrigin=eyJpIjoiY2E1Njl... ) Issue Type: Bug Assignee: Unassigned Created: 10/May/2023 08:22 AM Environment: java 17, spring boot 3, hibernate 6.1.7 Priority: Blocker Reporter: Oleksii Miroshnyk ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a157d9... ) Seems there is a bug somewhere either in query parsing or joins processing in hibernate 6.1.7+ That code & query works fine in all prev. versions from hibernate 3 to 5 I have a named query: ``` <query>SELECT COUNT(adminPermission) FROM org.broadleafcommerce.openadmin.server.security.domain.AdminPermission adminPermission LEFT OUTER JOIN adminPermission.allChildPermissions childPermission LEFT OUTER JOIN childPermission.qualifiedEntities childQualifiedEntity LEFT OUTER JOIN adminPermission.qualifiedEntities qualifiedEntity LEFT OUTER JOIN adminPermission.allRoles role LEFT OUTER JOIN role.allUsers roleUser LEFT OUTER JOIN adminPermission.allUsers permissionUser WHERE (roleUser = :adminUser OR permissionUser = :adminUser) AND (adminPermission.type = :type OR adminPermission.type = 'ALL' OR childPermission.type = :type OR childPermission.type = 'ALL') AND (qualifiedEntity.ceilingEntityFullyQualifiedName = :ceilingEntity OR childQualifiedEntity.ceilingEntityFullyQualifiedName = :ceilingEntity) </query> ``` There OneToMany and ManyToMany through JoinTable asossiations it this is important I debugged a bit and see that it processes condition (roleUser = :adminUser OR permissionUser = :adminUser) and finds a join LEFT OUTER JOIN role.allUsers roleUser so it finds alias "roleUser", logic goes into org.hibernate.query.hql.internal.QuerySplitter.UnmappedPolymorphismReplacer#visitSetJoin where it does: final SqmSetJoin copy = new SqmSetJoin<>( findSqmFromCopy( join.getLhs() ), so join.getLhs() will return another join LEFT OUTER JOIN adminPermission.allRoles role so it finds alias "role" And in org.hibernate.query.hql.internal.QuerySplitter.UnmappedPolymorphismReplacer#findSqmFromCopy it does return (X) sqmFrom.accept( this ); So it will do a recursive call that will eventually come to org.hibernate.query.hql.internal.QuerySplitter.UnmappedPolymorphismReplacer#visitSetJoin again, where it will process join with alias "role" and in org.hibernate.query.hql.internal.QuerySplitter.UnmappedPolymorphismReplacer#visitJoins will register that alias, and also it will do join.visitSqmJoins( sqmJoin -> sqmJoin.accept( this ) ); where "role" join will reference "roleUser" join via joins.forEach( consumer ); in org.hibernate.query.sqm.tree.domain.AbstractSqmFrom#visitSqmJoins it will process it and also registers via org.hibernate.query.hql.internal.SqmPathRegistryImpl#register(org.hibernate.query.sqm.tree.domain.SqmPath<?>) so now registry map knows about "role" and "roleUser" joins and recursion ends and it eventually continues to process "roleUse" join - where we start recursion... and it again will attempt to register org.hibernate.query.hql.internal.SqmPathRegistryImpl#register(org.hibernate.query.sqm.tree.domain.SqmPath<?>) where it fails with exception... So there is something wrong on how "multi-level joins" are handled. ( https://hibernate.atlassian.net/browse/BVAL-787#add-comment?atlOrigin=eyJ... ) Add Comment ( https://hibernate.atlassian.net/browse/BVAL-787#add-comment?atlOrigin=eyJ... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100225- sha1:55ea0c5 )