ORDER BY FIELD(..) throws java.sql.SQLException: Operand should contain 1 column(s)

Description

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!

Environment

Ubuntu Linux 15.10, Java 8, Hibernate 5.0.7, MySQL Driver 5.1.34

Activity

Show:
Gail Badner
February 24, 2016, 2:08 AM

, 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.

Steve Ebersole
February 25, 2016, 3:25 AM

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

Gail Badner
April 7, 2016, 8:23 PM

A pull request with test that reproduces this issue along with a fix would be most welcome!

Vlad Mihalcea
August 30, 2016, 12:59 PM

You might want to backport it to 5.0 and 5.1 as well.

Yordan Gigov
October 4, 2016, 5:25 AM

@steve

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 4.6.17.5 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.

Fixed

Assignee

Vlad Mihalcea

Reporter

Benjamin Makus

Fix versions

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Minor
Configure