OGR FileGDB driver: 'OBJECTID' not recognised as an available field.

[peifer]

OGR's -where switch seems to be aware of the OBJECTID field in a FileGDB, whereas the -sql option reports: 'OBJECTID' not recognised as an available field, see (0) below.

I observed a potentially related behaviour for another FileGDB, which has 2 layers: a simple table without geometry (FSTA2009_grid) and a Feature Class with a polygon geometry (eea_1Kgrid). Table FSTA2009_grid seems to have a FID column which has 2 names at the same time: OBJECTID_1 and OBJECTID, see (1)

ogrinfo -where understands both: OBJECTID_1 and OBJECTID, see (2)

ogrinfo -sql only understands OBJECTID, see (3)

(0)

$ ogrinfo -q -geom=no out.gdb eea_1Kgrid -where OBJECTID=1

Layer name: eea_1Kgrid
OGRFeature(eea_1Kgrid):1
  CELLCODE (String) = E1647N1038
  Shape_Length (Real) = 0.0383874724895616
  Shape_Area (Real) = 9.18055152110593e-05

$ ogrinfo -q -geom=no out.gdb -sql "select * from eea_1Kgrid where OBJECTID=1"
ERROR 1: 'OBJECTID' not recognised as an available field.

(1)

$ ogrinfo -al -so FSTA2009_grid.gdb
INFO: Open of `FSTA2009_grid.gdb'
      using driver `FileGDB' successful.

Layer name: FSTA2009_grid
Geometry: None
Feature Count: 4735968
Layer SRS WKT:
(unknown)
FID Column = OBJECTID_1
OBJECTID: Integer (0.0)
CELLCODE: String (0.0)
FGA1_M: Real (0.0)
FGA1_S: Real (0.0)
FGA2_M: Real (0.0)
FGA2_S: Real (0.0)
FGB2_M: Real (0.0)
FGB2_S: Real (0.0)

Layer name: eea_1Kgrid
Geometry: Multi Polygon
Feature Count: 4948506
Extent: (1547000.000100, 940999.999400) - (6525999.999900, 5416162.796700)
Layer SRS WKT:
PROJCS["ETRS89 / LAEA Europe",
    GEOGCS["ETRS89",
        DATUM["European_Terrestrial_Reference_System_1989",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            TOWGS84[0,0,0,0,0,0,0],
            AUTHORITY["EPSG","6258"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AUTHORITY["EPSG","4258"]],
    PROJECTION["Lambert_Azimuthal_Equal_Area"],
    PARAMETER["latitude_of_center",52],
    PARAMETER["longitude_of_center",10],
    PARAMETER["false_easting",4321000],
    PARAMETER["false_northing",3210000],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AXIS["Y",NORTH],
    AXIS["X",EAST],
    AUTHORITY["EPSG","3035"]]
FID Column = OBJECTID
Geometry Column = Shape
CELLCODE: String (0.0)
Shape_Length: Real (0.0)
Shape_Area: Real (0.0)

(2)

$ ogrinfo -q FSTA2009_grid.gdb FSTA2009_grid -where OBJECTID_1=1

Layer name: FSTA2009_grid
OGRFeature(FSTA2009_grid):1
  OBJECTID (Integer) = 1
  CELLCODE (String) = E1647N1038
  FGA1_M (Real) = 365.10730848
  FGA1_S (Real) = 2.73892079609
  FGA2_M (Real) = 71.7346430224
  FGA2_S (Real) = 13.9402659282
  FGB2_M (Real) = 71.7751336583
  FGB2_S (Real) = 13.9324017808

$ ogrinfo -q FSTA2009_grid.gdb FSTA2009_grid -where OBJECTID=1

Layer name: FSTA2009_grid
OGRFeature(FSTA2009_grid):1
  OBJECTID (Integer) = 1
  CELLCODE (String) = E1647N1038
  FGA1_M (Real) = 365.10730848
  FGA1_S (Real) = 2.73892079609
  FGA2_M (Real) = 71.7346430224
  FGA2_S (Real) = 13.9402659282
  FGB2_M (Real) = 71.7751336583
  FGB2_S (Real) = 13.9324017808

(3)

$ ogrinfo -q FSTA2009_grid.gdb -sql "select * from FSTA2009_grid where OBJECTID_1=1"
ERROR 1: 'OBJECTID_1' not recognised as an available field.

$ ogrinfo -q FSTA2009_grid.gdb -sql "select * from FSTA2009_grid where OBJECTID=1"

Layer name: FSTA2009_grid
OGRFeature(FSTA2009_grid):1
  OBJECTID (Integer) = 1
  CELLCODE (String) = E1647N1038
  FGA1_M (Real) = 365.10730848
  FGA1_S (Real) = 2.73892079609
  FGA2_M (Real) = 71.7346430224
  FGA2_S (Real) = 13.9402659282
  FGB2_M (Real) = 71.7751336583
  FGB2_S (Real) = 13.9324017808 

[peifer]

Sample file geodatabase: out.gdb

[rouault]

Well, you're pointing to a very subtile aspect of the SQL use in OGR, in particular the mix between OGR own SQL engine and the possible delegation of part or all of an SQL request to the SQL engine of the Database server/SDK...

The key issue is that OBJECTID is the FID column of your table and not a regular attribute.

When you use -where OBJECTID=1, the where clause is directly forwarded from OGR to the the FileGDB SDK itself, and OGR makes no attempt of checking its content. The FileGDB SDK understands it, so you get a result.

When you use -sql "SELECT * from eea_1Kgrid where OBJECTID=1", as there is no specialized implementation of ExecuteSQL() by the FileGDB driver (I somehow remember that the root cause is that the SDK is broken w.r.t to evaluating full SQL requests), OGR will then try to evaluate it with its own SQL engine, which one doesn't (try to) understand that OBJECTID is the FID column, and supposes that it is a regular field, which it isn't, hence the error message "'OBJECTID' not recognised as an available field.

With the current code, one solution/workaround is then to use the special field "FID" (see  http://gdal.org/ogr/ogr_sql.html ) in your request :

-sql "SELECT * from eea_1Kgrid where FID=1"

But if you try that, it will not work directly, because the OGR SQL engine will try to forward the where clause to the FileGDB SDK, which doesn't understand the special FID keyword. Grrr

But there's an extra workaround/solution, brought by the changes done in #4022, that consists in specifically adding -dialect OGRSQL.

So, all in all, the following works :

ogrinfo out.gdb -al -sql "select * from eea_1Kgrid where fid = 1" --debug on -dialect OGRSQL

I'm well aware this is not really understandable by someone who hasn't spent a few hours digging in the code, but I fail to see a solution that would guess correctly in all cases. One root cause seem to be that the OGRGenSQLResultsLayer() should know if the implementation of the OGRLayer by drivers override SetAttributeFilter?() to pass the clause to another SQL engine (in which case, it won't understand OGR extensions), or if it just uses the default implementation (which will understand them).

Another approach, for the specific case of this ticket, would be to teach the OGR SQL engine to accept the return of GetFIDColumn() (if not empty) and deal with it exactly as the special FID keyword.

[peifer]

Thanks for the detailed explanations. I already noted that (as you wrote): a simple "where FID=1" doesn't help. Thanks for the hint that it can be used combination with "-dialect OGRSQL". Earlier, I noted the existence of the new -dialect switch, but I never used it.

Would you have a hint about the other issue: OBJECTID_1 versus OBJECTID ?

[rouault]

I think the same explanation applies. In the second GDB, OBJECTID_1 is recognized as the FID column, whereas OBJECTID is a regular field. This must comes from the structure of this particular FGDB.

[peifer]

OK. Thanks again. From your explanations, I understand that it would be best to close the ticket. I will put the resolution marker on "won't fix", which for this ticket should actually read: "no generic solution possible in the short term, but there is a workaround for the reported specific case".