Java Hibernate BUG when using query parameters in order clause
When using query parameters the ORDER BY clause in combination with a oneToMany relation with the fetch strategy subselect, Hibernate 5.0.10 throws an exception: java.sql.SQLException: Invalid column index
The Problem: Hibernate executes the query perfectly. But afterwards, when loading the referenced oneToMany relation, it reuses parts of the query (especially the WHERE clause) to load all referenced entities. Some parts of the query, like the ORDER BY clause will be stripped off. Unfortunately, all query parameters from the original query were taken as they are into the subselect query without checking if they are still required. When Hibernate executes the subselect query it binds more parameters than there are in the query which leads to the exception.
Is it a bug or is it not supported in JPA?
edit - Adding quote from JPA spec
Input parameters can only be used in the WHERE clause or HAVING clause of a query or as the new value for an update item in the SET clause of an update statement.
But it would be a nice Hibernate feature anyway. Furthermore, the criteria API binds all literals as parameter. So it is not possible to use literals in the select/groupBy/orderBy clause using the criteria API. Is this also specified by JPA that way?
I have set up a scenario to replicate this issue:
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 mapping
<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
em.createQuery("SELECT p FROM Person p ORDER BY concat(:prefix, p.name)")
.setParameter("prefix", "any")
.getResultList(); //Exception here
edit - Inserted Log statements
You can see, that the first query executes nicely, but the second query results in an exception.
10:01:23,749 DEBUG [org.hibernate.SQL] (default task-22) select person0_.ID as ID1_1_, person0_.NAME as NAME2_1_ from APERSON person0_ order by ?||person0_.NAME
10:01:23,756 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] (default task-22) binding parameter [1] as [VARCHAR] - [any]
10:01:23,766 DEBUG [org.hibernate.SQL] (default task-22) select address0_.PERSON as PERSON3_0_1_, address0_.ID as ID1_0_1_, address0_.ID as ID1_0_0_, address0_.NAME as NAME2_0_0_ from AADDRESS address0_ where address0_.PERSON in (select person0_.ID from APERSON person0_ )
10:01:23,770 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] (default task-22) binding parameter [1] as [VARCHAR] - [any]
10:01:23,775 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-22) SQL Error: 17003, SQLState: 99999
10:01:23,775 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-22) Invalid column index