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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Out of Date
    • Affects Version/s: 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
    • Fix Version/s: TBD EAP 4
    • Component/s: Hibernate
    • Labels:
      None
    • Environment:

      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

    • Affects:
      Release Notes
    • Release Notes Text:
      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.
    • Release Notes Docs Status:
      Documented as Known Issue
    • Docs QE Status:
      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.

        Gliffy Diagrams

          Attachments

            Issue Links

            cloned to

            Bug - A problem which 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 - A request that should be considered seriously but is not a show stopper.
            • Resolved

            Bug - A problem which 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 - A request that should be considered seriously but is not a show stopper.
            • Closed
            relates to

            Bug - A problem which 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 - A request that should be considered seriously but is not a show stopper.
            • Closed

              Activity

                People

                • Assignee:
                  jaredmorgs Jared Morgan
                  Reporter:
                  juraci.costa Juraci Paixão Kröhling
                  Writer:
                  Jared Morgan
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: