CriteriaBuilder#concat(Expression<String>, Expression<String>) produces wrong SQL expressions

Description

The #concat method of CriteriaBuilder produces the wrong SQL statement on Oracle 10g and 11g using Oracle JDBC 11.2.0.3.

The following expressions

produces the following sql fragment (linebreaks inserted for readability:

Problem seems to be the second concat...

Environment

Oracle 10g, Oracle 11g

Activity

Show:
Michael Simons
October 24, 2013, 1:25 PM

Expected output is:

Steve Ebersole
October 27, 2015, 7:16 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/

Steve Ebersole
October 28, 2015, 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.

Michael Simons
January 14, 2016, 2:05 PM
Edited

Hello,

first of all, let me please apologize for the bad report. Not only was a real test case missing, the fragment i've posted is wrong also.

Nevertheless, the problem is still there.

Thanks to @mih_vlad it's pretty easy now to provide JPA test cases. Attached are test cases for both 4.3.11 and 5.0.7

What i want: Query a person on their full name. The full name is defined as "name, firstname" if there is a non-null first name, otherwise "name". Also, the person with id 0 is a special, not used instance.

It's pretty easy to do this in SQL:

and not to hard using the criteria API:

The generated sql looks like this:

which is pretty obvious wrong.

To use the test cases:

  • You'll need an Oracle (10g or 11g) database with the scott/tiger schema

  • Eventually fix the coordinates of the JDBC driver in pom.xml, i used com.oracle:ojdbc6, as far as i know Oracles JDBC driver still isn't in maven.org

  • Replace the line 19 in persistence.xml with your host and sid (<property name="hibernate.connection.url" value="jdbc:oracle:thin:@host:1521:sid"/>)

I would write it down as a native query, but it's part of a bigger system where a lot of generated queries are used for dynamically fetching stuff.

Thanks a lot for looking at it!

Assignee

Unassigned

Reporter

Michael Simons

Fix versions

None

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure