Parameter value did not match expected type

[re1709]

trafficstars

Description

Using the criteria builder with a whereExists and combination of where like, and conditionally a 'whereOr' breaks when the like condition happens first. An exception is thrown with the paramater being the wrong type.

If the like query is moved to the end of the whereExists everything work ok, so not sure if we have done something wrong with the criteria builder here...or if there is a bug with Blaze or Hibernate itself.

The error:

java.lang.IllegalArgumentException: Parameter value [%search term%] did not match expected type [java.lang.Long (n/a)] at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:54) at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:27) at org.hibernate.query.internal.QueryParameterBindingImpl.validate(QueryParameterBindingImpl.java:90) at org.hibernate.query.internal.QueryParameterBindingImpl.setBindValue(QueryParameterBindingImpl.java:55) at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:501) at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:122)

Expected behavior

Not sure why the order here matters of the like clause.

Actual behavior

Like clause causes paramater type exception if placed before other clauses with a parameter, when using whereExists.

Steps to reproduce

Code that results in error (only when condition user restrictions are applied, else works ok):

var cb = BlazeCriteria.get(builderFactory);
var query = cb.createQuery(returnType);
var root = query.from(Document.class, "d");
        
var builder = query.createCriteriaBuilder(entityManager);

var whereOrBuilder = builder.whereOr();

for (String searchColumn : config.getSearchColumns()) {
    String searchParam = "%" + searchValue + "%";
    
    switch (searchColumn) {
        case "values" -> {
            var valuesBuilder = whereOrBuilder.whereExists().from("values", "v");
            valuesBuilder.where("v.value").like(false).value(searchParam).noEscape();
            if (applyUserRestrictions) {
                valuesBuilder
                        .whereOr()
                            .where("d.confidential").eq(false)
                            .where("d.userId").eq(userid)
                        .endOr();
            }
            valuesBuilder.end();
        }
        case "id" -> whereOrBuilder.where("CAST_STRING(" + searchColumn + ")").like().value(searchParam).noEscape();
        default -> whereOrBuilder.where(searchColumn).like(false).value(searchParam).noEscape();
    };
}

whereOrBuilder.endOr();

Code that works (where("v.value") line moved after the conditional user restrictions):

var cb = BlazeCriteria.get(builderFactory);
var query = cb.createQuery(returnType);
var root = query.from(Document.class, "d");
        
var builder = query.createCriteriaBuilder(entityManager);

var whereOrBuilder = builder.whereOr();

for (String searchColumn : config.getSearchColumns()) {
    String searchParam = "%" + searchValue + "%";
    
    switch (searchColumn) {
        case "values" -> {
            var valuesBuilder = whereOrBuilder.whereExists().from("values", "v");
            if (applyUserRestrictions) {
                valuesBuilder
                        .whereOr()
                            .where("d.confidential").eq(false)
                            .where("d.userId").eq(userid)
                        .endOr();
            }
            valuesBuilder.where("v.value").like(false).value(searchParam).noEscape();
            valuesBuilder.end();
        }
        case "id" -> whereOrBuilder.where("CAST_STRING(" + searchColumn + ")").like().value(searchParam).noEscape();
        default -> whereOrBuilder.where(searchColumn).like(false).value(searchParam).noEscape();
    };
}

Environment

Version: 1.6.14
JPA-Provider: Hibernate 5.6.15.Final (but also tested on 6.6 with same issue)
DBMS: PostgreSQL 16
Application Server: Spring Boot Java 17

[beikov]

Could you please post the HQL that is generated for both cases that fails?

[re1709]

Sorry for only just getting back on this, but please see HQL. If you need any further please let me know. As a fix, we have changed the searchParam to a param on the builder and referenced it using an expression. Still curious to why the original way is an issue though and whether we did something wrong. Thanks

Not working (like clause happens first in exists, and conditional restrictions applied in code):

SELECT DISTINCT d.id AS DocumentReportView2_id, d.documentType.id AS DocumentReportView2_documentType_id, d.documentType.id AS DocumentReportView2_documentType_captures FROM Document d WHERE d.customer = :param_0 AND d.uploaded >= :param_1 AND d.uploaded <= :param_2 AND d.hideInTableViews = :param_3 AND (cast_string(d.id) LIKE :param_4 OR (UPPER(d.originalFilename) LIKE UPPER(:param_5) AND (d.confidential = :param_6 OR d.originator = :param_7)) OR UPPER(d.statusText) LIKE UPPER(:param_8) OR UPPER(d.rejectionReason) LIKE UPPER(:param_9) OR UPPER(d.goneawaysReason) LIKE UPPER(:param_10) OR UPPER(d.postage) LIKE UPPER(:param_11) OR EXISTS (SELECT 1 FROM DocumentValue v, d.originator originator WHERE v.document.id = d.id AND UPPER(v.value) LIKE UPPER(:param_12) AND (d.confidential = :param_13 OR originator = :param_14 OR (v.id.name NOT IN (:param_15) AND v.id.name NOT IN (SELECT c.name FROM d.documentType dt JOIN dt.captures c))))) ORDER BY DocumentReportView2_id ASC

Working (with like clause moved to end of where):

SELECT DISTINCT d.id AS DocumentReportView2_id, d.documentType.id AS DocumentReportView2_documentType_id, d.documentType.id AS DocumentReportView2_documentType_captures FROM Document d WHERE d.customer = :param_0 AND d.uploaded >= :param_1 AND d.uploaded <= :param_2 AND d.hideInTableViews = :param_3 AND (cast_string(d.id) LIKE :param_4 OR (UPPER(d.originalFilename) LIKE UPPER(:param_5) AND (d.confidential = :param_6 OR d.originator = :param_7)) OR UPPER(d.statusText) LIKE UPPER(:param_8) OR UPPER(d.rejectionReason) LIKE UPPER(:param_9) OR UPPER(d.goneawaysReason) LIKE UPPER(:param_10) OR UPPER(d.postage) LIKE UPPER(:param_11) OR EXISTS (SELECT 1 FROM DocumentValue v, d.originator originator WHERE v.document.id = d.id AND (d.confidential = :param_12 OR originator = :param_13 OR (v.id.name NOT IN (:param_14) AND v.id.name NOT IN (SELECT c.name FROM d.documentType dt JOIN dt.captures c))) AND UPPER(v.value) LIKE UPPER(:param_15))) ORDER BY DocumentReportView2_id ASC

Working (when conditional restrictions not applied, so like clause appears at end anyway):

SELECT DISTINCT d.id AS DocumentReportView2_id, d.documentType.id AS DocumentReportView2_documentType_id, d.documentType.id AS DocumentReportView2_documentType_captures FROM Document d WHERE d.customer = :param_0 AND d.uploaded >= :param_1 AND d.uploaded <= :param_2 AND d.hideInTableViews = :param_3 AND (cast_string(d.id) LIKE :param_4 OR (UPPER(d.originalFilename) LIKE UPPER(:param_5)) OR UPPER(d.statusText) LIKE UPPER(:param_6) OR UPPER(d.rejectionReason) LIKE UPPER(:param_7) OR UPPER(d.goneawaysReason) LIKE UPPER(:param_8) OR UPPER(d.postage) LIKE UPPER(:param_9) OR EXISTS (SELECT 1 FROM DocumentValue v WHERE v.document.id = d.id AND UPPER(v.value) LIKE UPPER(:param_10))) ORDER BY DocumentReportView2_id ASC

[beikov]

It's kind of odd that you say the first is not working but the second one is, since the queries are equivalent. It's just that one predicate is at a different position.

Could you please try creating a reproducer for this problem for me to look into e.g. based on one of our quickstarts?

[re1709]

Sure will try to get this done tomorrow between things. Thanks for looking into this, drove me insane!

Could it just be a bug with the parameter type checking? I did a small amount of debugging and when it didn't work the parameter for the search term ended up checking the originator parameter.

Interestingly we are using an entity view with a sub entity view that has a subselect of a further list entity view. If i comment out the subselect to use the default join strategy then everything works (regardless of where the like clause is placed). Sorry my knowledge ends here as feel like this could be a bug deep in hibernate.

[beikov]

Well, this could actually be a problem with the subselect strategy in entity-views. I hope that with a reproducer, I can figure this out.

[re1709]

reproducer.zip

Please see attached reproducer (took me some time to actually reproduce as seems to be a very specific combination of things..). With hibernate 6.6 moving the line as per the original post no longer fixes the issue.

Not sure if it helps but found that:

1. If the subselect mapping is removed from the entity view, then everything works ok.
2. The bug seems to require a minimum number of where clauses. If i remove a single one of them, the exception no longer occurs but obviously our query is then wrong. I created a working test as an example in the reproducer.
3. If a name parameter is used and set on the builder (and used as an expression instead of value multiple times) then this fixes the issue - the fix we have gone for in production code.