org.hibernate.QueryException: CAST function should only have 2 arguments

Hibernate ORM
1

Hi guys. Please help me here. I’ve been on this since yesterday. This query works pretty fine on MySQL Workbench but Hibernate interpretes this differently. I’ll tried all manner of variants but to no avail. I found this post No data type for node [MethodNode] very educating, made use of more recent hibernate version but while it helped by resolving the methodnode error, i started having the same error my topic suggest.
The query is goes like this:


StringBuilder queryBuilder = new StringBuilder("SELECT ");
        queryBuilder.append(
                          "CAST(FUNCTION('JSON_EXTRACT', s.JsonColumn, '$.datStatus') AS string), "
                        + "COUNT(CASE WHEN (FUNCTION('JSON_EXTRACT', s.JsonColumn, '$.datStatus')) IS NOT NULL THEN 1 ELSE NULL END), "
                        + "SUM(s.amount) "
                        + "FROM MyTable s "
                        + "WHERE s.anotherColumn IN ('FAILED', 'UNSETTLED'') "
                        + "AND FUNCTION('JSON_EXTRACT', s.extraData, '$.datStatus') IS NOT NULL "
			+ "GROUP BY FUNCTION('JSON_EXTRACT', s.JsonColumn, '$.datStatus') "
			+ "ORDER BY FUNCTION('JSON_EXTRACT', s.JsonColumn, '$.datStatus') ASC ");

And below is the Stack trace:

Caused by: org.hibernate.QueryException: CAST function should only have 2 arguments
	at org.hibernate.hql.internal.ast.SqlGenerator$CastFunctionArguments.betweenFunctionArguments(SqlGenerator.java:295)
	at org.hibernate.hql.internal.ast.SqlGenerator.betweenFunctionArguments(SqlGenerator.java:132)
	at org.hibernate.hql.internal.antlr.SqlGeneratorBase.methodCall(SqlGeneratorBase.java:2542)
	at org.hibernate.hql.internal.antlr.SqlGeneratorBase.selectExpr(SqlGeneratorBase.java:2088)
	at org.hibernate.hql.internal.antlr.SqlGeneratorBase.selectColumn(SqlGeneratorBase.java:1900)
	at org.hibernate.hql.internal.antlr.SqlGeneratorBase.selectClause(SqlGeneratorBase.java:544)
	at org.hibernate.hql.internal.antlr.SqlGeneratorBase.selectStatement(SqlGeneratorBase.java:186)
	at org.hibernate.hql.internal.antlr.SqlGeneratorBase.statement(SqlGeneratorBase.java:135)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.generate(QueryTranslatorImpl.java:244)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:210)
	... 134 more
2

Check the syntax of your generated query. You have at least one problem in there 'UNSETTLED'' i.e. an extra '

3

Hi Beikov. I’m sorry for replying lately now. Thanks for the correction. Actually, in my code I didn’t have that typo error. It was while I copied and tried to edit in here I had that typo. I’ll reshare the code again here. I still have the same error till today.
We use hibernate-entitymanager; I’ve updated to 5.6.7 but still didn’t work.

StringBuilder queryBuilder = new StringBuilder("SELECT ");
        queryBuilder.append(
                "CAST(FUNCTION('JSON_EXTRACT', s.jsonColumn, '$.datStatus') AS java.lang.String), "
                        + "COUNT(CASE WHEN (FUNCTION('JSON_EXTRACT', s.jsonColumn, '$.datStatus')) IS NOT NULL THEN 1 ELSE NULL END), "
                        + "SUM(s.amount) "
                        + "FROM MyTable s "
                        + "WHERE s.anotherColumn IN ('FAILED', 'UNSETTLED', 'DELIVERY_TOKEN_REQUIRED') "
                        + "AND FUNCTION('JSON_EXTRACT', s.jsonColumn, '$.datStatus') IS NOT NULL ");

Please help

4

Try casting to String instead of java.lang.String

5

Gave an error - Caused by: org.hibernate.QueryException: Could not resolve requested type for CAST : String

FYI, I’d earlier used string and though it didn’t give the type-error, it threw the same CAST function should only have 2 arguments

6

By looking at the source code of Hibernate 5, this looks like a limitation of the SQL rendering when the function is unknown. I’d suggest you register the function JSON_EXTRACT into your Dialect, then it should work fine e.g.

public class JsonExtract implements SQLFunction {

    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }

    @Override
    public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException {
        return StandardBasicTypes.STRING;
    }

    @Override
    public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
        return "json_extract(" + args.get(0) + "," + args.get(1) + ")";
    }
}
2 Likes
7

It has worked. Thanks a great deal.

But I have a concern. Will this not make the method getReturnType(…) always return a String? Cos other methods exist in this Service class that makes use of the same JsonExtract function but may not need to return a String type but Integer or any other type instead.

8

Then you have to cast the result. The problem is that casting function('<your function>', ..) doesn’t properly work.

9

Alright. Well noted. thanks so much.

PS: I sent you a connect invite on LinkedIn too