[BUG] Queries with a LIMIT clause and those without it return different results. #3121
Description
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