JPQL FUNCTION call not working in LIKE

Hibernate ORM
1

Hello,

With HIbernate 6.6, I have an issue when calling JPQL FUNCTION in LIKE.

The following test fails with error: org.hibernate.query.SemanticException: Operand of 'like' is of type 'java.lang.Object' which is not a string (its JDBC type code is not string-like)

package org.hibernate.orm.test.query.hql;

import org.hibernate.Session;
import org.hibernate.orm.test.jpa.model.AbstractJPATest;
import org.junit.jupiter.api.Test;

/**
 * Test use of like with functions
 *
 */
public class FunctionAndLikeJPQLTest extends AbstractJPATest {

	@Test

	public void functionSubstrLikeTest() {
		try (Session session = this.sessionFactoryScope().getSessionFactory().openSession()) {
			// Assertions.assertThrows(IllegalArgumentException.class, () -> {
			session.createQuery("select i from Item i where function( 'substr', i.name, 1, 3 ) like 'abc'")
					.list();
			// });
		}
	}

	@Test
	public void likeFunctionSubstrTest() {
		try (Session session = this.sessionFactoryScope().getSessionFactory().openSession()) {
			// Assertions.assertThrows(IllegalArgumentException.class, () -> {
			session.createQuery("select i from Item i where 'abc' like function( 'substr', i.name, 1, 3 )")
					.list();
			// });
		}
	}
}

Per JPA spec, at least functionSubstrLikeTest should work, I am not sure about likeFunctionSubstrTest

The test fails from hibernate branches 6.6, 7.1 to main branch.

Regards

2

Hello @ghislain-rouvignac_f, why are you using a custom function() expression instead of Hibernate’s standard substring()? Anyway, if you really need to do that, you should be able to easily workaround that by using cast(function(...) as String) in your like expression.

3

Hello @mbladel,

In my case I have the problem with a custom function registered in the Dialect.
In the unit test, I used substr as an example to easily build the test.
This problem is reproducible with any String manipulating function.
I want to be strict JPQL compliant this is why I use FUNCTION.

I know that I can workaround with cast(function(...) as String) but my purpose is to highlight a bug.

Per JPA spec:

like_expression ::= string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]


string_expression ::=
    state_valued_path_expression |
    string_literal |
    input_parameter |
    functions_returning_strings |
    aggregate_expression |
    case_expression |
    function_invocation |
    string_cast_function |
    string_expression || string_expression |
    (subquery)


function_invocation ::= FUNCTION(function_name {, function_arg}*)

my case is expected to work:

like_expression ::= FUNCTION(function_name {, function_arg}*) [NOT] LIKE pattern_value

My conclusion is that Hibernate does not comply with the spec.

I want to confirm I am right before filling a JIRA ticket.

Regards.

4

Yes @ghislain-rouvignac_f, feel free to open a Jira and, while you’re at it, consider contributing a fix yourself to the upstream Hibernate repository.

5

Filled issue: [HHH-19875] Hibernate not compliant with JPQL specification when calling “FUNCTION” in “LIKE” - Hibernate JIRA