#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 by , 7 years ago
comment:4 by , 6 years ago
Component: | default → OGR_SF |
---|---|
Keywords: | carto added |
Milestone: | → 2.2.4 |
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 ofiNext
) to a 1-based FID.