Uploaded image for project: 'JBoss Enterprise Application Platform 4 and 5'
  1. JBoss Enterprise Application Platform 4 and 5
  2. JBPAPP-5582

Document - ANN-625 - @OrderBy usage on a joined classes (when using join table) produces incorred SQL syntax

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • TBD
    • 4.3.0.GA_CP02_FP01, 4.3.0.GA_CP03_FP01, 4.3.0.GA_CP04_FP01, EAP 5.0.0.BETA, EAP 5.0.0.CR2, EAP 5.0.0.CR3, EAP 5.0.0.CR4 (FCS and BETA2), EAP 5.0.0.CR5, EAP 5.0.0, 4.2.0.GA_CP08, 4.3.0.GA_CP07, 4.3.0.GA_CP09
    • Documentation, Hibernate
    • None
    • Hibernate Annotations 3.3.0 GA (taken from EAP 4.3 CP 02 FP01) and MySQL 5 / PostgreSQL 8.2.3 / Oracle 9g / Oracle 10g / MSSQL 2005

    • Documentation (Ref Guide, User Guide, etc.)
    • Not Required

    Description

      Test testOrderByOnSuperclassProperty on org.hibernate.test.annotations.onetomany.OneToManyTest generates this SQL query, which databases complains:

      select organisati0_.id_organisation as id1_16_0_, organisati1_.id_organisation_user as id1_15_1_, organisati0_.name as name16_0_, organisati1_1_.first_name as first2_15_1_, organisati1_1_.last_name as last3_15_1_, organisati1_.fk_id_organisation as fk3_17_1_, organisati1_.some_text as some1_17_1_, organisati1_.fk_id_organisation as fk3_0_, organisati1.id_organisation_user as id2_0__ from ORGANISATION organisati0_ left outer join ORGANISATION_USER organisati1_ on organisati0_.id_organisation=organisati1_.fk_id_organisation left outer join PERSON_Orderby organisati1_1_ on organisati1_.id_organisation_user=organisati1_1_.id_person order by PERSON_Orderby.first_name asc

      The below query is recognized as correct on MySQL (same change also makes it work on PostgreSQL/Oracle/MSSQL):
      select organisati0_.id_organisation as id1_16_0_, organisati1_.id_organisation_user as id1_15_1_, organisati0_.name as name16_0_, organisati1_1_.first_name as first2_15_1_, organisati1_1_.last_name as last3_15_1_, organisati1_.fk_id_organisation as fk3_17_1_, organisati1_.some_text as some1_17_1_, organisati1_.fk_id_organisation as fk3_0_, organisati1.id_organisation_user as id2_0__ from ORGANISATION organisati0_ left outer join ORGANISATION_USER organisati1_ on organisati0_.id_organisation=organisati1_.fk_id_organisation left outer join PERSON_Orderby organisati1_1_ on organisati1_.id_organisation_user=organisati1_1_.id_person order by organisati1_1_.first_name asc

      The difference is in the "order by" clause. The first one, use the joined table as qualification for the field name, where the second uses the given alias as it's qualification.

      Attachments

        Issue Links

          Activity

            People

              mistysj Misty Stanley-Jones (Inactive)
              jpkroehling@redhat.com Juraci Paixão Kröhling (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: