---
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:
      indices.create:
        index: test-lookup-color-match
        body:
          settings:
            index:
              mode: lookup
          mappings:
            properties:
              key:
                type: long
              color:
                type: keyword
              shade:
                type: keyword
  - do:
      indices.create:
        index: test2
        body:
          mappings:
            properties:
              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" }
  - do:
      bulk:
        index: "test-lookup-color-match"
        refresh: true
        body:
          - { "index": { } }
          - { "key": 10, "color": "red" , shade: "light" }
          - { "index": { } }
          - { "key": 10, "color": "red" , shade: "reddish" }
          - { "index": { } }
          - { "key": 20, "color": "blue" , shade: "dark" }
          - { "index": { } }
          - { "key": 30, "color": "pink" , shade: "hot" }
  - do:
      bulk:
        index: "test2"
        refresh: true
        body:
          - { "index": { } }
          - { "key": 10, "color": "red" }
          - { "index": { } }
          - { "key": 20, "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 v2:
  - requires:
      capabilities:
        - method: POST
          path: /_query
          parameters: [ ]
          capabilities: [ enable_lookup_join_on_remote ]
      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: "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" }
---
lookup-no-key-only-key:
  - requires:
      capabilities:
        - method: POST
          path: /_query
          parameters: [ ]
          capabilities: [ enable_lookup_join_on_remote ]
      reason: "recent lookup join fix"
  - do:
      esql.query:
          body:
            query: 'FROM test | LOOKUP JOIN test-lookup-no-key ON key | KEEP key'
      catch: "bad_request"

  - match: { error.type: "verification_exception" }
  - contains: { error.reason: "Unknown column [key] in right side of join" }
---
basic join on two fields:
  - requires:
      capabilities:
        - method: POST
          path: /_query
          parameters: [ ]
          capabilities: [ lookup_join_on_multiple_fields ]
      reason: "uses LOOKUP JOIN on two fields"
  - do:
      esql.query:
        body:
          query: 'FROM test2 | LOOKUP JOIN test-lookup-color-match ON key, color | KEEP key, color, shade | SORT key, color, shade | LIMIT 10'
  - length: { values: 3 }
  - match: { columns.0.name: "key" }
  - match: { columns.0.type: "long" }
  - match: { columns.1.name: "color" }
  - match: { columns.1.type: "keyword" }
  - match: { columns.2.name: "shade" }
  - match: { columns.2.type: "keyword" }
    #for 10 and red 2 rows match
  - match: { values.0: [ 10, "red", "light" ] }
  - match: { values.1: [ 10, "red", "reddish" ] }
    #for 20 and yellow, no rows match, but we keep the row as it is a lookup join
  - match: { values.2: [ 20, "yellow", null ] }

