[gdal-dev] OGR SQL and lother dialects?

[Even Rouault]

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.
> 
> 
> Jürgen

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com