ElasticSearch Sql aggregation query bugs with SUM function

Question

Sql query is a very fantastic tool offered by EL 7.3. However i cant explain that bug :

When i use SUM with Match , 1 condition, query below works well

POST _xpack/sql?format=txt
{
"query":"SELECT SUM(FlightTimeHour) Avg_Flight_Time FROM flights where MATCH(OriginCountry,'AE') "
}

But if i use 2 or more conditions, SUM and MATCH query below fails :

POST _xpack/sql?format=txt
{
"query":"SELECT SUM(FlightTimeHour) Avg_Flight_Time FROM flights where MATCH(OriginCountry,'AE') OR MATCH(OriginCountry,'FR') "
}

Can someone tell me what is wrong ?

Answer 1

SQL documentation mentions nothing about boolean operators on MATCH. I don't think it is supported.

You can perform above search as below 1. using query instead of Match for full text search

"query": "SELECT SUM(FlightTimeHour) Avg_Flight_Time FROM flights where  QUERY('OriginCountry: AE OR OriginCountry: FR')"

2. Perform search on keywords

"query": "SELECT SUM(FlightTimeHour) Avg_Flight_Time FROM flights where OriginCountry.keyword='AE' OR OriginCountry.keyword='FR'"

EDIT: Above query as elastic search

GET fonds/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "query": "bordeaux",
            "fields": [
              "IDEE",
              "SLOGAN",
              "NOM",
              "ADRESSE",
              "VILLE",
              "ACTIVITE1",
              "ACTIVITE2",
              "KEYWORDS",
              "KEYWORDS_SITE",
              "SITE_H1_H6",
              "DESCRIPTION",
              "DESCRIPTION_SITE",
              "ACTIVITE3",
              "BUSINESS_MODEL",
              "COLORS"
            ]
          }
        },
        {
          "range": {
            "FONDS_LEVEES_TOTAL": {
              "gt": 0
            }
          } 
        },
        {
          "script": {
            "script": "doc['COMPE_RESULTAT_CA_2000'].value + doc['COMPE_RESULTAT_CA_2001'].value>0"
          }
        }
      ]
    }
  },
  "aggs": {
    "SUM": {
      "sum": {
        "field": "FONDS_LEVEES_TOTAL"
      }
    }
  }
}