Hibernate assumes arguments in HQL query where there are none

Why does Hibernate complain about the following query:

  public Set<Long> findImageVariantIdsWithOutOfBoundsDimension() {
    final StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append("select id from ImageVariant where ");
    // imageVariantType is something like R_110X75 with 110 being max x.
    // cast(substring( extracts 110.
    queryBuilder.append("dimensionX > cast(substring(imageVariantType, 3, locate('X', imageVariantType) - 3), int) ");
    queryBuilder.append("or ");
    // imageVariantType is something like R_110X75 with 75 being max y.
    // cast(substring( extracts 75.
    queryBuilder.append("dimensionY > cast(substring(imageVariantType, locate('X', imageVariantType) + 1), int)");
    final Query query = getEntityManager().createQuery(queryBuilder.toString());
    @SuppressWarnings("unchecked")
    final Set<Long> result = new HashSet<Long>(query.getResultList());
    return result;
  }

The warning is Function 开发者_如何学运维template anticipated 3 arguments, but 2 arguments encountered. However, my query contains neither tokens nor arguments. Since cast is a Hibernate specific function (no string-to-int conversion in JPQL) I assume it's a HQL issue.

When I debug Hibernate's TemplateRenderer I see that it's internal argument list is ['X', imagevaria0_.imagevarianttype].

This is basically Hibernate warning that some SQL function is not being called with all its admissible parameters. In your case, this is the LOCATE function. The syntax for this function is LOCATE(string1, string 2, [start]), where start is an optional parameter that specifies the index of the first character from where to start the search.

In your case, you are calling this function as locate('X', imageVariantType), passing it only two parameters instead of the maximum 3 allowed. The Hibernate class TemplateRenderer detects this and generates the warning message you see.

I have raised a bug with the Hibernate team to either lower the severity of this message (since it does not stop the query from working correctly in most cases) or implement a better check around it so that the warning does not get generated in cases that are valid for the given SQL function.

As for anyone running into a similar issue, this warning can be simply ignored if the query works fine.

When you knew it is hibernate specific function why are you creating query using JPA?

Try getting hibernate session and creating query on it. You can get hibernate session using

Session session = (Session) em.getDelegate();

EDIT: getEntityManager().createQuery(queryBuilder.toString()); assumes the query string you passed is JPQL right? You should not be using HQL specific features in this. You need to get HQL session instead of JPA entity manager to create the query.

EDIT: I tried this and it is working fine for me. My configuration is JPA 2.0 with Hibernate 3.6.0 and MySQL 5.1.28. What is your configuration? –