Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#4022 closed defect (fixed)

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

Reported by: andersmoe Owned by: warmerdam
Priority: normal Milestone: 1.9.0
Component: OGR_SF Version: 1.8.0
Severity: normal Keywords: ogr sqlite ogr-sql special fields
Cc:

Description

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

Attachments (1)

testsqlite.py (2.1 KB) - added by Even Rouault 6 years ago.

Download all attachments as: .zip

Change History (7)

comment:1 Changed 6 years ago by 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.

comment:2 Changed 6 years ago by 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

comment:3 Changed 6 years ago by 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 ?

Changed 6 years ago by Even Rouault

Attachment: testsqlite.py added

comment:4 Changed 6 years ago by 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.

comment:5 Changed 6 years ago by Even Rouault

Milestone: 1.9.0
Resolution: fixed
Status: newclosed

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)

comment:6 Changed 6 years ago by Even Rouault

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

Note: See TracTickets for help on using tickets.