Query Parameters in Order clause

Description

The issue is already described on stackoverflow: http://stackoverflow.com/questions/41279113/java-hibernate-bug-when-using-query-parameters-in-order-clause

It is reproduceable with following tables and mappings. No classes required due to hibernate's non-pojo mode. Further note that this issue happens also, when using JPA criteria API and adding an order clause, because Hibernate resolves literals as named parameters.

— SQL
CREATE TABLE "APERSON"
(
"ID" VARCHAR2(50) PRIMARY KEY NOT NULL,
"NAME" VARCHAR2(255)
);
CREATE TABLE "AADDRESS"
(
"ID" VARCHAR2(50) PRIMARY KEY NOT NULL,
"PERSON" VARCHAR2(50) NOT NULL, – foreign key
"NAME" VARCHAR2(255)
);
INSERT INTO "APERSON" (ID, NAME) VALUES ('1', 'me');
INSERT INTO "APERSON" (ID, NAME) VALUES ('2', 'you');

— hbm file
<class entity-name="Person" table="APERSON">
<id name="id" column="ID" type="string" />
<property name="name" column="NAME" type="string" />
<bag name="address" fetch="subselect" lazy="false" inverse="true">
<key column="PERSON" />
<one-to-many entity-name="Address" />
</bag>
</class>

<class entity-name="Address" table="AADDRESS">
<id name="id" column="ID" type="string" />
<property name="name" column="NAME" type="string" />
</class>

— java code
em.createQuery("SELECT p FROM Person p ORDER BY concat(refix, p.name)").setParameter("prefix", "any").getResultList();

Environment

Hibernate 5.0.10, WildFly 10.1, Oracle 12.

Activity

Show:
Christian Beikov
February 20, 2017, 1:49 PM

The reason for this problem is that the SUBSELECT strategy uses only the sql up to the order by clause, but doesn't respect parameters that might come in the order by or other clauses.
I am planning to work on the subselect strategy as part of reworking the limit handling in 6.0. In the meantime you can either use a literal value instead of the parameter, or a different fetch strategy.

Rafael Jonach
February 20, 2017, 2:18 PM

I faced the problem when using JPA criteria API. So I cannot use literals because criteria literals are bound as parameter too. And I cannot use a different fetch strategy either.
Is there any possibility, that hibernate resolves criteria literals as real literals?

Christian Beikov
February 20, 2017, 2:27 PM

I guess the example you gave is not your actual query? Because ordering by CONCAT(refix, name) is as good as ordering by just the name, so you don't need the concat.
If you also select the expression that you want to order by, you could instead order by the select alias.

If that doesn't help, you will have to do your ordering in-memory instead i.e. use something like

In the end you could still patch the SubselectFetch and build your own hibernate version if you really want this to work.

Rafael Jonach
February 21, 2017, 6:46 AM

You said:

If you also select the expression that you want to order by, you could instead order by the select alias.

I'm not 100% sure, but I think the problem is the same in SELECT-clauses, because they will be thrown away too when subselecting. So you have to consider this too, when reworking the strategy in hibernate 6.0.

And no, this isn't my actual query. I would like to call a specific database function with the criteria API like "ORDER BY anyDbFunction(entity.name, 'arg1', 'arg2')". I just came up with the CONCAT-example to make it reproducible.

Furthermore, I had the same problem when ordering some enum-like database fields in a specific order using a SELECT-CASE:
ORDER BY
CASE
WHEN entity.CATEGORY='Foo'
THEN 0
WHEN entity.CATEGORY='Bar'
THEN 1
WHEN entity.CATEGORY='Baz'
THEN 2
ELSE 3
END ASC

Just to give you my real examples, in case you might want to consider it during the rework.
But thank you for your support! I'll find a solution

Christian Beikov
February 21, 2017, 7:20 AM

Thanks for the info. I'll create a testcase for that in 6.0 as well when working on the limit part.

Assignee

Unassigned

Reporter

Rafael Jonach

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure