JPA coalesce / NVL, bind parameters and database index usage

Question

There is a common approach in Oracle to use predicate NVL(some_column, ' ') = ' ' when we want to use function-based index on nullable column like this:

CREATE INDEX idx_some_index ON some_table (NVL(some_column, ' '));

The predicate built in JPA:

criteriaBuilder.equal(criteriaBuilder.coalesce(root.get("some_column"), " "), " ")

The result SQL predicate generated by hibernate implementation (also by eclipse implementation):

nvl(sometable0_.some_column, ?)=?

It does not allow oracle to use function-based index because of use first bind parameter. So as a result oracle uses FULL SCAN.

According to the code (LiteralExpression.render(...)) JPA implementation creates bind parameters for all string literals (non-numeric literals). I think it uses this approach to avoid possible SQL-injections...

Similar case with numeric column works fine: nvl(sometable0_.some_column, -1)=-1 (the second -1 can be replaced with criteriaBuilder.parameter(...) not to force Oracle to do hard parse for every new parameter value).

So my question: is there any legal way to force JPA not to use bind parameter in the case when I am absolutely sure that the injection is not possible?

ps. I can use my own implementation of expression (constructed bypassing the criteria builder) like this:

public class UnsafeLiteralExpression<T> extends ExpressionImpl<T> implements Serializable {
    @SuppressWarnings({ "unchecked" })
    public UnsafeLiteralExpression(T literal) {
        this((Class<T>) determineClass( literal ), literal );
    }

    public UnsafeLiteralExpression(Class<T> type, T literal) {
        super(null, type );
        this.literal = literal;
    }

    //...

    public String render(CriteriaQueryCompiler.RenderingContext renderingContext) {
        return renderProjection(renderingContext);
    }

    //...
}

But I don't consider this is correct.

Answer 1

Starting with 11g you may use virtual column

   some_column_nvl VARCHAR2(10) GENERATED ALWAYS AS (NVL(some_column,' ')) VIRTUAL,

and use ordinary index on this column

create index test_idx on test(some_column_nvl);

The virtual column handles the NVL in the database, so you may simple query:

 criteriaBuilder.equal(root.get("some_column_nvl"), " ")

using the index

 |*  2 |   INDEX RANGE SCAN                  | TEST_IDX |     2 |       |     1   (0)| 00:00:01 |

to get all records with some_column blank or NULL