FunctionKeyExpression is not honored when evaluating the QueryPlan

FunctionKeyExpression don’t seem to be honored in the evaluation of a query plan.
We came across a test casewhich exposes this .
Is there any plan to make FunctionKeyExpressions allowed to be picked for indexes ?
Are there any workarounds for the same ?

No, queries cannot currently use FunctionKeyExpressions. The main problem is that the they currently have to way to express the structure of the index keys that they produce in a manner that would be meaningful to the planner. For example, you could have a function that picks the third letter of every string field in the record and concatenates them to produce a fan-out index key — there planner has absolutely no insight into what is going on in the guts of the function to know how query arguments could be applied against the index.

There is work going on in the planner that I will let others chime in on that may pave a path to having fully exposed function based indexes.

What sort of index are you trying to create?

Thank you for the insight @scgray.

Our use case is pretty straightforward, We’re trying to create Uppercase string indexes on a few fields of our data.

Oh, ok, then you probably want to take a look at collating indexes. These are in the fdb-record-layer-icu package. Here is an example:

Ah ok! Let me take a look at that. I’ll come back with my findings.

First, I will second Scott’s recommendation that you try a collating index with case-insensitivity (3) strength. They are designed for just this kind of use.

That said, an index defined with a simple function, such as one that uppercases, on a field can in fact be used in a query. The restriction is that the entire key expression that defines the index must be used verbatim in the query with [in]equality predicates. And the FunctionKeyExpression implementation must be marked as a QueryableKeyExpression.

So, given UPPER(f), you can do the equivalent of `WHERE UPPER(f) >= ‘e’ AND UPPER(f) <= ‘m’ to scan such an index between those bounds.

What doesn’t work is planner support for “understanding” what the index does. An imaginary case of this would be something like WHERE UPPER(f >= 'e' AND f <= 'm').

I have created a pull request to fix the test that you found to demonstrate the simple case that does work today.