[ES|QL] The behavior of COUNT is inconsistent

[abdonpijpelink]

The COUNT function behaves differently, depending on the source of the data:

• For data coming from ROW, it seems to count values.
• For data coming from FROM, it seems to count non-null rows.

If this is the expected behavior, it should be documented.

This example returns 2:

POST _query?format=txt
{
  "query": """
ROW my_field = ["foo", "bar"]
| STATS COUNT(my_field)
  """
}

But this example returns 1:

PUT test
{
  "mappings": {
    "properties": {
      "my_field": {
        "type": "keyword"
      }
    }
  }
}

PUT test/_doc/1
{
  "my_field": ["foo", "bar"]
}

POST _query?format=txt
{
  "query": """
FROM test
| STATS COUNT(my_field)
  """
}

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[costin]

Good find. This is caused by the SearchStats count implementation which relies on getDocCount

[costin]

To clarify, COUNT on a field should count the non-null values (not rows or documents) whether the data comes from Elasticsearch or from row.
COUNT(*) or COUNT(1) counts the number of documents/rows as it doesn't count just one field.
See https://github.com/elastic/elasticsearch/pull/104891/files#diff-8bd88b8bda18fb65ce077ef2b06513dcf4c24f15a31a3a97552520822cf6d0ec as an example.