wrong count query for Entities with EmbeddedID in Spring Data and Hibernate 4.0.1

[sejmajnejm]

Hello,

It seems that Spring Data (spring-data-jpa-1.0.2.RELEASE) and Hibernate 4.0.1 do not work right when Spring Data generates count query for custom method and Entity with EmbeddedId.
Programdescription class is an Entity with EmbeddedId.

Here is the example:

CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<Long> cq = cb.createQuery(Long.class);
		Root<Programdescription> r = cq.from(Programdescription.class);
		
		cq.where(r.get("programdescription").isNotNull());
		
		cq.select(cb.count(r));

generates this query:

select count(*) as col_0_0_ from programDescription programdes0_ where programdes0_.programDescription is not null limit ?

which is right according to https://hibernate.onjira.com/browse/HHH-5419

But when Spring data is used with following custom method:

programdescriptionRepository.findProgramNull(new PageRequest(1, 10) );

Spring Data with Hibernate generate this SQL which causes exception (please, check count statement in select clause - all fields from EmbeddedId):

select count((programdes0_.descriptionCulture, programdes0_.programDescriptionType, programdes0_.programId, programdes0_.sourceId)) as col_0_0_ from programDescription programdes0_ where programdes0_.programDescription is not null

However this call

programdescriptionRepository.count();

generates query with count(*) (which is desirable)

Here is the Query definition for findProgramNull method:

@NamedQuery(name="Programdescription.findProgramNull", query="SELECT pd.programdescriptionid.programid FROM Programdescription pd WHERE pd.programdescription is not null ")

I do not know exactly where the problem is, but it looks like Hibernate generates right query when used alone (see QueryBuilder example at the top of the post)

Here is the stack trace after calling the method from the Spring Data repository

xception in thread "main" org.springframework.dao.DataIntegrityViolationException: Operand should contain 1 column(s); SQL [n/a]; nested exception is org.hibernate.exception.DataException: Operand should contain 1 column(s)
	at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:642)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:102)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:368)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy39.findProgramNull(Unknown Source)
	at com.cyandia.dms.dataprovider.rovi.service.impl.ProgramServiceImpl.getDetails(ProgramServiceImpl.java:131)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy71.getDetails(Unknown Source)
	at XXXXXXX.DAOTest.main(DAOTest.java:88)
Caused by: org.hibernate.exception.DataException: Operand should contain 1 column(s)
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:134)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
	at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
	at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
	at $Proxy78.executeQuery(Unknown Source)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1978)
	at org.hibernate.loader.Loader.doQuery(Loader.java:829)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
	at org.hibernate.loader.Loader.doList(Loader.java:2463)
	at org.hibernate.loader.Loader.doList(Loader.java:2449)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
	at org.hibernate.loader.Loader.list(Loader.java:2274)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115)
	at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:252)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:99)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:54)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:93)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:83)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
	... 16 more
Caused by: java.sql.SQLException: Operand should contain 1 column(s)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
	... 40 more

UPDATE:

after performing some checks I found that hibernate still translates queries like (which is sent from Spring Data)

SELECT count(pd) FROM Programdescription pd WHERE pd.programdescription is not null

into SQL with all fields from EmbeddedID in the count statement

Cheers
A.