Aggregations with basic algebraic operations are not supported.

[chloe-zh]

Aggregations combined with basic algebraic operations failed, including cases like:
MAX(a + const) ; MAX(a + b); MAX(a) + const; MAX(a) + MAX(b); etc.

Below is an example of a SQL query against AES instance v7.1.1.
Example indexed document

{
    "_index": "production.asset-metrics.history",
    "_type": "_doc",
    "_id": "EF602B6FF7E2956B0E627EF5901FFC08A9277556",
    "_score": 1.0,
    "_source": {
        "location": {
            "lat": 52.88041,
            "lon": -1.704096
        },
        "date": "2019-07-29T21:21:23Z",
        "organisation_id": "5c76863de13a541c18c3d963",
        "equipment_id": "5d3ebd4e2cf0ae28ea420604",
        "group_id": "5d3ebd4e2cf0ae28ea420627",
        "product_group_code": "500",
        "oem": "JCB",
        "model": "531-70",
        "latitude": 52.88041,
        "longitude": -1.704096,
        "altitude_in_metres": 81.875,
        "time_operating_in_hours": 110.0,
        "time_idle_in_hours": 33.1552777777778,
        "fuel_used_in_litres": 514,
        "fuel_remaining_percent": 98.0
    }
}

Example query

POST <host>/_opendistro/_sql
{
    "query": "SELECT equipment_id, (MAX(time_operating_in_hours) - MIN(time_operating_in_hours)) FROM development-asset-metrics-history GROUP BY equipment_id"
}

Example Response

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "",
    "type": "NullPointerException"
  },
  "status": 400
}

[chloe-zh]

Currently have obtained the expected DSL for this case.

[penghuo]

closed with commit.3730ac6