Micronaut Data JDBC Criteria API upper and lower methods are not working correctly.

[ralmeida7]

Expected Behavior

By using the UPPER method from criteria builder, I expect the generated SQL statement to include the UPPER function.

    public static <T> PredicateSpecification<T> titleContains(String title) {
        return (root, criteriaBuilder) -> criteriaBuilder.like(criteriaBuilder.upper(root.get("title")), "%" + title.toUpperCase() + "%");
    }

Generated SQL statement:

select b1_0.id,b1_0.author,b1_0.title from book b1_0 where upper(b1_0.title) like ? escape '' offset ? rows

This works fine with Micronaut Data JPA

Actual Behaviour

The generated SQL statement is not including the UPPER function.

SELECT book_.`id`,book_.`title`,book_.`author` FROM `book` book_ WHERE ('io.micronaut.data.model.jpa.criteria.impl.expression.UnaryExpression@2e3572e8' LIKE ?)

Steps To Reproduce

1. Download the example application
2. Use the "jdbc" branch git checkout jdbc
3. Run the test ./gradlew test --tests "com.example.BookRepositoryTest.testUpperCriteriaQuery"

The same implementation with Micronaut Data JPA can be found in the branch "jpa" git checkout jpa

Environment Information

• Operating System: MacOS Sonoma
• JDK: 21

Example Application

https://github.com/ralmeida7/criteria-builder-bugs

Version

4.6.0

[ibmsoft]

in the document https://micronaut-projects.github.io/micronaut-data/latest/guide/#dbcCriteriaSpecifications it seem like not support

Micronaut Criteria API currently implements only a subset of the API. Most of it is internally used to create queries with predicates and projections.

Currently, not supported JPA Criteria API features:

Joins with custom ON expressions and typed join methods like joinSet etc

Sub-queries

Collection operations: isMember etc

Custom or tuple result type

Transformation expressions like concat, substring etc.

Cases and functions

[ralmeida7]

In the release notes of the version 4.6.0

Micronaut Data 4.9.0 contains improvements to the criteria API expressions.

Now upper and lower methods are implemented

    @Override
    @NonNull
    public Expression<String> lower(@NonNull Expression<String> x) {
        return new UnaryExpression<>(x, UnaryExpressionType.LOWER);
    }

    @Override
    @NonNull
    public Expression<String> upper(@NonNull Expression<String> x) {
        return new UnaryExpression<>(x, UnaryExpressionType.UPPER);
    }

In previous versions

    /**
     * Not supported yet.
     *
     * {@inheritDoc}
     */
    @Override
    @NonNull
    public Expression<String> lower(@NonNull Expression<String> x) {
        throw notSupportedOperation();
    }

    /**
     * Not supported yet.
     *
     * {@inheritDoc}
     */
    @Override
    @NonNull
    public Expression<String> upper(@NonNull Expression<String> x) {
        throw notSupportedOperation();
    }