I'm building a JAva 17 Spring Boot App using a MySQL database. As part of the app, I have a central Entity (let's call it centralEntity) with a number of other Entities connected (10 OneToOne-Relations, 3 OneToMany-Relations).

Every entity in this database is supposed to be @Audited, and the majority are FetchType.Lazy (don't know if this is relevant, cannot do anything about it, just added for completeness sake)

As I also need searchability and am currently testing implementing the ValidityAuditStrategy (The normal DB already has 700k entries and using the DefaultAuditStrategy for an AuditQuery basically never finished in a human lifetime (or at least not over the span of 30 hours when I had it run for a weekend)), I've added the corresponding REVEND columns for each entity. Spring Boot correctly starts up and recognizes them, using the new Audit Strategy.

Search results are very fast, when all revisionnumbers align.

However, for workplace-related reasons, I need to make sure I can basically get each entity by itself out as well, by giving it a revisionNumber and it going down the list of possible entries for that entityID and giving the "closest" or last entry found to create the full object.

This is where my problem starts. Whenever I try to do this for the centralEntity with the following code:

private List returnCorrectOrFittingRevisionEntry(Class<?> clazz, Long entityID, Number revisionNumber)
AuditReader auditReader = AuditReaderFactory.get(entityManager);
List list = 
auditReader.createQuery.forRevisionsofEntity(clazz, false, false)
.add(AuditEntity.id().eq(entityID))
.add(AuditEntity.revisionNumber().desc())
.getResultList();
return list;

I get a unique ResultException. I can see, while debugging, that he does find both entities with their different result values, but 1. He somehow starts from the bottom, instead of going down from the revisionNumber I gave him and 2. If I force him to go from equalToOrLess from the revisionNumber I gave him, I immediately hit the NonUniqueResultException.

This is driving me insane. How can I fix this?

What I tried: Tried to use Hibernate Envers to get the latest revision of an audited entity based on a given revisionNumber counting down.

What I expected: I get the latest revision of the audited entity with a state based on the revisionNumber of lower (older if only older copies exist)

What I got: As described before, an NonUniqueResultException that I do not understand. Like, both AuditEntries for centralEntity have differing revNumbers (4 and 5) and refTypes (Insert and Mod).