1 2 Previous Next 16 Replies Latest reply on Jun 30, 2004 6:54 AM by martingx

    CMR / EJB-QL problem?

    martingx Newbie

      It seems jboss since 3.2.1 up to and including 3.2.4 is generating wrong sql for some EJB-QL queries.

      (FYI: we are using mysql 3.23.58 and mysql-connector 2.0.14 and cross reference relation tables rather than foreign keys.)

      It seems that performing a query over two CMR related beans used to generate left joins in jboss 3.2.1 which gave the correct results.

      Now it appears to use inner joins which only work if objects exist for both beans.

      eg, suppose we have CMP beans Customer and Address with a one to one relationship.

      The following query to find customers with no address:

      SELECT OBJECT(cust) from Customer cust where cust.address is NULL

      fails to give any results because it matches the key on customers to that on the cross reference table, but the record does not exist on the cross reference so we get no result.

      jboss 3.2.1 used left join, so we got the correct result.

      We also tried using the sql92 compiler setting just to see if it worked, and that fell over because it seems to assume we are using foreign key mapping.

        • 1. Re: CMR / EJB-QL problem?
          Alexey Loubyansky Master

          What is the generated SQL? Isn't it using subquery? What SQL is generated qith SQL92 compiler?

          • 2. Re: CMR / EJB-QL problem?
            martingx Newbie

            I think its not using subquery because mysql 3.x doesn't support it.

            Anyway, here is an example of the same problem (slightly more complicated example, but all I have at hand right now)

            For this query, using using jboss with mysql 3.23.x:

            DYNAMIC-QL: SELECT OBJECT(hierarchy) FROM Hierarchy hierarchy WHERE (
            (hierarchy.partyGroup IS NULL) OR (NOT (hierarchy.mainInternalHierarchy IS
            NULL)) ) ORDER BY hierarchy.code


            JBoss 3.2.1 did this (working):

            SELECT DISTINCT t0_hierarchy.id, t0_hierarchy.CODE
            FROM cmp_hierarchy t0_hierarchy LEFT JOIN MainInternalHierarchy_Hierarchys t2_hierarchy_mainInternalHierarc ON t0_hierarchy.id=t2_hierarchy_mainInternalHierarc.HIERARCHY_FK LEFT JOIN PartyGroup_Hierarchys t1_hierarchy_partyGroup_RELATION ON t0_hierarchy.id=t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK

            WHERE ((t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK IS NULL) OR (NOT (t2_hierarchy_mainInternalHierarc.HIERARCHY_FK IS NULL))) ORDER BY t0_hierarchy.CODE ASC
            ;


            JBoss 3.2.4RC2 does this (doesn't fall over, but appears not correct):

            SELECT DISTINCT t0_hierarchy.id, t0_hierarchy.CODE
            FROM cmp_hierarchy t0_hierarchy, cmp_main_internal_hierarchy t3_hierarchy_mainInternalHierarc, maininternalhierarchy_hierarchys t2_hierarchy_mainInternalHierarc, cmp_party_group t4_hierarchy_partyGroup, partygroup_hierarchys t1_hierarchy_partyGroup_RELATION

            WHERE (((t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK IS NULL) OR ( NOT (t2_hierarchy_mainInternalHierarc.HIERARCHY_FK IS
            NULL))) AND t0_hierarchy.id=t2_hierarchy_mainInternalHierarc.HIERARCHY_FK AND t3_hierarchy_mainInternalHierarc.id=t2_hierarchy_mainInternalHierarc.MAIN_INTERNAL_HIERARCHY_FK AND t0_hierarchy.id=t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK AND t4_hierarchy_partyGroup.id=t1_hierarchy_partyGroup_RELATION.PARTY_GROUP_FK) ORDER BY t0_hierarchy.CODE ASC
            ;

            I did also test with 3.2.4 final and it still didn't work. I can't be 100% sure its the same generated sql as for 3.2.4rc2 above because I don't have the output here right now.

            I also don't have access to the output of the sql92 compiler. I will post it when I do.

            • 3. Re: CMR / EJB-QL problem?
              Alexey Loubyansky Master

              Use SQL92 compiler. Current default compiler will be deprecated. The only reason sql92 compiler was not the default one was that HSQLDB (which is the default JBoss datasource) didn't support outer/inner joins and subqueries. Now it does. So sql92 compiler will be the default one.

              • 4. Re: CMR / EJB-QL problem?
                martingx Newbie

                using sql92 compiler generates:

                SELECT t0_hierarchy.id, t0_hierarchy.CODE, t0_hierarchy.TOP_LEVEL_NAME FROM cmp_hierarchy t0_hierarchy LEFT OUTER JOIN partygroup_hierarchys t2_hierarchy_partyGroup_RELATION ON t0_hierarchy.id=t2_hierarchy_partyGroup_RELATION.HIERARCHY_FK LEFT OUTER JOIN cmp_party_group t1_hierarchy_partyGroup ON t1_hierarchy_partyGroup.id=t2_hierarchy_partyGroup_RELATION.PARTY_GROUP_FK WHERE t1_hierarchy_partyGroup.HIERARCHY_FK IS NULL

                which giives:

                java.sql.SQLException: Column not found, message from server: "Unknown column 't1_hierarchy_partyGroup.HIERARCHY_FK' in 'where clause'"
                at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1905)
                at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1109)
                at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1203)
                at com.mysql.jdbc.Connection.execSQL(Connection.java:2090)
                at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1496)
                at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:314)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:198)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.execute(JDBCDynamicQLQuery.java:138)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindEntitiesCommand.java:38)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities(JDBCStoreManager.java:587)
                at org.jboss.ejb.plugins.CMPPersistenceManager.findEntities(CMPPersistenceManager.java:285)


                (etc)


                It seems to think we are using foreign key but we are using relation table mapping.

                • 5. Re: CMR / EJB-QL problem?
                  martingx Newbie

                  sorry, my last post was a slightly different query of:

                  SELECT OBJECT(hierarchy) FROM Hierarchy hierarchy WHERE hierarchy.partyGroup IS NULL

                  but its the same issue.

                  • 6. Re: CMR / EJB-QL problem?
                    Alexey Loubyansky Master

                    Could you replace the SQL92 compiler in 3.2.4 with the current version in Branch_3_2? I think, this bug was fixed.

                    • 7. Re: CMR / EJB-QL problem?
                      martingx Newbie

                      Are there daily builds of Branch_3_2 available?

                      Branch_3_2 from cvs fails to build for me and I won't have time this week to look into why.

                      Thanks for your help so far.

                      Regards,
                      Martin.

                      • 8. Re: CMR / EJB-QL problem?
                        Alexey Loubyansky Master

                        Here is what you need http://cvs.sourceforge.net/viewcvs.py/*checkout*/jboss/jboss/src/main/org/jboss/ejb/plugins/cmp/jdbc/EJBQLToSQL92Compiler.java?rev=1.6.2.6

                        What was your cvs command to check out? The correct one is
                        cvs -z3 -d:pserver:anonymous@cvs.sourceforge.net:/cvsroot/jboss co -r Branch_3_2 jboss-3.2

                        • 9. Re: CMR / EJB-QL problem?
                          martingx Newbie

                          thats the command I used.

                          I followed the FAQ and did:

                          cvs -z3 -d:pserver:anonymous@cvs.sourceforge.net:/cvsroot/jboss co -r JBoss_3_2_3 jboss-3.2
                          cd jboss-3.2/build
                          build

                          (except i replaced the final "build" with "./build.sh"

                          and got:

                          Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/tools/ant/launch/Launcher

                          A quick look at build.sh shows it messing with my ANT_HOME and I don't really know what its trying to do.

                          I could do with help compiling though because I'm also trying to look at a different possible bug in dynamic-ql

                          • 10. Re: CMR / EJB-QL problem?
                            martingx Newbie

                            sorry - ignore my typo. the checkout command i used was exactly the one you gave, not the one from the FAQ.

                            • 11. Re: CMR / EJB-QL problem?
                              martingx Newbie

                              sorry - ignore my typo. the checkout command i used was exactly the one you gave, not the one from the FAQ.

                              • 12. Re: CMR / EJB-QL problem?
                                Alexey Loubyansky Master

                                You should be able to build JBoss w/o any additional setup steps if the right command was used and check out went ok. The Ant version which is distributed with JBoss should be used.

                                • 13. Re: CMR / EJB-QL problem?
                                  martingx Newbie

                                  okay, i deleted everything and tried again in case I did something wrong before.

                                  here is _exactly_ what I did:

                                  [gartonm@dev JBOSS_CVS]$ cvs -z3 -d:pserver:anonymous@cvs.sourceforge.net:/cvsroot/jboss co -r Branch_3_2 jboss-3.2

                                  [*snipped long cvs output - checkout went okay*]

                                  [gartonm@dev JBOSS_CVS]$ cd jboss-3.2/build/
                                  [gartonm@dev build]$ ./build.sh
                                  Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/tools/ant/launch/Launcher
                                  [gartonm@dev build]$

                                  There must be something else I need to do?

                                  Is there a jboss developer newbie list or somewhere I can ask?

                                  • 14. Re: CMR / EJB-QL problem?
                                    martingx Newbie

                                    I got jboss to build by ignoring the FAQ and just using my own installed version of ant.

                                    So I tested again using the sql92 compiler with todays CVS version of Branch_3_2 and got the same result. Here is the log.

                                    The reason it can't find "t1_hierarchy_partyGroup.HIERARCHY_FK" is because we are using <preferred-relation-mapping>relation-table</preferred-relation-mapping> in jbosscmp-jdbc.xml

                                    16:34:29,018 DEBUG [Hierarchy#findDynamic] DYNAMIC-QL: SELECT OBJECT(hierarchy) FROM Hierarchy hierarchy WHERE hierarchy.partyGroup IS NULL
                                    16:34:29,020 DEBUG [Hierarchy#findDynamic] Executing SQL: SELECT t0_hierarchy.id, t0_hierarchy.CODE, t0_hierarchy.TOP_LEVEL_NAME FROM cmp_hierarchy t0_hierarchy LEFT OUTER JOIN partygroup_hierarchys t2_hierarchy_partyGroup_RELATION ON t0_hierarchy.id=t2_hierarchy_partyGroup_RELATION.HIERARCHY_FK LEFT OUTER JOIN cmp_party_group t1_hierarchy_partyGroup ON t1_hierarchy_partyGroup.id=t2_hierarchy_partyGroup_RELATION.PARTY_GROUP_FK WHERE t1_hierarchy_partyGroup.HIERARCHY_FK IS NULL
                                    16:34:29,025 DEBUG [Hierarchy#findDynamic] Find failed
                                    java.sql.SQLException: Column not found, message from server: "Unknown column 't1_hierarchy_partyGroup.HIERARCHY_FK' in 'where clause'"

                                    1 2 Previous Next