Situation:
I have got the following jpa query which works flawlessly in the IntelliJ jpa-ql console. I've added it as picture so one can see the code highlighting and therefore the actual error:
select
run1.someAttribute1 AS someAttribute1,
run1.someAttribute2 AS someAttribute2,
run1.someAttribute3 AS someAttribute3,
(select
sum(function('timestampdiff', SECOND, run2.runStartTime, run2.runEndTime))
from runTable AS run2
where run2.anotherAttribute1 = run1.anotherAttribute1
and run2.anotherAttribute2 = run1.someAttribute2
and run2.anotherAttribute3 = run1.someAttribute3
) AS runtime,
sum(
case when type(event) = SomeEvent and event.someType = '...' then 1
else 0 end
) AS numberOfSomething1,
sum(
case when type(event) = SomeEvent and event.someType = '...' then 1
else 0 end
) AS numberOfSomething2,
sum(
case when type(event) = SomeEvent and event.someType = '...' then 1
else 0 end
) AS numberOfSomething3,
sum(
case when type(event) = SomeEvent and event.someType = '...' then 1
else 0 end
) AS numberOfSomething4,
sum(
case when type(event) = SomeEvent2 then 1
else 0 end
) AS numberOfSomething5from runTable AS run1
left join run1.events AS event
group by someAttribute1, someAttribute2, someAttribute3
order by
max(function('right', function('left', someAttribute1, 8), 3)) desc,
someAttribute2 desc,
someAttribute3 desc
JPA Query with subselect validation error
If ran in the jpa-ql console it returns the following result:
The third column is the runTime in seconds which is correct if done with the subselect query. If I refactor the subselect to use the run1 alias it returns the wrong result. I thought the grouping does already contain the right runTime but obviously I'm wrong:
select
run1.someAttribute1 AS someAttribute1,
run1.someAttribute2 AS someAttribute2,
run1.someAttribute3 AS someAttribute3,
sum(
function('timestampdiff', SECOND, run1.runStartTime, run1.runEndTime)
) AS runtime,
sum(case when type(event) = SomeEvent and event.someType = '...' then 1 else 0 end) AS numberOfSomething1,
sum(case when type(event) = SomeEvent and event.someType = '...' then 1 else 0 end) AS numberOfSomething2,
sum(case when type(event) = SomeEvent and event.someType = '...' then 1 else 0 end) AS numberOfSomething3,
sum(case when type(event) = SomeEvent and event.someType = '...' then 1 else 0 end) AS numberOfSomething4,
sum(case when type(event) = SomeEvent2 then 1 else 0 end) AS numberOfSomething5from runTable AS run1
left join run1.events AS event
group by someAttribute1, someAttribute2, someAttribute3
order by max(function('right', function('left', someAttribute1, 8), 3)) desc,
someAttribute2 desc,
someAttribute3 desc
Wrong result with alias from the FROM clause
Problem
The main difference is, that the first jpa query throws an error while starting up the spring boot application, while the refactored one does not:
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List someClass.someMethod()
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:92) ~[spring-data-jpa-1.11.8.RELEASE.jar:?]
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:62) ~[spring-data-jpa-1.11.8.RELEASE.jar:?]
at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:72) ~[spring-data-jpa-1.11.8.RELEASE.jar:?]
at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:53) ~[spring-data-jpa-1.11.8.RELEASE.jar:?]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:144) ~[spring-data-jpa-1.11.8.RELEASE.jar:?]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:211) ~[spring-data-jpa-1.11.8.RELEASE.jar:?]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:77) ~[spring-data-jpa-1.11.8.RELEASE.jar:?]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:436) ~[spring-data-commons-1.13.8.RELEASE.jar:?]
at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:221) ~[spring-data-commons-1.13.8.RELEASE.jar:?]
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.initAndReturn(RepositoryFactoryBeanSupport.java:277) ~[spring-data-commons-1.13.8.RELEASE.jar:?]
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:263) ~[spring-data-commons-1.13.8.RELEASE.jar:?]
at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:101) ~[spring-data-jpa-1.11.8.RELEASE.jar:?]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:208) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1138) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1066) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:835) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:741) ~[spring-beans-4.3.10.RELEASE.jar:4.3.10.RELEASE]
... 17 more
It seems like spring data can't handle the opening bracket which follows a comma within the select clause(see 1), even though the actual query does work.
Question
Is there any way to solve this issue or rewrite the subselect query?
Used spring-data version 1.11.8 which does the query validation
I don't know what you executed in the console but it's neither of the queries you posted, which becomes obvious once you format them as I did.
In both cases, there is a space missing in front of the from before the left join.
Also, I think a subselect should start with the keyword select.