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

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

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Obsolete
    • Icon: Major Major
    • TBD EAP 4
    • 4.3.0.GA_CP02_FP01, 4.3.0.GA_CP03_FP01, 4.3.0.GA_CP04_FP01, 4.2.0.GA_CP08, 4.3.0.GA_CP07, 4.3.0.GA_CP09, 4.3.0.GA_CP10
    • 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

    • Release Notes
    • Hide
      When <literal>@OrderBy</literal> is used on joined classes (using a join table), the generated SQL is invalid on MySQL, PostgreSQL, Oracle, DB2, Sybase, and MSSQL because the "order by" clause qualifies the columns using the actual table name. The "order by" clause should use the table alias instead.
      Show
      When <literal>@OrderBy</literal> is used on joined classes (using a join table), the generated SQL is invalid on MySQL, PostgreSQL, Oracle, DB2, Sybase, and MSSQL because the "order by" clause qualifies the columns using the actual table name. The "order by" clause should use the table alias instead.
    • Documented as Known Issue
    • ASSIGNED

      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.

              jaredmorgs_jira Jared Morgan (Inactive)
              jpkroehling@redhat.com Juraci Paixão Kröhling (Inactive)
              Jared Morgan Jared Morgan (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: