CARTO ExecuteSQL() Results Set pagination is off by one

[molnar]

OGRCARTOLayer::FetchNewFeatures() uses the LIMIT and OFFSET method of pagination to fetch one page of features at at time. Example:

SELECT my_field FROM my_table LIMIT 500 OFFSET 0

... later:

SELECT my_field FROM my_table LIMIT 500 OFFSET 500

One of the problems right now is that the number passed in to specify the offset is the FID of the last feature plus one. Therefore you get:

SELECT my_field FROM my_table LIMIT 500 OFFSET 0

... last feature's ID is 500, so next offset is 501

SELECT my_field FROM my_table LIMIT 500 OFFSET 501

... etc., etc., skipping one feature every page.

Furthermore, this method is not guaranteed to work unless it includes ORDER BY "cartodb_id" ASC.

Even further more, this method of pagination suffers poor performance for large datasets, since the LIMIT and OFFSET method forces the database to scan over the offset on each query (we scan O(n*n) records instead of O(n*logn)). OGRCartoTableLayer::FetchNewFeatures() does this in a better way (orders by id, remembers the last id, uses a WHERE clause to fetch features with a higher id, and LIMITs the number of returned features).

[molnar]

Oh, I should mention that only happens when you SELECT "cartodb_id" (in addition to any other fields) because it causes us to go from a 0-based FID (the default initialization of iNext) to a 1-based FID.

[Even Rouault]

In 41750:

CARTO: fix missing features when iterating over a SQL result layer with pagination (fixes #6880)

[Even Rouault]

In 41751:

CARTO: fix missing features when iterating over a SQL result layer with pagination (fixes #6880)