Subquery randomly generating wrong SQL

Hibernate ORM
1

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:

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:

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:

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

2

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

(SqmCreationHelper.java)

	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().

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

3

Raised [HHH-18170] - Hibernate JIRA

1 Like