Aliases expected length is 0; actual length is 1 on hibernate query cache

Question

String q1Str = "SELECT parent_id FROM meta WHERE st_id = "+childIds+";";
Query q1 = em.createNativeQuery(q1Str);
//q1.setHint("org.hibernate.cacheable", true);
Object parentId = null;
try{
parentId = q1.getSingleResult();
}catch(NoResultException nre){
    //nope
}

enabling hibernate.cacheable will throw me the following exception

aliases expected length is 0; actual length is 1

Answer 1

So, this happened to me too and after searching / researching a possible solution I've decided to give up, but after a very long debug session (not Session.class) I stumbled upon a solution.

And so... The environment

hibernate-core 5.3.0.Final

Wildfly 20.0.1 Final

Java 11

PostgreSql 9.4 ? (don't know the exact version, sorry)

Infinispan for caching

So, first using

em.createNamedQuery("MyClass.findMyStuff", Tuple.class)
            .setParameter(0, declarationDate)
            .setHint("org.hibernate.cacheable", true)
            .getResultList()

I received an error similar to this

aliases expected length is 0; actual length is 1

but, of course, with different numbers (instead of 0 and 1)

Note: my native sql was like this

select a.date as mydate, a.id as myid, a.test as mytest from mytable a where date = ?0

And so, something smelly hit the fan...

Tried this

em.createNamedQuery("MyClass.findMyStuff", Tuple.class)
            .setParameter(0, declarationDate)
            .setHint("org.hibernate.cacheable", true)
            .unwrap(NativeQuery.class)
            .addSynchronizedQuerySpace("myQuery_space")
            .getResultList()

and I failed... Tried this same stuff with all addSynchronized methods available in NativeQuery.class, but each and every time hibernate was telling me to go and fun myself.

Then I tried rewriting my query with HQL, but good luck with that... Rewriting all these subselects, joins and what not in HQL was just a wishful thinking - NOT POSSIBLE (in my case. Note that resources I have are limited).

Got back to where I've started... And changed this

em.createNamedQuery("MyClass.findMyStuff", Tuple.class)
            .setParameter(0, declarationDate)
            .setHint("org.hibernate.cacheable", true)
            .unwrap(NativeQuery.class)
            .addSynchronizedQuerySpace("myQuery_space")
            .getResultList()

to

em.createNamedQuery("MyClass.findMyStuff", Tuple.class)
            .setParameter(0, declarationDate)
            .setHint("org.hibernate.cacheable", true)
            .unwrap(NativeQuery.class)
            .addScalar("mydate")
            .getResultList()

and got the same error similar to

aliases expected length is 0; actual length is 1

And then I tried this

em.createNamedQuery("MyClass.findMyStuff", Tuple.class)
            .setParameter(0, declarationDate)
            .setHint("org.hibernate.cacheable", true)
            .unwrap(NativeQuery.class)
            .addScalar("mydate", new DateType())
            .getResultList()

And BAAAAAAM finally it started working.

To sum it up it started working then I unwrapped query to NativeQuery and added scalar with a type!!!

Note if your native query does not have aliases then add them!!!

Related threads (to name a few)

https://hibernate.atlassian.net/browse/HHH-9111

Hibernate Ehcache NOT working for SQL Native query cache

Hibernate query cache applicable for native queries?

Cache JPA Native Query