SQL: normalized keywords shouldn't be allowed for groupings and sorting #35203
Description
A normalized keyword field can potentially change the original value of a field, but SQL works with exact values. Probably as part of #34718, the restriction was lifted, as field_caps API can tell if a field is aggregatable/searchable, but not if it's normalized or a simple keyword.
As such, in the following scenario:
{
"settings": {
"analysis": {
"normalizer": {
"my_normalizer": {
"type": "custom",
"filter": [
"lowercase",
"asciifolding"
]
}
}
}
},
"mappings": {
"test": {
"properties": {
"user": {
"type": "text",
"fields": {
"normalized": {
"type": "keyword",
"normalizer": "my_normalizer"
},
"keyword": {
"type": "keyword"
}
}
},
"user2": {
"type": "keyword",
"normalizer": "my_normalizer"
}
}
}
}
}
With a query like { "query" : "select user from test group by user" } one gets back:
{
"error": {
"root_cause": [
{
"type": "mapping_exception",
"reason": "Multiple exact keyword candidates available for [user]; specify which one to use"
}
],
"type": "mapping_exception",
"reason": "Multiple exact keyword candidates available for [user]; specify which one to use"
},
"status": 400
}
And for { "query" : "select user2 from test group by user2" } there are results returned where, in fact, ES SQL should return an error.