OGR SQLite driver doesn't accept special fields in WHERE clause

[andersmoe]

When using OGR/SQLite and OGR-SQL, using one of the OGR-SQL special fields in the WHERE clause gives an error. For example

select OGR_GEOMETRY from world_wgs84 WHERE OGR_GEOMETRY = 'POLYGON'

gives

GDAL failure: 1 : In ResetStatement(): sqlite3_prepare(SELECT _rowid_, * FROM 'world_wgs84' WHERE OGR_GEOMETRY = 'POLYGON' ):

no such column: OGR_GEOMETRY

[Even Rouault]

Not really a bug IMHO. The OGR-SQL special fields are... OGR specific, so the regular SQL processing that forwards the processing to sqlite must not be used in that case.

As describe in the Sqlite driver doc, you should try passing the "OGRSQL" dialect to ExecuteSQL() if you use OGR API directly. If you use ogrinfo or ogr2ogr with the -sql option, you should add -dialect OGRSQL to the command line.

If you use the -where option of ogrinfo or ogr2ogr (or the SetAttributeFilter() of the OGR API), you can't. The API has no way to specify that this is OGRSQL dialect. In that case, you must issue a full SQL SELECT with ExecuteSQL() as described above.

[andersmoe]

Actually I am using the OGRSQL dialect parameter to ExecuteSQL. For example, the following works fine :

select OGR_GEOMETRY from world_wgs84

It's when I add the WHERE clause things go wrong. However I've noted that the error occurs when I subsequently call

layer_d->ResetReading(); OGRFeature* feature = layer_d->GetNextFeature();

where layer_d is the result layer from ::ExecuteSQL()

Anders

[Even Rouault]

Hum, I have investigated this a bit and I'm attaching a python script that tests different variations and exhibits cases that work and cases that don't work. If I understand well, you're hitting the test 5 : the easy workaround for now would be to use test 4.

The core of the issue is that when you use OGRSQL dialect, ExecuteSQL fallbacks to generic ExecuteSQL that instanciates a OGRGenSQLResultsLayer. The where clause of the SQL is extracted by the OGR SQL parser and forwared back to the underlying layer, in this case the OGRSQLiteTableLayer, through its SetAttributeFilter() method, and this method forwards the where expression to the libsqlite... We could modify OGRGenSQLResultsLayer to avoid forwarding the WHERE clause to the underlying layer and do the attribute filter itself. That would work, but that would perhaps decrease performance in a few cases that currently work. A possible mitigation could be to avoid forwarding the WHERE clause to the underlying layer only when the OGRGenSQLResultsLayer has been build from a ExecuteSQL() method that has been called with a OGRSQL dialect.

Frank, any thoughts ?

[warmerdam]

I see the issue. It would be nice if we could avoid forwarding the WHERE clause to SetAttributeFilter() if we know the ExecuteSQL() was done *specifically* with the OGRSQL dialect. However, if this is not reasonably practical, I'd be inclined to just accept this as a limitation of the OGRSQL dialect.

[Even Rouault]

r22092 /trunk/gdal/ogr/ogrsf_frmts/generic/ (ogr_gensql.cpp ogr_gensql.h ogrdatasource.cpp): OGRGenSQLResultsLayer: if the dialect is explicitely set to OGRSQL, don't propagate the WHERE clause of the SELECT to the source layer, but evaluate it instead at the OGRGenSQLResultsLayer level (#4022)

r22093 /trunk/autotest/ogr/ogr_sqlite.py: Test that ExecuteSQL() with OGRSQL dialect doesn't forward the where clause to sqlite (#4022)

[Even Rouault]

r22246 /trunk/ ( 2 files in 2 dirs ): More restrictive fix for #4022, to avoid regressions