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

Details

    • Bug
    • Resolution: Obsolete
    • 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

    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

          clones

          Bug - A problem that impairs or prevents the functions of the product. JBPAPP-5582 Document - ANN-625 - @OrderBy usage on a joined classes (when using join table) produces incorred SQL syntax

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Resolved

          Bug - A problem that impairs or prevents the functions of the product. JBPAPP-7103 CLONE - ANN-625 - @OrderBy usage on a joined classes (when using join table) produces incorred SQL syntax

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Closed
          relates to

          Bug - A problem that impairs or prevents the functions of the product. JBPAPP-5930 HHH-4394 @OrderBy is used in conjunction with a table per class hierarchy produces incorred SQL syntax.

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Closed

          Activity

            People

              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

              Dates

                Created:
                Updated:
                Resolved: