Validate Collection/Scalar parameter declarations in PartTree query [DATAJPA-1182]

[spring-projects-issues]

Caleb Cushing opened DATAJPA-1182 and commented

I'm not sure if anything can be done about this, and arguably it's a bug in my code, but wondering if it could be detected based on the signature, and fail on app start, or with a better or earlier message.

This is what I wrote

// Classification implements Spring Data's Persistable
    Stream<Classification> findAllById( Collection<Long> ids );

and the database side of the exception

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
	at org.hibernate.loader.Loader.scroll(Loader.java:2693)
	at org.hibernate.loader.hql.QueryLoader.scroll(QueryLoader.java:570)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:423)
	at org.hibernate.engine.query.spi.HQLQueryPlan.performScroll(HQLQueryPlan.java:350)
	at org.hibernate.internal.SessionImpl.scroll(SessionImpl.java:1420)
	at org.hibernate.internal.QueryImpl.scroll(QueryImpl.java:75)
	at org.springframework.data.jpa.provider.PersistenceProvider$HibernateScrollableResultsIterator.<init>(PersistenceProvider.java:428)
	at org.springframework.data.jpa.provider.PersistenceProvider$1.executeQueryWithResultStream(PersistenceProvider.java:121)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$StreamExecution.doExecute(JpaQueryExecution.java:343)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:85)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:483)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
	... 176 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-01797: this operator must be followed by ANY or ALL

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:353)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)

this is the SQL generated

select classifica0_.id as id1_11_, classifica0_.date_created as date_created2_11_, classifica0_.date_modified as date_modified3_11_, classifica0_.lockincrement as lockincrement4_11_, classifica0_.name as name5_11_, classifica0_.sort_weight as sort_weight6_11_ from classification classifica0_ where classifica0_.id=(? , ? , ? , ? , ? , ? , ? , ? , ?)

what I should have written is this

Stream<Classification> findAllByIdIn( Collection<Long> ids );

just thinking it may be possible to detect that SD is generating something that needs an IN or an ALL, or whatever else and throw some exception. If that's unreasonable feel free to close

---

Issue Links:

• DATACMNS-1489 Add PartTree and declared parameters validation

Referenced from: pull request #228

[spring-projects-issues]

Jens Schauder commented

Since we pass the method arguments through to JPA, there exists a chance that some JPA implementation allows some other weird stuff, which we block by the validation.

We could either not do a hard validation but log on WARN instead. Or wait for complaints to come in and fine tune the conditions. My proposal is to do the later

[spring-projects-issues]

Mark Paluch commented

From a usefulness perspective, it would make sense to provide similar functionality for the other store modules. Porting the code across multiple modules distributes complexity of validation rules across multiple codebases so let's see with DATACMNS-1489 what we can do to maintain a single validator that can be used from the individual modules