[QGIS-Developer] OGR/GBD: Unrecognized field name OBJECTID

[Sandro Mani]

On 20.09.2017 12:46, Even Rouault wrote:
>
> On mercredi 20 septembre 2017 11:34:50 CEST Sandro Mani wrote:
>
> > On 20.09.2017 08:45, Nyall Dawson wrote:
>
> > > On 20 September 2017 at 10:18, Nyall Dawson 
> <nyall.dawson at gmail.com> wrote:
>
> > >> Hi all,
>
> > >>
>
> > >> I've just been trying to diagnose an issue with loading an existing
>
> > >> 2.18 project in the new 2.18.13 release.
>
> > >>
>
> > >> Seems like something may be broken here with regards to loading OGR
>
> > >> based layers which have a subset filter set.
>
> > >>
>
> > >> These layers trigger the "missing layers" dialog when loading the
>
> > >> project and the layer link cannot be repaired without removing the
>
> > >> filter clause.
>
> > >>
>
> > >> (specifically this is with .gdb layers on Windows - not sure yet if
>
> > >> it's restricted to this combination).
>
> > >>
>
> > >> So just a heads up to test your existing projects before rolling out
>
> > >> the 2.18.13 update.
>
> > >
>
> > > Failing unit test is here: https://github.com/qgis/QGIS/pull/5222,
>
> > > regression bisected to https://github.com/qgis/QGIS/commit/8eaeaaa
>
> > >
>
> > > Sandro can you take a look at this?
>
> >
>
> > Argh.
>
> >
>
> > So the result of the subset query
>
> >
>
> > SELECT OBJECTID as orig_ogc_fid, * FROM "lines" WHERE "text" = 'shape 2'
>
> >
>
> > now is
>
> >
>
> > 305: OGR(1): OGR[3] error 1: Unrecognized field name OBJECTID.
>
> > 305: OGR(1): Data source is invalid (Unrecognized field name OBJECTID.)
>
> >
>
> > but OBJECTID is what was returned by
>
> >
>
> > OGR_L_GetFIDColumn( layer )
>
> <long_story>
>
> Ah, that one is tricky. Let me try to explain the situation
>
> Normally when you do ExecuteSQL(sql_statement, NULL, dialect) with 
> dialect==NULL, OGR will forward sql_statement to the underlying SQL 
> engine for a SQL compatible DBMS (Postgres, MySQL, SQLite, OCI, 
> etc....), and if there's no such engine, evaluate it with its own 
> OGRSQL builtin engine.
>
> For the FileGDB driver, the ESRI SDK has some SQL evaluation 
> capability, but my testing showed that it was too limited to be 
> reliable, so by default I fallback to OGRSQL.
>
> You can force the use of the ESRI SQL engine by specifying a dialect 
> == "FileGDB". If you do that with the above statement, you'll get
>
> $ ogrinfo sampleFGDB_1.gdb -sql "select OBJECTID as foo, * FROM 
> polygonArcsZM1" -dialect FileGDB -q
>
> ERROR 1: Failed at executing 'select OBJECTID as foo, * FROM 
> polygonArcsZM1' (An invalid SQL statement was used.)
>
> So no better. Apparently, the ESRI SDK doesn't like selecting a column 
> and "*".
>
> So to come back to the bug, the SQL statement is evaluated with the 
> OGRSQL engine. So this is a bug in it in the situation where the FID 
> column name is an actual non-empty string. OGRSQL only understands 
> "FID" as the shortcut for whatever the FID column name, and doesn't 
> try to look if a column name matches OGR_L_GetFIDColumn() (when it was 
> developped, all drivers that defaulted to OGRSQL had an implementation 
> of OGR_L_GetFIDColumn() that returned a "" string). So this is a 
> current shortcoming of the OGRSQL engine, that should probably be fixed
>
> In the meantime you have to workaround it on the QGIS side, both for 
> the "FileGDB" and "OpenFileGDB" drivers (. In that situation instead 
> of using the result of OGR_L_GetFIDColumn() to build your SQL 
> statement, hardcode FID.
>
> So your statement will become:
>
> SELECT FID as orig_ogc_fid, * FROM "lines" WHERE "text" = 'shape 2'
>
> Actually when digging a bit more this could affect also the GeoJSON 
> driver, but not in all situations. Only if you have a "id" member at 
> the same level of "type": "Feature"
>
> So something like
>
> { "type": "Feature", "id": 168, "properties": {...}, "geometry": {...} "
>
> then id will be used as the FID and returned by OGR_L_GetFIDColumn()
>
> And you'll hit the FileGDB bug
>
> If you have something like
>
> { "type": "Feature", "properties": { "id": 168,...}, "geometry": {...} "
>
> Then OGR_L_GetFIDColumn() will return "id" but you'll have also a 
> regular id field, so "SELECT id as ogrig_ogc_fid, * FROM ..." will 
> work then.
>
> </long_story>
>
> <short_answer>
>
> My (final!) suggestion is thus to first try:
>
> SELECT {OGR_L_GetFIDColumn() or FID if it is empty} as orig_ogc_fid, * 
> FROM
>
> If that returns an error look for CPLGetLastErrorMsg() and if it 
> contains OGR_L_GetFIDColumn() in it, retry with
>
> SELECT FID as orig_ogc_fid, * FROM
>
> Not pretty, but should be relatively robust
>
> </sort_answer>
>
Thanks for the detailed explanation.

Does it actually hurt to try with FID regardless of the contents of 
CPLGetLastErrorMsg if the previous attempt failed, instead of relying on 
the contents of an error message?

PR is here:
https://github.com/qgis/QGIS/pull/5224


Sandro
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20170920/49a96463/attachment.html>