Malformed SQL query sent to SQL Server with left outer join and pessimistic lock

Description

I can illustrate the problem I'm seeing with a simple entity called Person which has a self-join to its parent:

I've written a query using the JPA Criteria API to return a Person with their parent, locking both the child and parent rows:

Using either Hibernate 5.2.2-FINAL or Hibernate 4.3.11-FINAL running on JDK 8, the generated SQL query is corrupted:

I get the same result using JPQL.

This query worked as expected with Hibernate 4.3.11-FINAL running on JDK 7, generating the query:

I've attached a sample TestCase which highlights the problem.

Environment

Hibernate 5.2.2-Final
SQL Server 2008 Express jdk8

Activity

Show:
Gail Badner
January 9, 2017, 10:10 PM

Using 4.2.21, the generated SQL is wrong in a different way:

{{
select
person0_.id as id1_0_0_,
person1_.id as id1_0_1_,
person0_.parentId as parentId2_0_0_,
person1_.parentId as parentId2_0_1_
from
Person person0_
left outer join
person0_ with (updlock, rowlock )erson1_ with (updlock, rowlock )
on person0_.parentId=person1_.id
where
person0_.id=?
}}

Fails due to erson1_.

Andrea Boriero
January 10, 2017, 5:41 PM

the issue is related with

implementation, the alias substitution relies on the order of

being the same of the position of the aliases inside the sql String.
Unfortunately the aliases in

are stored in an

so with a Entity class named Person it seems that the person1_ alias is returned before the person0_, but in the sql string person0_ comes before person1_, so person1_ is substituted first and then a correction factor is applied to calculate the start position of person0_ causing the second lock string to be placed in the wrong position.

I'm going to submit a fix soon.

Assignee

Andrea Boriero

Reporter

Chris Abrams

Fix versions

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Minor
Configure