13 Replies Latest reply on May 4, 2012 10:32 AM by rhauch

    JOIN query created by QueryObjectModelFactory delivers a wrong result

    dimonv

      Hi all,

       

      if I create a query with JOIN over javax.jcr.query.QueryManager:

       

      q = queryManager.createQuery("SELECT a.* FROM [test:test] AS a INNER JOIN [test:owner] AS b ON a.owner = b.[jcr:uuid] WHERE b.myAttribute = 'Dimi'", Query.JCR_SQL2);

      (

      JCR-SQL2 -> SELECT a.* FROM [test:test] AS a INNER JOIN [test:owner] AS b ON a.owner = b.[jcr:uuid] WHERE b.myAttribute = 'Dimi'

      AQM -> SELECT a.* FROM test:test AS a INNER JOIN test:owner AS b ON a.owner = b.jcr:uuid WHERE b.myAttribute = 'Dimi'

      )

       

      it delievers right result: one node.

       

      I wrote a small method creating the same query by means QueryObjectModelFactory:

      JCR-JQOM -> SELECT * FROM test:test AS a INNER JOIN test:owner AS b ON a.owner = b.[jcr:uuid] WHERE b.myAttribute = 'Dimi'

      AQM -> SELECT * FROM test:test AS a INNER JOIN test:owner AS b ON a.owner = b.[jcr:uuid] WHERE b.myAttribute = 'Dimi'

       

      but its result  is wrong: 3 time the same node.

       

      (Workaround) After some modifications I obtain the statement from the QueryObjectModel and with this string I create a new query  over QueryManager and this new one delivers right result.

       

      Has anybody faced a similar issue?

       

      Thanks and kind regards

        • 1. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
          rhauch

          Might this be due to the bug you found and reported as MODE-1473? It's hard to tell whether that's the cause, without knowing more about the results and the other nodes. If not, please feel free to log an issue, posting as much information in the JIRA issue as you can.

          • 2. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
            dimonv

            Hi Randall,

             

            sorry for delay.

            It is definitely not the bug from MODE-1473 because I don't use this method but instantiate JcrEquiJoinCondition directly.

             

            Actually it is difficult to describe the issue without the whole source code, but I'm going to provide it ASAP.

            Nevertheless let me try.

             

            I'm trying to write a method creating QueryObjectModel by means of javax.jcr.query.qom.QueryObjectModelFactory. As the master I use a JCR query made "manually". On the top of this topic you can see the output of statements.

            I started with modeshape version 2.8.0.Final.

            What I noticed that QOMF doesn't "like" square-brackets but it accepted my join condition:

            JoinCondition jc = new JcrEquiJoinCondition(new SelectorName(selector1.getSelectorName()), parameter, new SelectorName(selector2.getSelectorName()), "[jcr:uuid]");
            

            see here.

            JCR-JQOM -> SELECT * FROM test:test AS a INNER JOIN test:owner AS b ON a.owner = b.[jcr:uuid] WHERE b.myAttribute = 'Dimi'

            AQM -> SELECT * FROM test:test AS a INNER JOIN test:owner AS b ON a.owner = b.[jcr:uuid] WHERE b.myAttribute = 'Dimi'

            Resulting QOM could build and execute query, but the result was 3 time the same node instead of one node.

            Then I switched to 2.8.1.Final. The same QOM as above provided then 0 nodes instead of 1 node. After I found out the cause ([] in join condition) I thought that it works.

            But then I amended my query by one JOIN more like this:

            JCR-JQOM -> SELECT * FROM test:test AS a INNER JOIN test:second AS second ON a.second = second.jcr:uuid INNER JOIN test:owner AS owner ON second.owner = owner.jcr:uuid WHERE (owner.myAttribute = $owner_myAttribute AND a.myAttribute = $myAttribute)

            AQM -> SELECT * FROM test:test AS a INNER JOIN test:second AS second ON a.second = second.jcr:uuid INNER JOIN test:owner AS owner ON second.owner = owner.jcr:uuid WHERE (owner.myAttribute = $owner_myAttribute AND a.myAttribute = $myAttribute)

            and its result was a node of the second selector (of type test:second).

             

            I tried to use Column API on QOM creating. The following attempt:

                           
            Column col = qomf.column("a",null, null);
            Column[] columns = new Column[]{col};
            ...
            qom = qomf.createQuery(join, constraint, orderings, columns);
            

            results in NPE  as well as using wildcards instead of null for properties and column names.

             

            The current workaround I use is:

            • creating QOM with jcr type in square brackets
            • obtain statement
            • use this statement to create query and execute this.

            and it works.

             

            Regards,

            Dmitri

            • 3. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
              rhauch

              Yes, it is pretty confusing. :-) You should not have to put the square brackets in the parameters to the factory methods, and you certainly shouldn't have to build the QOM query, obtain the statement, and execute the statement.

               

              Can you provide a test case (or test cases) that demostrate the problem(s)? That would make it significantly easier to make sure we're correctly and thoroughly fixing the problem your seeing. The test case(s) can simply create an in-memory repository, build the queries and check the results. If it's easier to reuse your node types, then register them in the repository configuration; if not, then maybe try to duplicate with some built-in node types (but don't use residual property definitions).

              • 4. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
                dimonv

                Randall Hauch wrote:

                 

                Yes, it is pretty confusing. :-)

                You'r right.

                 

                Randall Hauch wrote:

                 

                You should not have to put the square brackets in the parameters to the factory methods, and you certainly shouldn't have to build the QOM query, obtain the statement, and execute the statement.

                Yes, I know. As I wrote it's just a workaround, since QOM returns wrong result, but the same query but with square brackets does.

                 

                Randall Hauch wrote:

                 

                Can you provide a test case (or test cases) that demostrate the problem(s)? That would make it significantly easier to make sure we're correctly and thoroughly fixing the problem your seeing. The test case(s) can simply create an in-memory repository, build the queries and check the results. If it's easier to reuse your node types, then register them in the repository configuration; if not, then maybe try to duplicate with some built-in node types (but don't use residual property definitions).

                I'm gonna do that ASAP.

                 

                Regards

                • 5. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
                  rhauch

                  I'm gonna do that ASAP.

                   

                  When you do that, please log a new issue. We'll target it (and MODE-1473) for a 2.8.2.Final patch release, which is currently unscheduled but which we can probably schedule to release within a few weeks maximum.

                   

                  Thanks Dmitri and best regards

                  • 6. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
                    dimonv

                    I have attached a test case demostrating the issue. During prepairing this sample I noticed that Column value is decisive on creating of a QueryObjectModel.

                    Surely a column like this:

                    Column col = qomf.column(selector1.getSelectorName(), null, null);

                    should not cause problems on execution of query.

                     

                    Please have a look at the code. I'm not sure if there is a bug or is my code just wrong.

                     

                    Thanks and regards

                    • 7. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
                      rhauch

                      Thanks, Dmitri. I'll take a look at the example in a little bit.

                       

                       

                      Column col = qomf.column(selector1.getSelectorName(), null, null);

                      should not cause problems on execution of query.

                      That should not be a problem. Our code (see here) is explicitly checking for a null second parameter, and if that's the case then it will create a Column object with just the selector name.

                      • 8. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
                        dimonv

                        One more finding I made is that if I change sequense of joins:

                         

                                  Join join = qomf.join(selector2, selector3, QueryObjectModelFactory.JCR_JOIN_TYPE_INNER, jc2);
                                  join = qomf.join(selector1, join, QueryObjectModelFactory.JCR_JOIN_TYPE_INNER, jc1);
                        

                        it creates the following query:

                        JCR-JQOM -> SELECT * FROM test:test AS a INNER JOIN test:second AS second INNER JOIN test:owner AS owner ON second.owner = owner.jcr:uuid ON a.second = second.jcr:uuid WHERE owner.myAttribute = $owner_myAttribute
                        AQM -> SELECT * FROM test:test AS a INNER JOIN test:second AS second INNER JOIN test:owner AS owner ON second.owner = owner.jcr:uuid ON a.second = second.jcr:uuid WHERE owner.myAttribute = $owner_myAttribute

                        which is wrong. But the qom executes it without claiming.

                        But if I take its statement, insert quare brackets where required, create a normal query and try to execute; I tend generally to verify queries created by QOM this way, it I get an exception:

                        Exception in thread "main" javax.jcr.query.InvalidQueryException: The JCR-SQL2 query "SELECT a.* FROM [test:test] AS a INNER JOIN [test:second] AS second INNER JOIN [test:owner] AS owner ON second.owner = owner.[jcr:uuid] ON a.second = second.[jcr:uuid] WHERE owner.myAttribute = $owner_myAttribute" is not well-formed: Expecting "ON" but found "INNER" at line 1, column 69: t:second] AS second  ===>> INNER JOIN [test:own
                        

                         

                        Back to my issue with the wrong type in QOM result let me write my assumption I got on debbuging: I have an impression that eather org.modeshape.graph.query.plan.CanonicalPlanner or org.modeshape.graph.query.plan.PlanNode whose selector sequence is wrong.

                        • 9. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
                          dimonv

                          Hi Randall,

                           

                          were you able to reproduce the issues or do you need any further details?

                           

                          Thanks

                          • 10. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
                            rhauch

                            I just tried to extract the files from your attachment, and wasn't able to. Can you please create a bug in our JIRA for this issue, and attach the source code as a ZIP file to the issue? Even if it's not a bug, we need to track it in order for us to work on it.

                             

                            Thanks

                            • 11. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
                              dimonv

                              Could you please try once again?

                               

                              Thanks

                              • 12. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
                                dimonv
                                • 13. Re: JOIN query created by QueryObjectModelFactory delivers a wrong result
                                  rhauch

                                  Thanks for logging that; I can get the test case source code now. I'll try to run it myself in just a little bit.