CriteriaBuilder#concat(Expression<String>, Expression<String>) produces wrong SQL expressions
The #concat method of CriteriaBuilder produces the wrong SQL statement on Oracle 10g and 11g using Oracle JDBC 126.96.36.199.
The following expressions
produces the following sql fragment (linebreaks inserted for readability:
Problem seems to be the second concat...
Oracle 10g, Oracle 11g
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!
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.
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/
Expected output is: