Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

@OrderBy produces incorrect SQL when used on a collection field.

Description

I have a test case - a set of maven projects based on a current project of mine, but simplified a bit for the purpose of demonstrating the problem - that shows that when an @OrderBy is placed on a collection field, incorrect SQL is produced for the MySQL dialect. I was previously working fine when using Hibernate 4.3.0.Beta3, and 4.3.0.Beta3 seems to also work, so the regression seems to be in 4.3.0.Beta5 and artifacts up to 4.3.0.CR2 seem to also be broken. The domain object in question is named Device, and the field with the @OrderBy that fails is deviceProperties. As given, the unit tests for the testcase project should fail, and show the error in the build output. A few things to note... you need a mysql instance set up (I'm using 5.7), by default on localhost and port 3306, and a blank database named hibernate_testcase_1. The maven project should create the schema using flyway, then build the project and run the unit test. A few other things to note about the project setup. You customize the project to use custom mysql parameters, like mysql root user's password, for your environment, by setting up a maven <profile> section (active by default, or activated from the command line if desired) for the following default properties:

<testcase.mysql.server>localhost</testcase.mysql.server>
<testcase.mysql.port>3306</testcase.mysql.port>
<testcase.mysql.admin.user>root</testcase.mysql.admin.user>
<testcase.mysql.admin.pass>root</testcase.mysql.admin.pass>
<testcase.mysql.user>testcaseuser</testcase.mysql.user>
<testcase.mysql.pass>testcaseuser</testcase.mysql.pass>
<testcase.mysql.dbname>hibernate_testcase_1</testcase.mysql.dbname>

Also, the project uses our maven nexus repository. If you wish to NOT customize the maven parent POM file to use your own maven repository, then you'll need to set up your settings.xml with the following <server> sections:

<servers>
<server>
<id>public</id>
<username>guest</username>
<password>viewonly</password>
</server>
<server>
<id>public-snapshots</id>
<username>guest</username>
<password>viewonly</password>
</server>
</servers>

This should get you read-only access (for now, until I lock it back down) to our nexus instance. If you use the eclipse launcher(s) included with the project, a maven trustStore included with the project will be used. See that launcher's definition to help you set up the MAVEN_OPTS (quick hint, the mavenTrust.jks file is in the launchers directory of the parent project) to use that trustStore so there are no SSL errors when maven tries to connect to our nexus instance since we're using self-signed certificates if you want to build the maven project from the command line instead of from eclipse.

One other note... you can quickly check that it works in 4.3.0.Beta3 or 4.3.0.Beta4 by changing the version in the parent pom file (look in properties section).

Please let me know if there is any other information I can provide to help this along or if for some reason I've left out steps or information you need in order to build the project.

It would be great if this could be fixed by the time you release 4.3.0.Final.

Attachments

2

Activity

Show:

Kendal MontgomeryApril 22, 2017 at 6:47 PM

Also, I'll note: the new .zip file contains a mercurial repository if you want to follow the changes I made.

Kendal MontgomeryApril 22, 2017 at 6:44 PM

I am the original reporter (different account / email address now). I have updated my testcase project with new versions of dependencies for hibernate, spring framework, and other components, and verified that the query produced is correct:

select
device0_.id as id1_0_0_, device0_.row_version as row_vers2_0_0_, device0_.description as descript3_0_0_, device0_.calibrated as calibrat4_0_0_, device0_.address as address5_0_0_, device0_.device_group_id as device_g6_0_0_, device0_.device_index as device_i7_0_0_, device0_.device_type_id as device_t8_0_0_, deviceprop1_.device_id as device_i1_2_1_, deviceprop2_.id as property2_2_1_, deviceprop2_.id as id1_1_2_, deviceprop2_.row_version as row_vers2_1_2_, deviceprop2_.property_type_id as property4_1_2_, deviceprop2_.prop_value as prop_val3_1_2_, deviceprop2_1_.device_id as device_i1_2_2_, propertyty3_.id as id1_6_3_, propertyty3_.row_version as row_vers2_6_3_, propertyty3_.description as descript3_6_3_, propertyty3_.prop_key as prop_key4_6_3_, propertyty3_.prop_type as prop_typ5_6_3_, devicetype4_.id as id1_3_4_, devicetype4_.row_version as row_vers2_3_4_, devicetype4_.description as descript3_3_4_, devicetype4_.vendor_id as vendor_i4_3_4_
from device device0_
left outer join device_property_xref deviceprop1_ on device0_.id=deviceprop1_.device_id
left outer join device_property deviceprop2_ on deviceprop1_.property_id=deviceprop2_.id
left outer join device_property_xref deviceprop2_1_ on deviceprop2_.id=deviceprop2_1_.property_id
left outer join property_type propertyty3_ on deviceprop2_.property_type_id=propertyty3_.id
left outer join device_type devicetype4_ on device0_.device_type_id=devicetype4_.id
where device0_.id=807
order by deviceprop2_.id asc

To update, my environment now consists of:

Mac OSX, MySQL 5.7, Eclipse Neon with embedded Maven, Hibernate 5.0.5.Final (also tried with 5.1.5.Final and 5.2.10.Final), MySQL Connector 6.0.5.

I will attach the code for the new testcase in a moment with the latest version as of writing (5.2.10.Final – which took a little effort to change out deprecated classes), but I believe this can be marked as resolved now!

Steve EbersoleOctober 28, 2015 at 3:26 AM

As part of verifying that this issue affects 5.0, please just set the "Affects version". Leave the "verify-affects-5.0" label and leave the issue in "Awaiting Response" status; these are critical for us to be able to track these verifications and triage them. Thanks.

Steve EbersoleOctober 27, 2015 at 7:15 PM

This bug report does not indicate that the reported issue affects version 5.x. Versions prior to 5.x are no longer maintained. It would be a great help to the Hibernate team and community for someone to verify that the reported issue still affects version 5.x. If so, please add the 5.x version that you verified with to the list of affected-versions and attach the (preferably SSCCE) test case you used to do the verification to the report; from there the issues will be looked at during our triage meetings.

For details, see http://in.relation.to/2015/10/27/great-jira-cleanup-2015/

BerndBMarch 4, 2014 at 4:39 PM

Another self-contained, minimalistic test case for this can be found here: https://github.com/smaragd/order-test
It has only two entity beans and a session EJB that deploys fine under Hibernate 4.3.0.Beta4 (as used in WildFly 8.0.0.Beta2), but breaks under Hibernate 4.3.1.Final (as used in the GA release of WildFly). I have tried all tagged versions of Hibernate up to 4.3.4.Final but they all show the same problem.

Duplicate

Details

Assignee

Reporter

Labels

Components

Affects versions

Priority

Created January 4, 2014 at 7:57 AM
Updated January 20, 2025 at 6:10 PM
Resolved January 20, 2025 at 6:10 PM