Opened 12 months ago

Closed 6 weeks ago

Last modified 6 weeks ago

#6880 closed defect (fixed)

CARTO ExecuteSQL() Results Set pagination is off by one

Reported by: molnar Owned by: warmerdam
Priority: normal Milestone: 2.2.4
Component: OGR_SF Version: unspecified
Severity: normal Keywords: carto
Cc:

Description

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).

Change History (4)

comment:1 Changed 12 months ago by 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.

comment:2 Changed 6 weeks ago by Even Rouault

Resolution: fixed
Status: newclosed

In 41750:

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

comment:3 Changed 6 weeks ago by Even Rouault

In 41751:

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

comment:4 Changed 6 weeks ago by Even Rouault

Component: defaultOGR_SF
Keywords: carto added
Milestone: 2.2.4
Note: See TracTickets for help on using tickets.