We're updating the issue view to help you get more done. 

QueryException when using CriteriaBuilder.function()

Description

I was trying to do a simple select using JPA:

1 SELECT POW(2, 10) FROM COUNTRY

So, I give it a try:

1 2 3 4 5 6 EntityManager em = getEM(); CriteriaBuilder cb = em.getCriteriaBuilder( ); CriteriaQuery< Object > cq = cb.createQuery( ); cq.from( Country.class ); cq.multiselect( cb.function( "POW", Integer.class, cb.literal( 2 ), cb.literal( 10 ) ) ); em.createQuery( cq ).getResultList( );

And this gives me back a QueryException:

1 2 3 4 5 6 7 java.lang.IllegalArgumentException: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode \-[METHOD_CALL] MethodNode: 'function (POW)' +-[METHOD_NAME] IdentNode: 'POW' {originalText=POW} \-[EXPR_LIST] SqlNode: 'exprList' +-[NUM_INT] LiteralNode: '2' \-[NUM_INT] LiteralNode: '10' [select function('POW', 2, 10) from model.Country as generatedAlias0]

Then, I tried to cast it:

1 2 3 4 5 6 EntityManager em = getEM(); CriteriaBuilder cb = em.getCriteriaBuilder( ); CriteriaQuery< Object > cq = cb.createQuery( ); cq.from( Country.class ); cq.multiselect( cb.function( "POW", Integer.class, cb.literal( 2 ), cb.literal( 10 ) ).as( Long.class ) ); em.createQuery( cq ).getResultList( );

(I have tried to cast it as String and Long. Casting to Integer results in the same exception as above.)

And then I get another QueryException:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 java.lang.IllegalArgumentException: org.hibernate.QueryException: CAST function should only have 2 arguments [select cast(function('POW', 2, 10) as string) from model.Country as generatedAlias0] [...] at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:655) ... 31 more Caused by: org.hibernate.QueryException: CAST function should only have 2 arguments at org.hibernate.hql.internal.ast.SqlGenerator$CastFunctionArguments.betweenFunctionArguments(SqlGenerator.java:300) at org.hibernate.hql.internal.ast.SqlGenerator.betweenFunctionArguments(SqlGenerator.java:137) at org.hibernate.hql.internal.antlr.SqlGeneratorBase.methodCall(SqlGeneratorBase.java:2584) at org.hibernate.hql.internal.antlr.SqlGeneratorBase.selectExpr(SqlGeneratorBase.java:2130) at org.hibernate.hql.internal.antlr.SqlGeneratorBase.selectColumn(SqlGeneratorBase.java:1942) at org.hibernate.hql.internal.antlr.SqlGeneratorBase.selectClause(SqlGeneratorBase.java:555) at org.hibernate.hql.internal.antlr.SqlGeneratorBase.selectStatement(SqlGeneratorBase.java:197) at org.hibernate.hql.internal.antlr.SqlGeneratorBase.statement(SqlGeneratorBase.java:146) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.generate(QueryTranslatorImpl.java:248) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:209) ... 37 more

What am I doing wrong?
Is there something I can do to solve this issue on the "user" side?

I noticed this error occurs with the following functions:

  • CHARSET

  • CONNECTION_ID

  • CONCAT_WS

  • CONV

  • DATABASE

  • POW

  • POWER

  • FORMAT

  • REPEAT

  • REPLACE

  • SUBSTR

  • USER

  • UUID

P.S.: I tried the above query using EclipseLink and it works fine.
P.S.2: POW and POWER don't work and are the same function. But in the SUBSTR and SUBSTRING pair, only the first don't work.

(As reported in https://stackoverflow.com/questions/45725573/queryexception-on-hibernate-when-using-criteriabuilder-function?noredirect=1#comment78415129_45725573)

Environment

Hibernate 5.2.10
MySQL 5.7.18 (localhost)
MySQL-connector 5.1.42 (driver)
Windows 10
Java 1.8.0_131-b11
Dialect: org.hibernate.dialect.MySQLDialect
Database: MySQL Default Examples (schema world)

Status

Assignee

Unassigned

Reporter

Miguel Koscianski Vidal

Fix versions

None

backPortable

None

Suitable for new contributors

Yes, likely

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

5.2.10

Priority

Major