HHH-8632-4.zip HHH-8632-5.zip 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:

select count(m.id) as col_0_0_ 
from marktteilnehmer m 
where decode(m.id, 0, null, m.name||decode(m.vorname,null, null, ', ' ||m.vorname)) like '%'

and not to hard using the criteria API:

final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
final Root<Marktteilnehmer> root = query.from(Marktteilnehmer.class);

final Expression<String> nameAndVorname = criteriaBuilder.function(
	"decode", String.class,
	root.get("id"),
	criteriaBuilder.literal(0l),
	criteriaBuilder.nullLiteral(String.class),
	criteriaBuilder.concat(
		root.<String>get("name"),
		criteriaBuilder.function("decode", String.class,
			root.get("vorname"),
			criteriaBuilder.nullLiteral(String.class),
			criteriaBuilder.nullLiteral(String.class),
			criteriaBuilder.concat(
				criteriaBuilder.literal(", "),
				root.<String>get("vorname")
			)
		)
	)
);

long cnt = entityManager.createQuery(
	query.select(criteriaBuilder.count(root)).where(criteriaBuilder.like(nameAndVorname, "%"))
).getSingleResult();

The generated sql looks like this:

select count(marktteiln0_.id) as col_0_0_ 
	  from marktteilnehmer marktteiln0_ 
         where decode(marktteiln0_.id, 0, null, marktteiln0_.name||decode(marktteiln0_.vorname||null||null||?||marktteiln0_.vorname)) like ?

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!