[BUG] Queries with a LIMIT clause and those without it return different results.

[penghuo]

What is the bug?
Queries with a LIMIT clause and those without it return different results.

How can one reproduce the bug?

• Test dataset, https://github.com/opensearch-project/sql/blob/main/integ-test/src/test/resources/correctness/opensearch_dashboards_sample_data_flights.json
• Test queries

###
POST {{baseUrl}}/_plugins/_sql/_explain
Content-Type: application/x-ndjson

{
  "query": "SELECT Origin, Dest FROM (SELECT * FROM opensearch_dashboards_sample_data_flights WHERE AvgTicketPrice > 100 GROUP BY Origin, Dest, AvgTicketPrice) AS flights WHERE AvgTicketPrice < 1000 ORDER BY AvgTicketPrice"
}

## Return 20 rows

###
POST {{baseUrl}}/_plugins/_sql/_explain
Content-Type: application/x-ndjson

{
  "query": "SELECT Origin, Dest FROM (SELECT * FROM opensearch_dashboards_sample_data_flights WHERE AvgTicketPrice > 100 GROUP BY Origin, Dest, AvgTicketPrice) AS flights WHERE AvgTicketPrice < 1000 ORDER BY AvgTicketPrice LIMIT 30"
}

### Return 19 rows, missing row is
[
  "Abu Dhabi International Airport",
  "Albuquerque International Sunport Airport"
]

What is your host/environment?

• OS 2.17