---
setup:
  - requires:
      cluster_features: ["gte_v8.11.0"]
      reason: "ESQL is available in 8.11+"
      test_runner_features: allowed_warnings_regex
  - do:
      indices.create:
        index:  test
        body:
          settings:
            number_of_shards: 5
          mappings:
            properties:
              data:
                type: long
              data_d:
                type: double
              count:
                type: long
              count_d:
                type: double
              time:
                type: long
              color:
                type: keyword
              text:
                type: text
  - do:
      bulk:
        index: "test"
        refresh: true
        body:
          - { "index": { } }
          - { "data": 1, "count": 40, "data_d": 1, "count_d": 40, "time": 1674835275187, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 2, "count": 42, "data_d": 2, "count_d": 42, "time": 1674835275188, "color": "blue", "text": "bb blue" }
          - { "index": { } }
          - { "data": 1, "count": 44, "data_d": 1, "count_d": 44, "time": 1674835275189, "color": "green", "text": "gg green" }
          - { "index": { } }
          - { "data": 2, "count": 46, "data_d": 2, "count_d": 46, "time": 1674835275190, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 1, "count": 40, "data_d": 1, "count_d": 40, "time": 1674835275191, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 2, "count": 42, "data_d": 2, "count_d": 42, "time": 1674835275192, "color": "blue", "text": "bb blue" }
          - { "index": { } }
          - { "data": 1, "count": 44, "data_d": 1, "count_d": 44, "time": 1674835275193, "color": "green", "text": "gg green" }
          - { "index": { } }
          - { "data": 2, "count": 46, "data_d": 2, "count_d": 46, "time": 1674835275194, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 1, "count": 40, "data_d": 1, "count_d": 40, "time": 1674835275195, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 2, "count": 42, "data_d": 2, "count_d": 42, "time": 1674835275196, "color": "blue", "text": "bb blue" }
          - { "index": { } }
          - { "data": 1, "count": 44, "data_d": 1, "count_d": 44, "time": 1674835275197, "color": "green", "text": "gg green" }
          - { "index": { } }
          - { "data": 2, "count": 46, "data_d": 2, "count_d": 46, "time": 1674835275198, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 1, "count": 40, "data_d": 1, "count_d": 40, "time": 1674835275199, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 2, "count": 42, "data_d": 2, "count_d": 42, "time": 1674835275200, "color": "blue", "text": "bb blue" }
          - { "index": { } }
          - { "data": 1, "count": 44, "data_d": 1, "count_d": 44, "time": 1674835275201, "color": "green", "text": "gg green" }
          - { "index": { } }
          - { "data": 2, "count": 46, "data_d": 2, "count_d": 46, "time": 1674835275202, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 1, "count": 40, "data_d": 1, "count_d": 40, "time": 1674835275203, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 2, "count": 42, "data_d": 2, "count_d": 42, "time": 1674835275204, "color": "blue", "text": "bb blue" }
          - { "index": { } }
          - { "data": 1, "count": 44, "data_d": 1, "count_d": 44, "time": 1674835275205, "color": "green", "text": "gg green" }
          - { "index": { } }
          - { "data": 2, "count": 46, "data_d": 2, "count_d": 46, "time": 1674835275206, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 1, "count": 40, "data_d": 1, "count_d": 40, "time": 1674835275207, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 2, "count": 42, "data_d": 2, "count_d": 42, "time": 1674835275208, "color": "blue", "text": "bb blue" }
          - { "index": { } }
          - { "data": 1, "count": 44, "data_d": 1, "count_d": 44, "time": 1674835275209, "color": "green", "text": "gg green" }
          - { "index": { } }
          - { "data": 2, "count": 46, "data_d": 2, "count_d": 46, "time": 1674835275210, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 1, "count": 40, "data_d": 1, "count_d": 40, "time": 1674835275211, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 2, "count": 42, "data_d": 2, "count_d": 42, "time": 1674835275212, "color": "blue", "text": "bb blue" }
          - { "index": { } }
          - { "data": 1, "count": 44, "data_d": 1, "count_d": 44, "time": 1674835275213, "color": "green", "text": "gg green" }
          - { "index": { } }
          - { "data": 2, "count": 46, "data_d": 2, "count_d": 46, "time": 1674835275214, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 1, "count": 40, "data_d": 1, "count_d": 40, "time": 1674835275215, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 2, "count": 42, "data_d": 2, "count_d": 42, "time": 1674835275216, "color": "blue", "text": "bb blue" }
          - { "index": { } }
          - { "data": 1, "count": 44, "data_d": 1, "count_d": 44, "time": 1674835275217, "color": "green", "text": "gg green" }
          - { "index": { } }
          - { "data": 2, "count": 46, "data_d": 2, "count_d": 46, "time": 1674835275218, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 1, "count": 40, "data_d": 1, "count_d": 40, "time": 1674835275219, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 2, "count": 42, "data_d": 2, "count_d": 42, "time": 1674835275220, "color": "blue", "text": "bb blue" }
          - { "index": { } }
          - { "data": 1, "count": 44, "data_d": 1, "count_d": 44, "time": 1674835275221, "color": "green", "text": "gg green" }
          - { "index": { } }
          - { "data": 2, "count": 46, "data_d": 2, "count_d": 46, "time": 1674835275222, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 1, "count": 40, "data_d": 1, "count_d": 40, "time": 1674835275223, "color": "red", "text": "rr red" }
          - { "index": { } }
          - { "data": 2, "count": 42, "data_d": 2, "count_d": 42, "time": 1674835275224, "color": "blue", "text": "bb blue" }
          - { "index": { } }
          - { "data": 1, "count": 44, "data_d": 1, "count_d": 44, "time": 1674835275225, "color": "green", "text": "gg green" }
          - { "index": { } }
          - { "data": 2, "count": 46, "data_d": 2, "count_d": 46, "time": 1674835275226, "color": "red", "text": "rr red" }

---
"Test From":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test'

  - match: {columns.0.name: "color"}
  - match: {columns.0.type: "keyword"}
  - match: {columns.1.name: "count"}
  - match: {columns.1.type: "long"}
  - match: {columns.2.name: "count_d"}
  - match: {columns.2.type: "double"}
  - match: {columns.3.name: "data"}
  - match: {columns.3.type: "long"}
  - match: {columns.4.name: "data_d"}
  - match: {columns.4.type: "double"}
  - match: {columns.5.name: "text"}
  - match: {columns.5.type: "text"}
  - match: {columns.6.name: "time"}
  - match: {columns.6.type: "long"}
  - length: {values: 40}

---
"Test simple grouping avg":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | where color == "red" | stats avg(data) by color'
          columnar: true

  - match: {columns.0.name: "avg(data)"}
  - match: {columns.0.type: "double"}
  - match: {columns.1.name: "color"}
  - match: {columns.1.type: "keyword"}
  - match: {values.0.0: 1.5} # ( 1 + 2 / 2 = 1.5 )

---
"Test From Stats Avg":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats avg(count)'
          columnar: true

  - match: {columns.0.name: "avg(count)"}
  - match: {columns.0.type: "double"}
  - match: {values.0.0: 43}

---
"Test From Stats Avg With Alias":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats f1 = avg(count)'
          columnar: true

  - match: {columns.0.name: "f1"}
  - match: {columns.0.type: "double"}
  - match: {values.0.0: 43}

---
"Test From Stats Count":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats count(data)'
          columnar: true

  - match: {columns.0.name: "count(data)"}
  - match: {columns.0.type: "long"}
  - match: {values.0.0: 40}

---
"Test From Stats Count With Alias":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats dataCount = count(data)'
          columnar: true

  - match: {columns.0.name: "dataCount"}
  - match: {columns.0.type: "long"}
  - match: {values.0.0: 40}

---
"Test From Stats Min":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats min(count)'
          columnar: true

  - match: {columns.0.name: "min(count)"}
  - match: {columns.0.type: "long"}
  - match: {values.0.0: 40}

---
"Test From Stats Min With Alias":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats minCount=min(count)'
          columnar: true

  - match: {columns.0.name: "minCount"}
  - match: {columns.0.type: "long"}
  - match: {values.0.0: 40}

---
"Test From Stats Max":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats max(count)'
          columnar: true

  - match: {columns.0.name: "max(count)"}
  - match: {columns.0.type: "long"}
  - match: {values.0.0: 46}

---
"Test From Stats Max With Alias":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats maxCount=max(count)'
          columnar: true

  - match: {columns.0.name: "maxCount"}
  - match: {columns.0.type: "long"}
  - match: {values.0.0: 46}

---
"Test Sort With Keyword Field":
  - do:
      esql.query:
        body:
          query: 'from test | stats avg(count) by color | sort color | limit 2'
          columnar: true

  - match: {columns.0.name: "avg(count)"}
  - match: {columns.0.type: "double"}
  - match: {columns.1.name: "color"}
  - match: {columns.1.type: "keyword"}
  - match: {values.0: [42.0, 44.0]}
  - match: {values.1: ["blue", "green"]}

---
"Test Median On Long":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats med=median(count)'
          columnar: true

  - match: {columns.0.name: "med"}
  - match: {columns.0.type: "double"}
  - match: {values.0.0: 43.0}

---
"Test Median On Double":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats med=median(count_d)'
          columnar: true

  - match: {columns.0.name: "med"}
  - match: {columns.0.type: "double"}
  - match: {values.0.0: 43.0}

---
"Test Grouping Median On Long":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats med=median(count) by color | sort med'
          columnar: true

  - match: {columns.0.name: "med"}
  - match: {columns.0.type: "double"}
  - match: {columns.1.name: "color"}
  - match: {columns.1.type: "keyword"}
  - match: {values.0: [42.0, 43.0, 44.0]}
  - match: {values.1: ["blue", "red", "green"]}

---
"Test Grouping Median On Double":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats med=median(count_d) by color | sort med'
          columnar: true

  - match: {columns.0.name: "med"}
  - match: {columns.0.type: "double"}
  - match: {columns.1.name: "color"}
  - match: {columns.1.type: "keyword"}
  - match: {values.0: [42.0, 43.0, 44.0]}
  - match: {values.1: ["blue", "red", "green"]}

---
"Test Median Absolute Deviation On Long":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats med=median_absolute_deviation(count)'
          columnar: true

  - match: {columns.0.name: "med"}
  - match: {columns.0.type: "double"}
  - match: {values.0.0: 2.0}

---
"Test Median Absolute Deviation On Double":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats med=median_absolute_deviation(count_d)'
          columnar: true

  - match: {columns.0.name: "med"}
  - match: {columns.0.type: "double"}
  - match: {values.0.0: 2.0}

---
"Test Grouping Median Absolute Deviation On Long":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats med=median_absolute_deviation(count) by color | sort color'
          columnar: true

  - match: {columns.0.name: "med"}
  - match: {columns.0.type: "double"}
  - match: {columns.1.name: "color"}
  - match: {columns.1.type: "keyword"}
  - match: {values.0: [0.0, 0.0, 3.0]}
  - match: {values.1: ["blue", "green", "red"]}

---
"Test Grouping Median Absolute Deviation On Double":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats med=median_absolute_deviation(count_d) by color | sort color'
          columnar: true

  - match: {columns.0.name: "med"}
  - match: {columns.0.type: "double"}
  - match: {columns.1.name: "color"}
  - match: {columns.1.type: "keyword"}
  - match: {values.0: [0.0, 0.0, 3.0]}
  - match: {values.1: ["blue", "green", "red"]}

---
"Test From Stats Eval":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats avg_count = avg(count) | eval x = avg_count + 7'

  - length: {values: 1}
  - length: {values.0: 2}
  - match: {values.0.1: 50.0}

---
"Test Stats Where":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | stats x = avg(count) | where x > 100'

  - length: {values: 0}

---
"Test Eval With Null":
  - do:
      esql.query:
        body:
          query: 'from test | eval nullsum = count_d + null | sort nullsum | limit 1'

  - length: {columns: 8}
  - length: {values: 1}
  - match: {columns.7.name: "nullsum"}
  - match: {columns.7.type: "double"}
  - match: {values.0.7: null}

---
"Test Eval Row With Null":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'row a = 1, b = 2, c = null | eval z = c + b + a'

  - length: {columns: 4}
  - length: {values: 1}
  - length: {values.0: 4}
  - match: {columns.0.name: "a"}
  - match: {columns.0.type: "integer"}
  - match: {columns.1.name: "b"}
  - match: {columns.1.type: "integer"}
  - match: {columns.2.name: "c"}
  - match: {columns.2.type: "null"}
  - match: {columns.3.name: "z"}
  - match: {columns.3.type: "integer"}
  - match: {values.0.0: 1}
  - match: {values.0.1: 2}
  - match: {values.0.2: null}
  - match: {values.0.3: null}

---
"Test Eval With Null And Count":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'from test | eval nullsum = count_d + null | stats count(nullsum)'

  - length: {columns: 1}
  - length: {values: 1}
  - match: {columns.0.name: "count(nullsum)"}
  - match: {columns.0.type: "long"}
  - length: {values.0: 1}
  - match: {values.0.0: 0}

---
"Test Eval With Multiple Expressions":
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'row l=1, d=1.0, ln=1 + null, dn=1.0 + null | stats sum(l), sum(d), sum(ln), sum(dn)'

  - length: {columns: 4}
  - length: {values: 1}
  - length: {values.0: 4}
  - match: {columns.0.name: "sum(l)"}
  - match: {columns.0.type: "long"}
  - match: {columns.1.name: "sum(d)"}
  - match: {columns.1.type: "double"}
  - match: {columns.2.name: "sum(ln)"}
  - match: {columns.2.type: "long"}
  - match: {columns.3.name: "sum(dn)"}
  - match: {columns.3.type: "double"}
  - match: {values.0.0: 1}
  - match: {values.0.1: 1}
  - match: {values.0.2: null}
  - match: {values.0.3: null}

---
grouping on text:
  - do:
      allowed_warnings_regex:
        - "No limit defined, adding default limit of \\[.*\\]"
      esql.query:
        body:
          query: 'FROM test | STATS med=median(count) BY text | SORT med'
          columnar: true

  - match: {columns.0.name: "med"}
  - match: {columns.0.type: "double"}
  - match: {columns.1.name: "text"}
  - match: {columns.1.type: "text"}
  - match: {values.0: [42.0, 43.0, 44.0]}
  - match: {values.1: ["bb blue", "rr red", "gg green"]}
