[gdal-dev] OGR SQL and lother dialects?

[Even Rouault]

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?

Hi Paolo,

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.
- 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.
- PostgreSQL accepts it, but the behaviour may be unspecified too.
And it seems that it behaves like OGR SQL, ie it doesn't select fields
with NULL values.
Actually looking at http://www.postgresql.org/docs/9.1/static/functions-comparison.html :

""" Do not write expression = NULL because NULL is not "equal to" NULL.
(The null value represents an unknown value, and it is not known whether
two unknown values are equal.) This behavior conforms to the SQL standard. 
Tip: Some applications might expect that expression = NULL returns true if
expression evaluates to the null value. It is highly recommended that these
applications be modified to comply with the SQL standard. 
"""
- MySQL seems to behave like PostgreSQL/OGR SQL

So it looks very much like "x = NULL" is not "normal" SQL at all.

Even

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