---
setup:
  - requires:
      test_runner_features: [capabilities, contains, allowed_warnings]
      capabilities:
        - method: POST
          path: /_query
          parameters: []
          capabilities: [join_lookup_v12]
      reason: "uses LOOKUP JOIN"
  - do:
      indices.create:
        index: test
        body:
          mappings:
            properties:
              key:
                type: long
              color:
                type: keyword
  - do:
      indices.create:
        index: test-mv
        body:
          mappings:
            properties:
              key:
                type: long
              color:
                type: keyword
  - do:
      indices.create:
        index: test-lookup-1
        body:
          settings:
            index:
              mode: lookup
          mappings:
            properties:
              key:
                type: long
              color:
                type: keyword
  - do:
      indices.create:
        index: test-lookup-2
        body:
          settings:
            index:
              mode: lookup
          mappings:
            properties:
              key:
                type: long
              color:
                type: keyword
  - do:
      indices.create:
        index: test-lookup-mv
        body:
          settings:
            index:
              mode: lookup
          mappings:
            properties:
              key:
                type: long
              color:
                type: keyword
  - do:
      indices.create:
        index: test-lookup-no-key
        body:
          settings:
            index:
              mode: lookup
          mappings:
            properties:
              no-key:
                type: long
              color:
                type: keyword
  - do:
      bulk:
        index: "test"
        refresh: true
        body:
          - { "index": { } }
          - { "key": 1, "color": "red" }
          - { "index": { } }
          - { "key": 2, "color": "blue" }
  - do:
      bulk:
        index: "test-lookup-1"
        refresh: true
        body:
          - { "index": { } }
          - { "key": 1, "color": "cyan" }
          - { "index": { } }
          - { "key": 2, "color": "yellow" }
  - do:
      bulk:
        index: "test-mv"
        refresh: true
        body:
          - { "index": { } }
          - { "key": 1, "color": "red" }
          - { "index": { } }
          - { "key": 2, "color": "blue" }
          - { "index": { } }
          - { "key": [0, 1, 2], "color": null }
  - do:
      bulk:
        index: "test-lookup-mv"
        refresh: true
        body:
          - { "index": { } }
          - { "key": 1, "color": "cyan" }
          - { "index": { } }
          - { "key": 2, "color": "yellow" }
          - { "index": { } }
          - { "key": [0, 1, 2], "color": "green" }
  - do:
      bulk:
        index: "test-lookup-no-key"
        refresh: true
        body:
          - { "index": { } }
          - { "no-key": 1, "color": "cyan" }
          - { "index": { } }
          - { "no-key": 2, "color": "yellow" }

---
basic:
  - do:
      esql.query:
        body:
          query: 'FROM test | SORT key | LOOKUP JOIN test-lookup-1 ON key | LIMIT 3'

  - match: {columns.0.name: "key"}
  - match: {columns.0.type: "long"}
  - match: {columns.1.name: "color"}
  - match: {columns.1.type: "keyword"}
  - match: {values.0: [1, "cyan"]}
  - match: {values.1: [2, "yellow"]}

---
fails with non-lookup index:
  - requires:
      capabilities:
        - method: POST
          path: /_query
          parameters: []
          capabilities: [update_lookup_join_error_messages]
      reason: "checks updated error messages"
  - do:
      esql.query:
        body:
          query: 'FROM test-lookup-1 | SORT key | LOOKUP JOIN test ON key | LIMIT 3'
      catch: "bad_request"

  - match: { error.type: "verification_exception" }
  - contains: { error.reason: "Found 1 problem\nline 1:45: Lookup Join requires a single lookup mode index; [test] resolves to [test] in [standard] mode" }

---
pattern-multiple:
  - do:
      esql.query:
        body:
          query: 'FROM test-lookup-1 | LOOKUP JOIN test-lookup-* ON key'
      catch: "bad_request"

  - match: { error.type: "parsing_exception" }
  - contains: { error.reason: "line 1:34: invalid index pattern [test-lookup-*], * is not allowed in LOOKUP JOIN" }

---
pattern-single:
  - do:
      esql.query:
        body:
          query: 'FROM test | SORT key | LOOKUP JOIN test-lookup-1* ON key | LIMIT 3'
      catch: "bad_request"

  - match: { error.type: "parsing_exception" }
  - contains: { error.reason: "line 1:36: invalid index pattern [test-lookup-1*], * is not allowed in LOOKUP JOIN" }

---
mv-on-lookup:
  - do:
      esql.query:
        body:
          query: 'FROM test | SORT key | LOOKUP JOIN test-lookup-mv ON key'
      allowed_warnings:
        - "No limit defined, adding default limit of [1000]"
        - "Line 1:24: evaluation of [LOOKUP JOIN test-lookup-mv ON key] failed, treating result as null. Only first 20 failures recorded."
        - "Line 1:24: java.lang.IllegalArgumentException: LOOKUP JOIN encountered multi-value"

  - match: {columns.0.name: "key"}
  - match: {columns.0.type: "long"}
  - match: {columns.1.name: "color"}
  - match: {columns.1.type: "keyword"}
  - match: {values.0: [1, "cyan"]}
  - match: {values.1: [2, "yellow"]}

---
mv-on-query:
  - do:
      esql.query:
        body:
          query: 'FROM test-mv | SORT key | LOOKUP JOIN test-lookup-1 ON key | LIMIT 4'
      allowed_warnings:
        - "Line 1:27: evaluation of [LOOKUP JOIN test-lookup-1 ON key] failed, treating result as null. Only first 20 failures recorded."
        - "Line 1:27: java.lang.IllegalArgumentException: LOOKUP JOIN encountered multi-value"

  - match: {columns.0.name: "key"}
  - match: {columns.0.type: "long"}
  - match: {columns.1.name: "color"}
  - match: {columns.1.type: "keyword"}
  - match: {values.0: [[0, 1, 2], null]}
  - match: {values.1: [1, "cyan"]}
  - match: {values.2: [2, "yellow"]}

---
lookup-no-key:
  - do:
      esql.query:
          body:
            query: 'FROM test | LOOKUP JOIN test-lookup-no-key ON key | KEEP key, color'
      catch: "bad_request"

  - match: { error.type: "verification_exception" }
  - contains: { error.reason: "Unknown column [key] in right side of join" }
