search

my2iu / Jinq

LINQ-style queries for Java 8
Other
659 stars 71 forks source link

java.lang.IllegalArgumentException: Could not translate code to a query #86

Closed phdbutbachelor closed 2 years ago

phdbutbachelor commented 4 years ago

I want to query like: SELECT b.* FROM (SELECT id as id, max(version) as version FROM gmcc.st.cms.model.content.forum.Posting GROUP BY id) AS a LEFT JOIN gmcc.st.cms.model.content.forum.Posting AS b ON a.id = b.id AND a.version = b.version

so tried the following: List _items = stream(Posting.class) .group(i -> i.getVersion(), (key, stream) -> stream.max(i -> i.getVersion())) .leftOuterJoin((i, session) -> session.stream(Posting.class), (i, posting) -> i.getOne().equals(posting.getId()) && i.getTwo().equals(posting.getVersion())) .select(i -> i.getTwo()) .toList();

getting the following error with sortedDescendingBy: java.lang.IllegalArgumentException: Could not translate code to a query at org.jinq.hibernate.HibernateQueryComposer.translationFail(HibernateQueryComposer.java:123) ~[jinq-hibernate-legacy-1.8.29.jar:?] at org.jinq.hibernate.HibernateQueryComposer.applyTransformWithTwoLambdas(HibernateQueryComposer.java:341) ~[jinq-hibernate-legacy-1.8.29.jar:?] at org.jinq.hibernate.HibernateQueryComposer.leftOuterJoinWithSource(HibernateQueryComposer.java:589) ~[jinq-hibernate-legacy-1.8.29.jar:?] at org.jinq.orm.stream.QueryJinqStream.leftOuterJoin(QueryJinqStream.java:142) ~[api-1.8.29.jar:?] at org.jinq.hibernate.QueryJPAJinqStream.leftOuterJoin(QueryJPAJinqStream.java:199) ~[jinq-hibernate-legacy-1.8.29.jar:?] at org.jinq.hibernate.QueryJPAJinqStream.leftOuterJoin(QueryJPAJinqStream.java:16) ~[jinq-hibernate-legacy-1.8.29.jar:?] at gmcc.st.cms.repository.PostingRepository.query(PostingRepository.java:182) ~[classes/:?] at gmcc.st.cms.repository.PostingRepository$$FastClassBySpringCGLIB$$f5549cb2.invoke() ~[classes/:?] at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) ~[spring-tx-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at gmcc.st.cms.repository.PostingRepository$$EnhancerBySpringCGLIB$$bdca9b99.query() ~[classes/:?] at gmcc.st.cms.repository.PostingRepository$$FastClassBySpringCGLIB$$f5549cb2.invoke() ~[classes/:?] at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) ~[spring-tx-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at gmcc.st.cms.repository.PostingRepository$$EnhancerBySpringCGLIB$$59baa243.query() ~[classes/:?] at gmcc.st.cms.controller.PostingController.query(PostingController.java:56) ~[classes/:?] at gmcc.st.cms.controller.PostingController$$FastClassBySpringCGLIB$$5d84fcd6.invoke() ~[classes/:?] at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85) ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at gmcc.st.sparrow.log.AutoLogAspect.log(AutoLogAspect.java:93) [classes/:?] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_191] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_191] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_191] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_191] at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:627) [spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:616) [spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70) [spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168) [spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) [spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673) [spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] at gmcc.st.cms.controller.PostingController$$EnhancerBySpringCGLIB$$2fd264bc.query() [classes/:?] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_191] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_191] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_191] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_191] at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) [spring-web-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) [spring-web-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) [servlet-api.jar:?] at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) [servlet-api.jar:?] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [catalina.jar:8.5.24] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.24] at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61) [shiro-web-1.4.0.jar:1.4.0] at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108) [shiro-web-1.4.0.jar:1.4.0] at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137) [shiro-web-1.4.0.jar:1.4.0] at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) [shiro-web-1.4.0.jar:1.4.0] at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66) [shiro-web-1.4.0.jar:1.4.0] at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) [shiro-web-1.4.0.jar:1.4.0] at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) [shiro-web-1.4.0.jar:1.4.0] at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) [shiro-core-1.4.0.jar:1.4.0] at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) [shiro-core-1.4.0.jar:1.4.0] at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:387) [shiro-core-1.4.0.jar:1.4.0] at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) [shiro-web-1.4.0.jar:1.4.0] at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) [shiro-web-1.4.0.jar:1.4.0] at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:347) [spring-web-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:263) [spring-web-4.3.16.RELEASE.jar:4.3.16.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.24] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.24] at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat-websocket.jar:8.5.24] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.24] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.24] at org.apache.logging.log4j.web.Log4jServletFilter.doFilter(Log4jServletFilter.java:71) [log4j-web-2.11.1.jar:2.11.1] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.24] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.24] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) [catalina.jar:8.5.24] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [catalina.jar:8.5.24] at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504) [catalina.jar:8.5.24] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [catalina.jar:8.5.24] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) [catalina.jar:8.5.24] at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650) [catalina.jar:8.5.24] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [catalina.jar:8.5.24] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [catalina.jar:8.5.24] at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) [tomcat-coyote.jar:8.5.24] at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-coyote.jar:8.5.24] at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790) [tomcat-coyote.jar:8.5.24] at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459) [tomcat-coyote.jar:8.5.24] at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-coyote.jar:8.5.24] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_191] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_191] at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:8.5.24] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_191] Caused by: org.jinq.jpa.transform.QueryTransformException: Existing query cannot be transformed further at org.jinq.jpa.transform.OuterJoinOnTransform.apply(OuterJoinOnTransform.java:98) ~[jinq-jpa-1.8.29.jar:?] at org.jinq.hibernate.HibernateQueryComposer.applyTransformWithTwoLambdas(HibernateQueryComposer.java:337) ~[jinq-hibernate-legacy-1.8.29.jar:?] ... 102 more

my2iu commented 4 years ago

It looks like the error might be from somewhere else in your code? Because it’s complaining about sortedDescendingBy(), but that doesn’t appear in the code snippet?

Could you tell me what’s in the Posting class? I can’t really tell what you’re trying to do without knowing what Posting is. Just glancing over things, it seems like you’re trying to return Null if there’s no versions for a posting, but it seems like that’s impossible, so there’s no reason to do that?

my2iu commented 4 years ago

Oh, wait, sorry. I see what you’re trying to do. I’m not sure. Once you get into subqueries, SQL always acts a bit wonky. Especially subqueries involving GROUP BYs. It’s hard to know whether Hibernate will accept them or not. What is the error you get once you fix up the sortedByDesecnding() issue?

It’s theoretically possible to do something with subqueries like this:

Select ( Select A.field1 from Posting A where A.id = B and A.version = (select max( c.version ) from posting c where c.id=B) ) ... From (select distinct ID from posting)

But with that many subqueries, it’s unclear if it will work, it’s unclear if that’s faster than just having two separate queries, and it’s just a mess. Let me think some more about it.

my2iu commented 4 years ago

Maybe something like?

Select * from Posting A Where A.version = (select max(b.version) from posting b where a.id=b.id)

Does that even work? This subquery stuff is always so confusing!

phdbutbachelor commented 4 years ago

This works very slowly, so I tried with group which dosen't work, I guess I have to use hql instead of jinq in this case.

my2iu commented 4 years ago

Jinq generates HQL, so if it’s slow, then it won’t really get any faster with HQL.

If you replace the final toList() with .getDebugQueryString(), what is the HQL query being generated by Jinq? If it’s identical to the HQL that you were planning on writing, then you may need to dig into optimizing your database indices etc.

matusdekanek commented 2 years ago

Hi, tl;dr by changing the source I managed to produce the query: SELECT B FROM org.jinq.hibernate.test.entities.Posting A LEFT OUTER JOIN org.jinq.hibernate.test.entities.Posting B ON A.version = B.id AND MAX(A.version) = B.version GROUP BY A.version The long version: I have tried the very scenario with a simplest Posting class possible:


@Entity
@Table(name="POSTING")
@NamedQuery(name="Posting.findAll", query="SELECT p FROM Posting p")
public class Posting {
    private static final long serialVersionUID = 1L;

    private int id;

    private Integer version;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Integer getVersion() {
        return version;
    }

    public void setVersion(Integer version) {
        this.version = version;
    }    
}

and test method like this in JinqJPATest 

   @Test
   public void issue86Test(){
       JPAJinqStream qry = streams.streamAll(em, Posting.class)
            .group(i -> i.getVersion(), (key, stream) -> stream.max(i -> i.getVersion()))

            .leftOuterJoin((i, session) -> session.stream(Posting.class), 
                    (i, posting) -> i.getOne().equals(posting.getId()) && i.getTwo().equals(posting.getVersion()))
            .select(i -> i.getTwo());

       String strQuery = qry.getDebugQueryString();
       qry.toList();
       assertTrue(query.length() > 0);   
   }

and that produced the mentioned error. That was thrown by jing in OuterJoinOnTransform.apply(), because the query.isSelectFromWhere() returns false. This doesn't produce any query, because the exception is thrown before that.

However, if I change GroupedSelectFromWhere.isSelectFromWhere() to return true, then I manage to get query

SELECT B FROM org.jinq.hibernate.test.entities.Posting A LEFT OUTER JOIN org.jinq.hibernate.test.entities.Posting B ON A.version = B.id AND MAX(A.version) = B.version GROUP BY A.version and that throws org.hibernate.exception.SQLGrammarException: could not prepare statement caused by Column reference 'POSTING1_.ID' is invalid, or is part of an invalid expression. For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions. which makes sense, because B is not in the GROUP BY list.

I am playing a bit with the code to solve this, but this problem is not simple. If we assume that this kind of expressions(left join after group by) is allowed, then either B has to be added to the GROUP BY clause automatically from the select expressions in GroupedSelectFromWhere.generateGroupBy, but I think that may produce some incorrect queries. Or the query.leftOuterJoin could produce a new query where the original one is nested in a left join, something like

SELECT B
FROM (SELECT A.version, MAX(A.version) col2 FROM org.jinq.hibernate.test.entities.Posting GROUP BY A.version) C
LEFT OUTER JOIN org.jinq.hibernate.test.entities.Posting B ON A.version = B.id AND C.col2 = B.version

which would require some rework in leftOuterJoin method.

I hope it helps.

btw, I think the original code has a mistake and should have been 

List _items = stream(Posting.class)
.group(i -> i.getId(), (key, stream) -> stream.max(i -> i.getVersion()))
.leftOuterJoin((i, session) -> session.stream(Posting.class), (i, posting) -> i.getOne().equals(posting.getId()) && i.getTwo().equals(posting.getVersion()))
.select(i -> i.getTwo())
.toList();

(the grouping expression should be i.getId())

my2iu commented 2 years ago

Thank you @matusdekanek for looking into this. I'm going to close this issue since the original poster hasn't updated the issue in a while. If anyone wants to reopen the issue though, that's fine.

Jinq explicitly does not support joins after a group by. I don't think it's possible to represent such an operation in JPQL/HQL/SQL correctly, so Jinq will refuse to translate such code into a query. The desired query itself is a bit messy. Performing a left outer join and only taking the right part of the join doesn't make too much sense. I imagine the original poster has found some other query to get the results that they were looking for.