[FEATURE REQ] Change to how case sensitivity is added to functions in queries

[azizabah]

Is your feature request related to a problem? Please describe.
There is a performance issue when using Case Insensitive querying with CosmosDB and functions. Currently the code is wrapping all of those calls with UPPER() around the parameters. UPPER() forces a table scan without indexes per https://docs.microsoft.com/en-us/azure/cosmos-db/sql/sql-query-upper. This is easy to see the performance implications with some simple querying:

--13.87 RUs
SELECT * FROM c where c.boatId = "TEST3"

--3,268.83 RUs
SELECT * FROM c where STARTSWITH(UPPER(c.boatId), UPPER("TEST3"))

--14.37 RUs
SELECT * FROM c where STARTSWITH(c.boatId, "TEST3", true)

It becomes very clear when comparing this scenario that there is room for improvement. Those numbers are from actual queries in an actual database.

Describe the solution you'd like
The example above alludes to my suggestion for a solution also. This method -

azure-sdk-for-java/sdk/cosmos/azure-spring-data-cosmos/src/main/java/com/azure/spring/data/cosmos/core/generator/AbstractQueryGenerator.java

Line 100 in 53aa670

private String getFunctionCondition(final Part.IgnoreCaseType ignoreCase, final String sqlKeyword,

currently handles the UPPER() logic. If it were adapted more along these lines:

 private fun getFunctionCondition(
        ignoreCase: IgnoreCaseType, sqlKeyword: String,
        subject: String, parameter: String
    ): String {
        return if (IgnoreCaseType.NEVER == ignoreCase) {
            String.format("%s(r.%s, @%s)", sqlKeyword, subject, parameter)
        } else {
            String.format("%s(r.%s, @%s, true)", sqlKeyword, subject, parameter)
        }
    }

Then it would benefit from the index scan performance improvements.

Additional context
That call relies on a check of CriteriaType.isFunction(CriteriaType type). Currently that check would also return true for IS_NULL and IS_NOT_NULL. Those functions do not support the third parameter per https://docs.microsoft.com/en-us/azure/cosmos-db/sql/sql-query-is-null and would need some additional logic to prevent accidental usage. Perhaps a check of some kind around CriteraType.isUnary(CriteriaType type) returning false or something along those lines.

Information Checklist
Kindly make sure that you have added all the following information above and checkoff the required fields otherwise we will treat the issuer as an incomplete report

• Description Added
• Expected solution specified

[azizabah]

@kushagraThapar - If you've got someone looking at the QueryGenerator code, here's an easy, big win.

[kushagraThapar]

Thank you @azizabah - this is great, appreciate the insights. We will investigate and incorporate this.

[ghost]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @kushagraThapar, @simplynaveen20, @abinav2307