Fix KQL case-sensitivity for keyword fields in ES|QL - #135776

Hi @afoucret, I've created a changelog YAML for you.

Pinging @elastic/es-search-relevance (Team:Search Relevance)

💚 Backport successful

Status	Branch	Result
✅	8.19
✅	9.1
✅	9.0
✅	8.18

Despite this being an inconsistent behavior, we actually have been exploiting this behavior in ES|QL to do case-insensitive searches on keywords where we ordinarily would have to resort to other hacks, like doing a regex DSL with case-insensitive set to true, or doing a to_lower()/upper() in ES|QL. That being said, those functions are much slower than the KQL() function.

It may be wise for there to be a toggle on this so users can choose the behavior they want to see. There has also been a long standing ticket asking for a similar behavior, like this: elastic/kibana#55378

@swg0101 got your point.

I think:

• the default behavior should be consistent
• we should expose the feature as an option of the KQL function

I opened an issue that describe exactly this: #135823.

That sounds great. Perhaps the result can be slightly more user-friendly, especially if there are no other variables that would need to be passed. Perhaps naming the function KQLi/kqli with and passing the case_insensitive: true parameter would be easier?

Just like using the DSL like this would be cumbersome:

{
  "kql": {
    "query": "user.name: student01",
    "case_insensitive": true
  }
}

Perhaps adding a toggle in Classic Discover would make it more friendly, although it got closed as not planned under: elastic/kibana#55378. Also, out of scope of this ticket... :)

@swg0101 There are probably some improvements that can be done in the UX but I am very reluctant to introduce new functions for things that can accessible through an optional parameter.

My plan for KQL function is to expose the case_insensitive but also to introduce other params that are supported by the query DSL:

• default field: list of field to use when no field is specified
• timezone: the timezone to use for date / time queries

Last but mot least, our plan with the KQL query is to allow users to migrate to ES|QL more easily. We do not want to add new features to the KQL language and would prefer that people rewrite their queries using native ES|QL syntax / functions.
If there are gaps, it would be better that we fix it in ES|QL directly instead of calling KQL to the rescue and call a query language inside a query language. My gut feeling is that we won't add new features to KQL once the above named params are implemented.

That makes sense. I am guessing that as long as autocomplete works well, then it probably wouldn't be that much of a problem.
To your point about migrating from KQL --> ES|QL, I think the top few things that drag me back into KQL are as follows:

1. Native ES|QL is still relatively weak when dealing with array values. For example, seeing if a particular field, like related.ip contains a value, regardless if it's in a scalar form or a multivalue (array) form. KQL couldn't care less about what type the field is in, and works like a "generic contains" match, versus ES|QL being much more literal in its matching (e.g., == vs : in KQL). This specific limitation also makes matching IPs difficult, such as something like related.ip: 192.168.0.0/16, where related.ip could contain either one IP or multiple IPs, depending on the document(s) being searched.

2. The absolute imposition of a strict limit with no pagination support, as described in ESQL: Support pagination #100000.

3. I am guessing this very discussion about case insensitivity would be one as well, as == is strictly case sensitive, and there is no option to change this on the fly without falling back to KQL().