SQL: GROUP BY with timezone and small page size returns duplicate values

[bpintea]

The following query on the test employees.csv:

POST _sql
{
  "query": "SELECT YEAR(birth_date) FROM employees GROUP BY YEAR(birth_date)",
  "time_zone": "Europe/Berlin",
  "fetch_size": 1
}

will return the following paged results: null, 1952, 1952, ...
Increasing the page size to 2 will duplicate 1954.

This doesn't (easily?) reproduce without a non-Z timezone.

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[matriv]

@bpintea This "issue" has to do with not passing the "time_zone": "Europe/Belrin" param to the subsequent "cursor": "xxxxxx requests. If it's not passed the subsequent requests will use Z zoneId, thus resulting to this discrepancy in the results.

I will open a PR to clarify this in our docs.

[matriv]

The query is of course executed once with the correct timezone passed with the initial HTTP request, the problem is in the HitExtractors where the timezone is set to Z for the processing of the subsequent pages.

[bpintea]

@bpintea This "issue" has to do with not passing the "time_zone": "Europe/Belrin" param to the subsequent "cursor": "xxxxxx requests. If it's not passed the subsequent requests will use Z zoneId, thus resulting to this discrepancy in the results.

Ah, I see. I haven't checked what the cursor actually stores, but:

• the (/some?) other parameters (like page size) seem to be stored in it, since the size given to the first request is kept on subsequent pages;
• a quick base64 decode reveals that the timezone appears in it, even if the time zone is not specified past the 1st request;
• (I vaguely remember seeing a comment stating that only the page timeout parameter should influence the request that contains a cursor -- I'll check it on Monday).

[bpintea]

The docs currently state: Do note that most parameters (outside the timeout and columnar ones) make sense only during the initial query - any follow-up pagination request only requires the cursor parameter as explained in the pagination chapter. That’s because the query has already been executed and the calls are simply about returning the found results - thus the parameters are simply ignored.

[matriv]

I opened #52080 to fix docs and also fix the issue for the JDBC driver.

[matriv]

@bpintea I was confused with the implementation. And @costin helped out (as always :) ): #52080 (comment)

The expected behaviour is not to pass the timezone in the subsequent paging requests, so there is a bug that affects both http REST endpoint and the jdbc driver.