# Subquery randomly generating wrong SQL

**URL:** <https://discourse.hibernate.org/t/subquery-randomly-generating-wrong-sql/9585>\
**Category:** Hibernate ORM\
**Created:** [May 25, 2024, 10:54am UTC](https://discourse.hibernate.org/t/subquery-randomly-generating-wrong-sql/9585 "2024-05-25T10:54:26Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![pela](https://avatars.discourse-cdn.com/v4/letter/p/eb8c5e/32.png) [@pela](https://discourse.hibernate.org/u/pela)\
**Post date:** [May 25, 2024, 10:54am UTC](https://discourse.hibernate.org/t/subquery-randomly-generating-wrong-sql/9585/1 "2024-05-25T10:54:26Z")

</div>

Hi,  
We are experiencing a strange random behavior with a CriteriaQuery using Subquery. Our codebase is currently on Hibernate 6.4.4.

The query is done similarly to this:

```java
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<TestEntity> cq = cb.createQuery(TestEntity.class);
Root<TestEntity> outerRoot = cq.from(TestEntity.class);

Subquery<Integer> subquery = cq.subquery(Integer.class);
Root<TestEntity> subRoot = subquery.from(TestEntity.class);
subquery.select(cb.max(subRoot.get("internalVersion")))
		.where(cb.equal(subRoot.get("id"), outerRoot.get("id")));

final Predicate[] predicates = new Predicate[2];
predicates[0] = cb.equal(outerRoot.get("linkedId"), "link1");
predicates[1] = cb.equal(outerRoot.get("internalVersion"), subquery);
cq.where(predicates);

```

When working fine, the generated SQL is like this:

```auto
select te1_0.id,te1_0.internalVersion,te1_0.linkedId from TestEntity te1_0 where te1_0.linkedId=? and te1_0.internalVersion=(select max(te2_0.internalVersion) from TestEntity te2_0 where te2_0.id=te1_0.id)

```

But sometimes it randomly generates it like this:

```auto
select te1_0.id,te1_0.internalVersion,te1_0.linkedId from TestEntity te1_0 where te1_0.linkedId=? and te1_0.internalVersion=(select max(te2_0.internalVersion) from TestEntity te2_0 where te2_0.id=te2_0.id)

```

Note how the ‘where’ clause in the sub-select is “te2\_0.id=_ **te2\_0** _.id”, instead of “te2\_0.id=_te1\_0_.id”, thus making it completely useless!

Unfortunately this is happening randomly against the production code, we were not able to create a standalone reproducer to help with the investigation.

Has someone any idea of where the bug could be here? Or what additional information/logs we could provide to ease the investigation? I can raise a jira on the hibernate project, but I would not be able to attach a reproducer code at the moment.

Thanks

---

<div class="post-metadata">

**Author:** ![pela](https://avatars.discourse-cdn.com/v4/letter/p/eb8c5e/32.png) [@pela](https://discourse.hibernate.org/u/pela)\
**Post date:** [May 27, 2024, 8:19am UTC](https://discourse.hibernate.org/t/subquery-randomly-generating-wrong-sql/9585/2 "2024-05-27T08:19:17Z")

</div>

We may have found the culprit in this part of code creating aliases for the Root and Subquery objects:

(SqmCreationHelper.java)

```auto
	public static String determineAlias(String alias) {
		// Make sure we always create a unique alias, otherwise we might use a wrong table group for the same join
		if ( alias == null ) {
			return Long.toString( System.nanoTime() );
		}
		else if ( alias == IMPLICIT_ALIAS ) {
			return null;
		}
		return alias;
	} 

```

Strangely, in the failed tests, the two `System.nanoTime()` calls return the same value!

From Java specs, it is not guaranteed to return unique values:

> This method provides nanosecond precision, but not necessarily nanosecond resolution (that is, how frequently the value changes) - no guarantees are made except that the resolution is at least as good as that of [`currentTimeMillis()`](https://docs.oracle.com/javase%2F7%2Fdocs%2Fapi%2F%2F/java/lang/System.html#currentTimeMillis()).

I guess a bug should be raised for this. In the meantime, is there any workaround, for example assigning aliases explicitly to avoid this issue?

Thanks

---

<div class="post-metadata">

**Author:** ![pela](https://avatars.discourse-cdn.com/v4/letter/p/eb8c5e/32.png) [@pela](https://discourse.hibernate.org/u/pela)\
**Post date:** [May 27, 2024, 9:19am UTC](https://discourse.hibernate.org/t/subquery-randomly-generating-wrong-sql/9585/3 "2024-05-27T09:19:19Z")

</div>

Raised [[HHH-18170] - Hibernate JIRA](https://hibernate.atlassian.net/browse/HHH-18170)
