What is the bug?

The new engine does not return values in an array while the Legacy engine returns all values in a row as an array. Implementing same support as V1 does isn't a right way, because legacy engine produces inconsistent value.

How can one reproduce the bug?

Steps to reproduce the behavior:

1. Start OpenSearch server
2. Clean index if was created before: curl -XDELETE 'http://localhost:9200/dbg'
3. Create a simple index with automatic mapping: curl -X POST "localhost:9200/dbg/_doc/?pretty" -H 'Content-Type: application/json' -d '{"myNum": 5}'
4. Query data: select * from dbg. Not bas so far.
5. Add new doc: curl -X POST "localhost:9200/dbg/_doc/?pretty" -H 'Content-Type: application/json' -d '{"myNum": [3, 4]}'
6. Check mapping: curl -X GET "localhost:9200/dbg?pretty"

"mappings" : {
  "properties" : {
	"myNum" : {
	  "type" : "long"
	}
  }
}

7. Query in the new engine: curl -s -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query": "select * from dbg"}'

{
  "schema": [
    {
      "name": "myNum",
      "type": "long"
    }
  ],
  "datarows": [
    [
      5
    ],
    [
      3
    ]
  ],
  "total": 2,
  "size": 2,
  "status": 200
}

(if you have only second doc in the index)

"schema": [
    {
      "name": "myNum",
      "type": "long"
    }
  ],
  "datarows": [
    [
      3
    ],
    [
      4
    ]
  ]

8. Query in the legacy engine: curl -s -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query": "select * from dbg", "fetch_size": 20}'

{
  "schema": [
    {
      "name": "myNum",
      "type": "long"
    }
  ],
  "total": 2,
  "datarows": [
    [
      5
    ],
    [
      [
        3,
        4
      ]
    ]
  ],
  "size": 2,
  "status": 200
}

What is the expected behavior?

TBD

Why legacy response is incorrect?

It declares data type as long, but returns a number and array of numbers. Imagine a user has a parser for response, what should parser do with such values?
You can try our JDBC driver as an example of a customer application.

What is your host/environment?

main @ 6108ca1