[gdal-dev] OGR SQL and lother dialects?
even.rouault at spatialys.com
Fri Dec 4 08:20:47 PST 2015
Le vendredi 04 décembre 2015 17:04:12, Jürgen E. Fischer a écrit :
> Hi Even,
> On Fri, 04. Dec 2015 at 14:29:47 +0100, Even Rouault wrote:
> > Le vendredi 04 décembre 2015 14:08:40, Paolo Cavallini a écrit :
> > > Hi all,
> > > apparently the OGR SQL dialect is different from others:
> > > https://hub.qgis.org/issues/7380
> > > Any hope to bring them to common ground?
> > I believe x = NULL is an extension of the SQL standard that may
> > be supported or not by the various SQL engines.
> > AFAIK, the SQL 92 standard only accepts the "x IS NULL" syntax.
> > From my tests:
> > - SQLite rejects "x = NULL" syntax.
> Not here.
> SELECT 1 WHERE ''=NULL
Ah indeed when trying directly with SQLite. My initial test was through -
dialect SQLite which uses a SQLite virtual table mechanism, and that might be
in its implementation in OGR that it is rejected.
> returns no rows in sqlite3 - consistent with what I found in PostgreSQL and
> Oracle (add "FROM dual" there).
> > - OGR SQL accepts it (does not error on it), but it's more in the
> > unspecified behaviour category and apparently doesn't behave like x IS
> > NULL. I guess this must be evaluated like x = '' internally.
> That might have been the original behaviour which caused the problem - but
> it seems to have changed.
Actually when trying with GDAL 1.10, it seems = NULL behaves like IS NULL. And
starting with 1.11, it returns empty result set. I doubt any of the 2
behaviour was really intended since there's no test in the regression suite
for the = NULL syntax.
> I found that OGR SQL (if that's still the default dialect) now behaves like
> the others in that regard and x=NULL is different from x IS NULL. I
> updated the ticket accordingly.
Spatialys - Geospatial professional services
More information about the gdal-dev