---
setup:
  - do:
      bulk:
        refresh: true
        body:
          - index:
              _index: test
              _id:    "1"
          - str: test1
            int: 1
          - index:
              _index: test
              _id:    "2"
          - str: test2
            int: 2
          - index:
              _index: test
              _id:    "3"
          - str: test3
            int: 3

---
"Execute some SQL":
  - do:
      sql.query:
        format: json
        body:
          query: "SELECT * FROM test ORDER BY int asc"
  - match: { columns.0.name: int }
  - match: { columns.1.name: str }
  - match: { rows.0.0: 1 }
  - match: { rows.0.1: test1 }
  - match: { rows.1.0: 2 }
  - match: { rows.1.1: test2 }
  - match: { rows.2.0: 3 }
  - match: { rows.2.1: test3 }

---
"Paging through results":
  - skip:
      cluster_features: ["gte_v8.1.99"]
      reason: PIT cursors introduced in 8.2 no longer return empty last page
  - do:
      sql.query:
        format: json
        body:
          query: "SELECT * FROM test ORDER BY int asc"
          fetch_size: 2
  - match: { columns.0.name: int }
  - match: { columns.1.name: str }
  - match: { rows.0.0: 1 }
  - match: { rows.0.1: test1 }
  - match: { rows.1.0: 2 }
  - match: { rows.1.1: test2 }
  - is_true: cursor
  - set: { cursor: cursor }

  - do:
      sql.query:
        format: json
        body:
          cursor: "$cursor"
  - match: { rows.0.0: 3 }
  - match: { rows.0.1: test3 }
  - is_false: columns
  - is_true: cursor
  - set: { cursor: cursor }

  - do:
      sql.query:
        format: json
        body:
          cursor: "$cursor"
  - is_false: columns
  - is_false: cursor
  - length: { rows: 0 }

---
"Paging through results with PIT cursor":
  - requires:
      cluster_features: ["gte_v8.2.0"]
      reason: PIT cursors introduced in 8.2 no longer return empty last page
  - do:
      sql.query:
        format: json
        body:
          query: "SELECT * FROM test ORDER BY int asc"
          fetch_size: 2
  - match: { columns.0.name: int }
  - match: { columns.1.name: str }
  - match: { rows.0.0: 1 }
  - match: { rows.0.1: test1 }
  - match: { rows.1.0: 2 }
  - match: { rows.1.1: test2 }
  - is_true: cursor
  - set: { cursor: cursor }

  - do:
      sql.query:
        format: json
        body:
          cursor: "$cursor"
  - match: { rows.0.0: 3 }
  - match: { rows.0.1: test3 }
  - is_false: columns
  - is_false: cursor

  - do:
      indices.stats: { index: 'test' }

  - match: { indices.test.total.search.open_contexts: 0 }

---
"Getting textual representation":
  - do:
      sql.query:
        format: txt
        body:
          query: "SELECT * FROM test ORDER BY int asc"
  - match:
      $body: |
               /^  \s+   int \s+  \| \s+  str \s+  \n
                   ---------------\+---------------\n
                   1      \s+     \|test1   \s+    \n
                   2      \s+     \|test2   \s+    \n
                   3      \s+     \|test3   \s+    \n
               $/

---
"Clean cursor":
  - do:
      sql.query:
        format: json
        body:
          query: "SELECT * FROM test ORDER BY int asc"
          fetch_size: 2
  - match: { columns.0.name: int }
  - match: { columns.1.name: str }
  - match: { rows.0.0: 1 }
  - match: { rows.0.1: test1 }
  - is_true: cursor
  - set: { cursor: cursor}

  - do:
      sql.clear_cursor:
        body:
          cursor: "$cursor"
  - match: { "succeeded": true }

  - do:
      indices.stats: { index: 'test' }

  - match: { indices.test.total.search.open_contexts: 0 }
