11 Replies Latest reply on Sep 27, 2006 4:18 AM by icecuber

    RC9 - LEFT JOIN FETCH queries that worked with RC8 are now g

    scotttam

      I recently upgraded to RC9 and some of my queries that contain LEFT JOIN FETCHES to lazy load relationships are now failing. For example, the following query:

      SELECT c FROM Campaign c "
      "LEFT JOIN FETCH c.states "
      "LEFT JOIN FETCH c.dmas "
      "LEFT JOIN FETCH c.ads "
      "LEFT JOIN FETCH c.dayParts "
      "LEFT JOIN FETCH c.adSlots "
      "WHERE c.id = :id
      


      is now throwing the following exception:

      javax.persistence.NonUniqueResultException: result returns 4 elements
       at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:85)
       at com.opus3media.ejb.CampaignDAOBean.findLazyLoadAll(CampaignDAOBean.java:174)
       at com.opus3media.ejb.CampaignDAOBean.findLazyLoadAll(CampaignDAOBean.java:142)
       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:585)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
       at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
       at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
       at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
       at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
       at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79)
       at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:197)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
       at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
       at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
       at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:78)
       at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:131)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
       at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
       at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
       at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:211)
       at org.jboss.ejb3.stateless.StatelessLocalProxy.invoke(StatelessLocalProxy.java:79)
       at $Proxy126.findLazyLoadAll(Unknown Source)
       at com.opus3media.web.ListCampaignsBean.createCampaignPurchaseType(ListCampaignsBean.java:272)
       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:585)
       at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:129)
       at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:63)
       at oracle.adf.view.faces.component.UIXCommand.broadcast(UIXCommand.java:211)
       at oracle.adf.view.faces.component.UIXCollection.broadcast(UIXCollection.java:94)
       at oracle.adf.view.faces.component.UIXTable.broadcast(UIXTable.java:205)
       at javax.faces.component.UIViewRoot._broadcastForPhase(UIViewRoot.java:90)
       at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:164)
       at org.apache.myfaces.lifecycle.LifecycleImpl.invokeApplication(LifecycleImpl.java:316)
       at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:86)
       at javax.faces.webapp.FacesServlet.service(FacesServlet.java:106)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
       at oracle.adfinternal.view.faces.webapp.AdfFacesFilterImpl._invokeDoFilter(AdfFacesFilterImpl.java:367)
       at oracle.adfinternal.view.faces.webapp.AdfFacesFilterImpl._doFilterImpl(AdfFacesFilterImpl.java:336)
       at oracle.adfinternal.view.faces.webapp.AdfFacesFilterImpl.doFilter(AdfFacesFilterImpl.java:196)
       at oracle.adf.view.faces.webapp.AdfFacesFilter.doFilter(AdfFacesFilter.java:87)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
       at com.opus3media.web.filter.SessionAliveFilter.doFilter(SessionAliveFilter.java:57)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
       at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
       at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:175)
       at org.jboss.web.tomcat.tc5.session.ClusteredSessionValve.invoke(ClusteredSessionValve.java:95)
       at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:524)
       at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:74)
       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
       at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
       at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
       at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
       at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
       at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
       at java.lang.Thread.run(Thread.java:595)
      


      If I grab the raw SQL, it definitely is coming back with 4 result where previously in RC8 there was only 1 result.

      Is this expected behavior or a bug?

      Thanks,

      Scott


        • 1. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
          juergen.zimmermann

          Did you try "SELECT DISTINCT" ?

          • 2. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
            icecuber

             

            "scotttam" wrote:
            I recently upgraded to RC9 and some of my queries that contain LEFT JOIN FETCHES to lazy load relationships are now failing. For example, the following query:

            SELECT c FROM Campaign c "
            "LEFT JOIN FETCH c.states "
            "LEFT JOIN FETCH c.dmas "
            "LEFT JOIN FETCH c.ads "
            "LEFT JOIN FETCH c.dayParts "
            "LEFT JOIN FETCH c.adSlots "
            "WHERE c.id = :id
            


            is now throwing the following exception:

            javax.persistence.NonUniqueResultException: result returns 4 elements
            


            If I grab the raw SQL, it definitely is coming back with 4 result where previously in RC8 there was only 1 result.

            Is this expected behavior or a bug?

            Thanks,

            Scott


            I'm using
            Release ID: JBoss [Zion] 4.0.4.GA (build: CVSTag=JBoss_4_0_4_GA date=200605151000)
            with
            Java version: 1.5.0_08,Sun Microsystems Inc.

            i'm having a similar problem with RC9 so i switched back to RC8: in my query i have 2 parameters WITH DIFFERENT NAMES BUT if the VALUE is the same i get NonUniqueResultException
            while ON THE DB i have a single record identified by these parameters

            i'm quite confident this is a serious bug!!!!

            • 3. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
              zerbit

              I had the same problem too,
              my query expects 4 parameters, two of them are USERNAME and PASSWORD.
              In RC7/RC8 this query works fine but in RC9 somethings get wrong when username and password have the same value (id usr=admin,pas=admin)!

              Is Query.setParameter(key,val) bugged? It seams that putting parameters with same value (BUT different key) one parameter is lost, any suggestion?

              best regards
              Zerbit

              • 4. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
                epbernard

                Scott,
                I fixed a bug in RC9 that was hiding a bug in your code.
                you must use distinct.

                • 5. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
                  epbernard

                  icecuber, in RC8, the *sql* query returned 4 result too right? If not then your data has changed, not the code.

                  • 6. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
                    epbernard

                    zerbit, I find it hard to believe, but if you can create a runnable test case reproducing the "different params same value" issue, then add it to JIRA.

                    • 7. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
                      icecuber

                       

                      "epbernard" wrote:
                      icecuber, in RC8, the *sql* query returned 4 result too right? If not then your data has changed, not the code.


                      No Sorry.. i tried RC7, RC8 and RC9 with the same data and the same query...

                      is it possible that due to joins in RC9 i select 4 time the same record while in RC7 and RC8 i select only one record???

                      in fact i did not try to add a distinct keyword... i'm going to install RC9 again and try

                      • 8. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
                        epbernard

                         

                        "icecuber" wrote:

                        No Sorry.. i tried RC7, RC8 and RC9 with the same data and the same query...


                        I meant the SQL query, not the HQL or Criteria query. Like the other you could have some issues related to the non use of distinct

                        • 9. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
                          epbernard

                          To be specific and to outsiders
                          if you use getSingleResult() and a query involving collection fetching, you could end up with this kind of issue if you don't use distinct

                          • 10. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
                            icecuber

                             

                            "epbernard" wrote:
                            zerbit, I find it hard to believe, but if you can create a runnable test case reproducing the "different params same value" issue, then add it to JIRA.


                            In my case Murphy's law hit again.... first of all

                            with distinct keyword it works as expected...

                            i call getSingleResult() and i got a correct single object with association... the "different params same value" issue was a coincidence: these records has multiple associations while other has a single associated value :(

                            • 11. Re: RC9 - LEFT JOIN FETCH queries that worked with RC8 are n
                              icecuber

                               

                              "epbernard" wrote:
                              zerbit, I find it hard to believe, but if you can create a runnable test case reproducing the "different params same value" issue, then add it to JIRA.


                              In my case Murphy's law hit again.... first of all

                              with distinct keyword it works as expected...

                              i call getSingleResult() and i got a correct single object with association... the "different params same value" issue was a coincidence: these records has multiple associations while other has a single associated value :(