###
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 the bug? Queries with a LIMIT clause and those without it return different results.
How can one reproduce the bug?
{ "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" ]