SQL: using a normalized field in a GROUP BY throws an unexpected error

[astefan]

This is valid for ES 6.4.2.

ES 6.5 and 7.x don't have this behavior, but there were changes in the way indices mappings are picked up and combined together as part of #34718.

Normalized fields shouldn't be allowed for groupings (aggregations), sorting and comparisons and a proper error message should be issued when this happens. But, a different error message is posted:

{
  "error": {
    "root_cause": [
      {
        "type": "query_shard_exception",
        "reason": "failed to find field [user.user] and [missing_bucket] is not set",
        "index_uuid": "6Wr_vO16TGahy3WhBQW_xg",
        "index": "test"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "test",
        "node": "qLp6GtalRIWMGkm2608rng",
        "reason": {
          "type": "query_shard_exception",
          "reason": "failed to find field [user.user] and [missing_bucket] is not set",
          "index_uuid": "6Wr_vO16TGahy3WhBQW_xg",
          "index": "test"
        }
      }
    ]
  },
  "status": 400
}

Test data:

DELETE /test
PUT /test
{
  "settings": {
    "analysis": {
      "normalizer": {
        "my_normalizer": {
          "type": "custom",
          "filter": [
            "lowercase",
            "asciifolding"
          ]
        }
      }
    }
  },
  "mappings": {
    "test": {
      "properties": {
        "user": {
          "type": "keyword",
          "normalizer": "my_normalizer"
        }
      }
    }
  }
}

POST /_xpack/sql
{
  "query": "select user from test group by user"
}

[elasticmachine]

Pinging @elastic/es-search-aggs

[jimczi]

Normalized fields shouldn't be allowed for groupings (aggregations), sorting and comparisons and a proper error message should be issued when this happens.

Why ? It's a keyword field so it should work even if a normalizer is set. The only difference is that the values in the doc_values are normalized.

The error message failed to find field [user.user] and [missing_bucket] is not set means that user.user does not exist in the mapping of the targeted index. I wonder why missing_bucket is not set though, null should be a valid group in SQL, right ?

[astefan]

@jimczi A normalized field has its value potentially changed (simplest example - lowercasing) and doing groupings (aggregations) will create buckets for the terms themselves and return those. SQL, on the other hand, is about exact values (the ones user used to index the document with) and getting back normalized values will break this rule.

[matriv]

@elastic/es-ql

[matriv]

Closing as for 7.x is not an issue:

Inserted 3 docs:

PUT test/_doc/1
{
  "user": "BÀR"
}

PUT test/_doc/2
{
  "user": "bar"
}

PUT test/_doc/3
{
  "user": "baz"
}

and the query returns:

      user   
---------------
bar            
baz