[ES|QL] Aggregation on conversion functions may return wrong results

[fang-xing-esql]

It happens when the conversion functions reference multi/union typed fields that come from multiple indices, this happens to the other conversion functions too, not specific for ::date, ::date_nanos. Aggregation on single index with conversion functions work as expected.

Here are the steps to reproduce this issue.

curl -u elastic:password -H "Content-Type: application/json" "127.0.0.1:9200/test1" -XPUT -d '{
  "mappings": {
        "properties": {
          "millis": { "type": "date" },
          "nanos": { "type": "date_nanos"},
          "num": { "type": "long" }
        }
  }
}'

curl -u elastic:password -H "Content-Type: application/json" "127.0.0.1:9200/test2" -XPUT -d '{
  "settings": {"index": {"mode": "lookup"}},
  "mappings": {
        "properties": {
          "millis": { "type": "date_nanos" },
          "nanos": { "type": "date"},
          "num": { "type": "long" }
        }
  }
}'

curl -X PUT "localhost:9200/test1/_bulk?refresh&pretty" -H 'Content-Type: application/json' -d'
{"index": {}}
{"millis":"2023-10-23T13:55:01.543Z","nanos":"2023-10-23T13:55:01.543123456Z","num":1698069301543123456}
{"index": {}}
{"millis":"2023-10-23T13:55:01.543Z","nanos":"2023-10-23T12:55:01.543123456Z","num":1698069301543123456}
{"index": {}}
{"millis":"1999-10-23T12:15:03.360Z","nanos":["2023-03-23T12:15:03.360103847Z", "2023-02-23T13:33:34.937193000Z", "2023-01-23T13:55:01.543123456Z"],"num":0}
'

curl -X PUT "localhost:9200/test2/_bulk?refresh&pretty" -H 'Content-Type: application/json' -d'
{"index": {}}
{"millis":"2023-10-23T13:55:01.543123456Z","nanos":"2023-10-23T13:55:01.543Z","num":1698069301543123456}
{"index": {}}
{"millis":"1999-10-23T12:15:03.360103847Z","nanos":["2023-03-23T12:15:03.360Z", "2023-02-23T13:33:34.937Z", "2023-01-23T13:55:01.543Z"],"num":0}
'

+ curl -u elastic:password -X POST 'localhost:9200/_query?format=txt&pretty' -H 'Content-Type: application/json' '-d
{ 
  "query": "from test* | stats count(millis::date)"
}
'
count(millis::date)
-------------------
0                  ===> WRONG RESULTS
+ curl -u elastic:password -X POST 'localhost:9200/_query?format=txt&pretty' -H 'Content-Type: application/json' '-d
{ 
  "query": "from test* | stats count(millis::date_nanos)"
}
'
count(millis::date_nanos)
-------------------------
0                        ===> WRONG RESULTS
+ curl -u elastic:password -X POST 'localhost:9200/_query?format=txt&pretty' -H 'Content-Type: application/json' '-d
{
  "query": "from test1 | stats count(millis)"
}
'
 count(millis) 
---------------
3              
+ curl -u elastic:password -X POST 'localhost:9200/_query?format=txt&pretty' -H 'Content-Type: application/json' '-d
{
  "query": "from test1 | stats count(millis::date)"
}
'
count(millis::date)
-------------------
3                  
+ curl -u elastic:password -X POST 'localhost:9200/_query?format=txt&pretty' -H 'Content-Type: application/json' '-d
{
  "query": "from test1 | stats count(millis::date_nanos)"
}
'
count(millis::date_nanos)
-------------------------
3                        
+ curl -u elastic:password -X POST 'localhost:9200/_query?format=txt&pretty' -H 'Content-Type: application/json' '-d
{
  "query": "from test2 | stats count(millis)"
}
'
 count(millis) 
---------------
2              
+ curl -u elastic:password -X POST 'localhost:9200/_query?format=txt&pretty' -H 'Content-Type: application/json' '-d
{
  "query": "from test2 | stats count(millis::date)"
}
'
count(millis::date)
-------------------
2                  
+ curl -u elastic:password -X POST 'localhost:9200/_query?format=txt&pretty' -H 'Content-Type: application/json' '-d
{
  "query": "from test2 | stats count(millis::date_nanos)"
}
'
count(millis::date_nanos)
-------------------------
2   

[elasticsearchmachine]

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

[fang-xing-esql]

The EsStatsQueryExec seems wrong, with "field" : "$$millis$converted_to$datetime".

[DEBUG][o.e.x.e.o.LocalPhysicalPlanOptimizer] [runTask-0] Tree transformation took 1ms
AggregateExec[[],[COUNT($$millis$converted_to$datetime{f$}#110,true[BOOLEAN]) AS count(millis::date)],INITIAL,[$$count(millis:: ! EsStatsQueryExec[test*], stats[Stat[name=$$millis$converted_to$datetime, type=COUNT, query={
date)$count{r}#115, $$count(millis::date)$seen{r}#116],null]                                                                    !   "exists" : {
\_EsSourceExec[test*][!millis, $$millis$converted_to$datetime{f$}#110]                                                          !     "field" : "$$millis$converted_to$datetime",
                                                                                                                                !     "boost" : 1.0
                                                                                                                                !   }
                                                                                                                                ! }]]], query[][$$count(millis::date)$count{r}#118, $$count(millis:..], limit[],

Should we unwrap the conversion function in EsStatsQueryExec or avoid pushing down in this kind of situation?

[alex-spies]

Great find Fang!

I think the problem is that in PushStatsToSource, we use FieldAttribute.name instead of FieldAttribute.fieldName, right here.

I just checked and when using fieldName in the linked lines, the reproducer from above provides correct results.