[BUG] Comparison operators only compare the first element for arrays against a scalar value

[normanj-bitquill]

What is the bug?
The comparison operators (=, !=, >, >=, <, <=) do not properly compare arrays. The comparison is performed on each element against a scalar value. If any element passes the comparison, then the whole array is considered to pass the comparison.

Consider an index with the following data:

{"x": 1, "y": [1, 2]}
{"x": 2, "y": [3, 4]}
{"x": 3, "y": [1, 5]}
{"x": 4, "y": [1, 2]}
{"x": 5, "y": [2, 3]}

and this query:

SELECT x, y FROM test3 WHERE y > 3;
{"x": 2, "y": [3, 4]}
{"x": 3, "y": [1, 5]}

Expected result:
Throw an exception since the types for the comparison are different. If both sides are arrays, then perform the comparison element by element until there is a difference.

How can one reproduce the bug?
Steps to reproduce the behavior:

1. Create a new index and load the data above
2. Run the query above

What is the expected behavior?
Throw an exception if the comparison is between an array and a scalar value. If the comparison is between two arrays, perform the comparison element by element.

What is your host/environment?

• OS: Mac OS X (shouldn't matter)
• Version: 3.0 code base
• Plugins: SQL plugin

Do you have any screenshots?
N/A

Do you have any additional context?
Issue #1300 had a change recently merged in that allows array values to be used in query evaluation and in the result set.

[normanj-bitquill]

Related to this, there should be a way of providing an array literal for comparisons in the OpenSearch engine.

POST test3/_search
{
  "query": {
    "range": {
      "y": {
        "gt": [1,2]
      }
    }
  }
}

{
  "error": {
    "root_cause": [
      {
        "type": "query_shard_exception",
        "reason": "failed to create query: For input string: \"]\"",
        "index": "test3",
        "index_uuid": "7X1qRsNZSIel9ivGfqxEDg"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "test3",
        "node": "pCTODN1sTfy2wK_F6DuUog",
        "reason": {
          "type": "query_shard_exception",
          "reason": "failed to create query: For input string: \"]\"",
          "index": "test3",
          "index_uuid": "7X1qRsNZSIel9ivGfqxEDg",
          "caused_by": {
            "type": "number_format_exception",
            "reason": "For input string: \"]\""
          }
        }
      }
    ]
  },
  "status": 400
}

[andrross]

[Catch All Triage - 1, 2, 3, 4, 5]