[QGIS-Developer] OGR: sqlite with subset query, QgsOgrFeatureIterator::fetchFeature with SpatialFilterRect always returns feature with id 0

[Even Rouault]

Hi Sandro,

> I'm debugging an issue with a sqlite layer, loaded with a subset query,
> where the attribute values in the identify results table don't match the
> ones presented in the feature form (if auto show feature form is enabled).
> 
> Upon closer inspection, I noticed that the feature id of the feature
> picked by the feature info tool is always 0 (though it's remaining
> attributes are correct), this leads to the feature form displaying the
> attributes of feature fid=0, hence the mistmatch.
> 
> Stepping through the featureRequest performed by the identify tool shows
> that indeed OGR_F_GetFID( fet ) in QgsOgrFeatureIterator::readFeature
> always returns zero.
> 
> To isolate the problem, I created a small standalone program which
> replicates what the feature iterator does as a result of the identify
> query (see below), the odd thing is that there it works, and the fid is
> the expected one.
> 
> Is there some other global logic implemented in the ogr provider which
> could cause this behaviour?

There's a flow in your reproducer. You run OGR_DS_ExecuteSQL( ds, subsetQuery, NULL, 
NULL ); but do not use the resulting result layer. So basically this has no effect on the rest of 
your code (except a memory leak).

But, besides that issue, I suspect the behaviour you describ is the one you get with just 
running

$ ogrinfo poly.sqlite  -sql "select * from poly where ogc_fid = 10"
INFO: Open of `poly.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
[...]
<=== no FID field identified
Geometry Column = GEOMETRY
area: Real (0.0)
eas_id: Integer64 (0.0)
prfedea: String (0.0)
OGRFeature(SELECT):0    <==== FID
  area (Real) = 5268.813
  eas_id (Integer64) = 170
  prfedea (String) = 35043413
  POLYGON ((479750.6875 4764702.0,479658.59375 4764670.0,479640.09375 
4764721.0,479735.90625 4764752.0,479750.6875 4764702.0))

compared to

$ ogrinfo poly.sqlite -fid 10 -al
INFO: Open of `poly.sqlite'
      using driver `SQLite' successful.

Layer name: poly
Geometry: Polygon
[...]
FID Column = ogc_fid   <== FID field identified
Geometry Column = GEOMETRY
area: Real (0.0)
eas_id: Integer64 (0.0)
prfedea: String (0.0)
OGRFeature(poly):10  <== FID
  area (Real) = 5268.813
  eas_id (Integer64) = 170
  prfedea (String) = 35043413
  POLYGON ((479750.6875 4764702.0,479658.59375 4764670.0,479640.09375 
4764721.0,479735.90625 4764752.0,479750.6875 4764702.0))


The reason is that when issuing a SQL request there's no guarantee that it will result in a set 
of rows with unique identifiers (think of a join that could make several rows appear twice), so 
the FID on the result set of ExecuteSQL() are just a sequence starting at 0.

If you know that your request will result in a result set with real FIDs you can use the 
following trick to get the FID column as a regular column (renaming the ogc_fid as something 
else, since there's special logic to ignre "ogc_fid as a regular field. If your FID field name is 
already something else than this default "ogc_fid", then you'll already get it as a regular 
field)

$ ogrinfo poly.sqlite  -sql "select ogc_fid as this_is_the_fid, * from poly where ogc_fid = 10"
INFO: Open of `poly.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: Unknown (any)
[...]
Geometry Column = GEOMETRY
this_is_the_fid: Integer (0.0)
area: Real (0.0)
eas_id: Integer64 (0.0)
prfedea: String (0.0)
OGRFeature(SELECT):0
  this_is_the_fid (Integer) = 10
  area (Real) = 5268.813
  eas_id (Integer64) = 170
  prfedea (String) = 35043413
  POLYGON ((479750.6875 4764702.0,479658.59375 4764670.0,479640.09375 
4764721.0,479735.90625 4764752.0,479750.6875 4764702.0))


Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20170902/a81259f6/attachment.html>