Since the update from Hibernate 5.0.6 to 5.0.7 our queries using ORDER BY FIELD won't work anymore. It throws java.sql.SQLException: Operand should contain 1 column(s)
The Query simply is:
SELECT e FROM MyEntity e WHERE e.id IN (:ids) ORDER BY FIELD(id, :ids)
The generated SQL shows a difference:
5.0.6: ... order by FIELD(id, ?, ?, ?)
5.0.7: ... order by FIELD(id, (?, ?, ?))
Thanks in advance!
Ubuntu Linux 15.10, Java 8, Hibernate 5.0.7, MySQL Driver 5.1.34
, I agree, but I'd still like to get Steve's thoughts on this. , is this a supported use case?
I've added a "regression" label because the failure is caused by HHH-10383.
Personally I think we opened a can of worms attempting to auto-apply parenthesis for users. The basis of that seems to be a belief that "the JPA implementation is supposed to add them", which is flat out inaccurate; JPA does not even define support for multi-valued params - that's a Hibernate feature. And I have always agreed with Christian's sentiment: I think the onus should be on the user to define the parentheses.
However, since we did add this auto-parenthesising I think this has to be a regression
A pull request with test that reproduces this issue along with a fix would be most welcome!
You might want to backport it to 5.0 and 5.1 as well.
The basis of that seems to be a belief that "the JPA implementation is supposed to add them", which is flat out inaccurate; JPA does not even define support for multi-valued params - that's a Hibernate feature.
In the JPA 2.1 PDF, page 195, section 18.104.22.168 they give this example as a valid query:
They may not have specifically outlined it in the setParameter definition, but collection expansion is expected behaviour.
Sounds like you've thought of it as a bonus feature, when it really should be standard.