Hibernate Ehcache NOT working for SQL Native query cache

Question

I am getting error

aliases expected length is 1; actual length is 4
    at org.hibernate.transform.CacheableResultTransformer.transformTuple

I have JPA + Hibernate configurations and query cache and second level cache using Eh-Cache.

Configuration: PostgreSQL 9.6 + JPA 2.1 + Hibernate 5.2.3.Final

I am trying to execute NativeQuery with SqlResultSetMapping [custom result set class]. Everything is working fine when I am disabling cache.

But, getting above error on when I am enabling cache. Cache is working fine except for NativeQuery.

Table SCHEMA:

PK  first   second  third   

---

 1  A       abc     C       
 2  A       abc     C       
 3  A       xyz     D       
 4  B       abc     C       
 5  B       xyz     C       
 6  B       abc     D       
 7  A       xyz     C       
 8  A       abc     D     

SQL Native QUERY:

SELECT  t.first,t.second,
    COUNT(t.second) total,
    COALESCE(t1.ccount, 0) ccount,
    COALESCE(t2.dcount, 0) dcount
FROM test t
LEFT JOIN (SELECT
    COUNT(third) AS ccount, FIRST, SECOND
    FROM test
    WHERE third = 'C'
    GROUP BY SECOND,FIRST) t1
ON (t1.first = t.first  AND t1.SECOND = t.SECOND)
LEFT JOIN (SELECT
    COUNT(third) AS dcount, FIRST, SECOND
    FROM test
    WHERE third = 'D'
    GROUP BY SECOND,FIRST) t2
ON (t2.first = t.first AND t2.SECOND = t.SECOND)
GROUP BY t.SECOND, t.first;

SqlResultSetMapping

 @SqlResultSetMapping(name = "RESULT_SET_NAME", classes = {
        @ConstructorResult( targetClass = TestResult.class,
            columns = { @ColumnResult(name = "first", type = String.class),
                        @ColumnResult(name = "second", type = String.class),
                        @ColumnResult(name = "total", type = String.class),
                        @ColumnResult(name = "ccount", type = String.class),
                        @ColumnResult(name = "dcount", type = String.class) }) })

query = getEntityManager().createNativeQuery(nativeQuery, "RESULT_SET_NAME");
query.setHint("org.hibernate.cacheable", true);
result = query.getResultList();

Expected result-set

first   second   total  ccount  dcount  
------  ------  ------  ------  --------
A       abc          3       2         1
B       abc          2       1         1
A       xyz          2       1         1
B       xyz          1       1         0

stack trace

aliases expected length is 1; actual length is 4
java.lang.IllegalStateException: aliases expected length is 1; actual length is 4
    at org.hibernate.transform.CacheableResultTransformer.transformTuple(CacheableResultTransformer.java:155)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:770)
    at org.hibernate.loader.Loader.processResultSet(Loader.java:985)
    at org.hibernate.loader.Loader.doQuery(Loader.java:943)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
    at org.hibernate.loader.Loader.doList(Loader.java:2615)
    at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2460)
    at org.hibernate.loader.Loader.list(Loader.java:2422)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2129)
    at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:981)
    at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:147)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1398)
    at org.hibernate.Query.getResultList(Query.java:417)

Gershom

WHAT query? WHAT stack trace? What configuration? What API calls? – Katti

@BillyFrost, I have added all the details, please check the edited question, please let me know – Gershom

I modified the ConstructorResultNativeQueryTest to use the example you've given, but it works with no error. pastebin.com/Cj5YntaC set your JAVA_HOME to the JDK8 path and run with ./gradlew hibernate-core:test --tests *ConstructorResultNativeQueryTest -Pdb=pgsql (assuming you've set-up a database hibernate_orm_test and a user with the same name and password and full access to it). I can't duplicate your error. – Unsure

@Unsure thanks for your valuable reply, I will check, just want add one point here that my entity class and result-set class are different and having different variable/properties. – Gershom

I found out why I couldn't reproduce the error. I had to define this function in the class to enable query caching @Override protected void addConfigOptions(Map s){s.put(org.hibernate.cfg.AvailableSettings.USE_QUERY_CACHE,"true");} – Unsure

Made a new patch that gets passed that error pastebin.com/xw6A7f6w and it reveals that it is not a bug, because you simply cannot cache a ConstructorResult. The conversion between ResultSet and the ConstructorResult-specified class is too deep before it enters the cache and it would require huge changes to implement a cache that supports this, and it would require your class to implement java.io.Serializable and have a no-args constructor. – Unsure

Answer 1

Hibernate can’t know what you are doing when you execute a native sql query and therefore it can’t know what caches need to be invalidated. As long as hibernate doesn’t know which caches are affected it must assume that all data is invalid to ensure data consistency. This means that hibernate will invalidate all caches.

Fortunatly the hibernate API let you specify the entities or query spaces that are affected by your query. Tell hibernate which tables are affected by your query and hibernate will only invalidate caches that are based on that data.

SQLQuery sqlQuery = session.createSQLQuery("UPDATE CUSTOMER SET ... WHERE ...");
sqlQuery.addSynchronizedEntityClass(Person.class);
int updatedEntities = sqlQuery.executeUpdate();

with entity name

sqlQuery.addSynchronizedEntityClass(Person.class);
sqlQuery.addSynchronizedEntityName("com.link_intersystems.xhibernate.testclasses.Person");
sqlQuery.addSynchronizedQuerySpace("SOME_TABLE");

Sometimes you want to execute a native query that doesn’t change any data. To prevent hibernate from invalidating the second level caches you can add an empty query space synchronization.

SQLQuery sqlQuery = session.createSQLQuery("ALTER SESSION SET NLS_COMP = 'BINARY'");
sqlQuery.addSynchronizedQuerySpace("");  
/*
 * Only the empty query space "" will be invalidated.
 * So no cache will be invalidated, because no table with an empty name exists
 */
int updatedEntities = sqlQuery.executeUpdate();

in hibernate mapping xml

<sql-query name="setNLSCompBinary">
 <!-- an empty synchronize tag prevents hibernate from invalidating second level caches -->
 <synchronize table="" />
  ALTER SESSION SET NLS_COMP = 'BINARY'
</sql-query>

impact-of-native-sql-queries-on-hibernates-second-level-cache

Unreconstructed

Answer 2

In your case, EHCache can only be used with JPQL queries. That will also mean you will have to rewrite your query not to use sub-selects, unions or similar native sql constructions.

Fries

Answer 3

OMG, you should not use cache with native queries, hibernate is not designed for that:

https://www.link-intersystems.com/blog/2011/10/08/impact-of-native-sql-queries-on-hibernates-second-level-cache/

Palliate

Are you sure it still applies from 3.6 to 5.2? – Unsure