---
setup:
- skip:
    features:
    - "headers"
    - "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"
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
- do:
    bulk:
      index: "test"
      refresh: true
      body:
      - index: {}
      - data: 1
        count: 40
        data_d: 1
        count_d: 40
        time: 1674835275187
        color: "red"
      - index: {}
      - data: 2
        count: 42
        data_d: 2
        count_d: 42
        time: 1674835275188
        color: "blue"
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
---
basic:
- requires:
    test_runner_features:
    - "capabilities"
    capabilities:
    - method: "POST"
      path: "/_query"
      parameters: []
      capabilities:
      - "tables_types"
    reason: "uses LOOKUP"
- do:
    esql.query:
      body:
        query: "FROM test | SORT time | KEEP color | LOOKUP colors ON color | LIMIT\
          \ 2"
        columnar: true
        tables:
          colors:
            color:
              keyword:
              - "red"
              - "green"
              - "blue"
            rgb:
              integer:
              - 16711680
              - 65280
              - 255
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
- match:
    columns.0.name: "color"
- match:
    columns.0.type: "keyword"
- match:
    columns.1.name: "rgb"
- match:
    columns.1.type: "integer"
- match:
    values.0:
    - "red"
    - "blue"
- match:
    values.1:
    - 16711680
    - 255
---
read multivalue keyword:
- requires:
    test_runner_features:
    - "capabilities"
    capabilities:
    - method: "POST"
      path: "/_query"
      parameters: []
      capabilities:
      - "tables_types"
    reason: "uses LOOKUP"
- do:
    esql.query:
      body:
        query: "FROM test | SORT time | KEEP color | LOOKUP color_associations ON\
          \ color | LIMIT 2"
        columnar: true
        tables:
          color_associations:
            color:
              keyword:
              - "red"
              - "green"
              - "blue"
            association:
              keyword:
              - - "love"
                - "passion"
                - "blood"
                - "happiness"
              - - "nature"
                - "healing"
                - "health"
                - "youth"
              - - "serenity"
                - "wisdom"
                - "ocean"
                - "sky"
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
- match:
    columns.0.name: "color"
- match:
    columns.0.type: "keyword"
- match:
    columns.1.name: "association"
- match:
    columns.1.type: "keyword"
- match:
    values.0:
    - "red"
    - "blue"
- match:
    values.1:
    - - "love"
      - "passion"
      - "blood"
      - "happiness"
    - - "serenity"
      - "wisdom"
      - "ocean"
      - "sky"
---
keyword matches text:
- requires:
    test_runner_features:
    - "capabilities"
    capabilities:
    - method: "POST"
      path: "/_query"
      parameters: []
      capabilities:
      - "tables_types"
    reason: "uses LOOKUP"
- do:
    indices.create:
      index: "test_text"
      body:
        settings:
          number_of_shards: 1
        mappings:
          properties:
            color:
              type: "text"
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
- do:
    bulk:
      index: "test_text"
      refresh: true
      body:
      - index: {}
      - color: "red"
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
- do:
    esql.query:
      body:
        query: "FROM test_text | LOOKUP colors ON color | LIMIT 1"
        columnar: true
        tables:
          colors:
            color:
              keyword:
              - "red"
              - "green"
              - "blue"
            rgb:
              integer:
              - 16711680
              - 65280
              - 255
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
- match:
    columns.0.name: "color"
- match:
    columns.0.type: "text"
- match:
    columns.1.name: "rgb"
- match:
    columns.1.type: "integer"
- match:
    values.0:
    - "red"
- match:
    values.1:
    - 16711680
---
duplicate keys:
- requires:
    test_runner_features:
    - "capabilities"
    capabilities:
    - method: "POST"
      path: "/_query"
      parameters: []
      capabilities:
      - "tables_types"
    reason: "uses LOOKUP"
- do:
    catch: "/found a duplicate row/"
    esql.query:
      body:
        query: "FROM test | LOOKUP colors ON color | SORT time | KEEP color, rgb |\
          \ LIMIT 2"
        columnar: true
        tables:
          colors:
            color:
              keyword:
              - "red"
              - "red"
              - "blue"
            rgb:
              integer:
              - 16711680
              - 65280
              - 255
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
---
multivalued keys:
- requires:
    test_runner_features:
    - "capabilities"
    capabilities:
    - method: "POST"
      path: "/_query"
      parameters: []
      capabilities:
      - "tables_types"
    reason: "uses LOOKUP"
- do:
    catch: "/only single valued keys are supported/"
    esql.query:
      body:
        query: "FROM test | LOOKUP colors ON color | SORT time | KEEP color, rgb |\
          \ LIMIT 2"
        columnar: true
        tables:
          colors:
            color:
              keyword:
              - - "red"
                - "blue"
              - "white"
              - "blue"
            rgb:
              integer:
              - 16711680
              - 65280
              - 255
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
---
index named lookup still works:
- do:
    bulk:
      index: "lookup"
      refresh: true
      body:
      - index: {}
      - f: 1
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
- do:
    esql.query:
      body:
        query: "FROM lookup | LIMIT 1"
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
- match:
    columns.0.name: "f"
- match:
    columns.0.type: "long"
- length:
    values: 1
- match:
    values.0.0: 1
---
on function:
- requires:
    test_runner_features:
    - "capabilities"
    capabilities:
    - method: "POST"
      path: "/_query"
      parameters: []
      capabilities:
      - "tables_types"
    reason: "uses LOOKUP"
- do:
    catch: "/token recognition error at: '\\('/"
    esql.query:
      body:
        query: "FROM test | SORT time | KEEP color | LOOKUP colors ON CONCAT(color,\
          \ \"foo\") | LIMIT 2"
        columnar: true
        tables:
          colors:
            color:
              keyword:
              - "red"
              - "green"
              - "blue"
            rgb:
              integer:
              - 16711680
              - 65280
              - 255
    headers:
      Content-Type: "application/vnd.elasticsearch+json;compatible-with=8"
      Accept: "application/vnd.elasticsearch+json;compatible-with=8"
    allowed_warnings_regex:
    - ".*rollup functionality will be removed in Elasticsearch.*"
    - "Index \\[\\.profiling-.*\\] name begins with a dot.* and will not be allowed\
      \ in a future Elasticsearch version."
    - "Parameter \\[default_metric\\] is deprecated and will be removed in a future\
      \ version"
