[QGIS-Developer] OGR/GBD: Unrecognized field name OBJECTID (was: Re: Warning - possible project corruption in 2.18.13)

[Even Rouault]

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>


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/20170920/611738f7/attachment-0001.html>