Handle dialect that does not exists

[latot]

Hi all, I was using GeomFromText and notice it does not exists on SpatiaLite:

ogr2ogr out.gpkg sample.csv -dialect spatialite -sql "select geomfromtext(wkt) as geom from sample"
ERROR 1: Undefined function 'geomfromtext' used.

With SQLite works:

ogr2ogr out.gpkg sample.csv -dialect sqlite -sql "select geomfromtext(wkt) as geom from sample"

This function is supported by SpatiaLite, is weird to not exists.

I'm using GDAL-3.8.0, with Gentoo-64.
sample.zip

[jratike80]

There is no -dialect spatialite. Just dialect -sqlite which supports SpatiaLite functions if they are available for the SQLite build that gets used.

The unknown dialect selection has no effect at all so the default SQL dialect "OGRSQL" is used.
Maybe there could be a list of supported dialects so that users could be warned about trying to use some non-existing dialects. However, that has not been any common issue if I remember right. At least we have tried to document which dialects are available for example in https://gdal.org/user/ogr_sql_dialect.html.

[rouault]

better runtime warning message and docs at #8844

[latot]

I think, would be better to instead a warning stop it, like when a driver don't exists.

We would expect run a particular dialect, if does not exists, I think we should change which one we will use, instead change which one will be used.

Thx!

[jratike80]

Maybe, but why users would like to use some undocumented dialect like -dialect=foo? We do not have secret dialects which are not documented but still work.

[latot]

There is several reasons, I think the most important one is that GDAL should use the declared options, just because is what we wanted to use.

We can see sqlite is not the default one:

ogr2ogr out.gpkg sample.csv -sql "select geomfromtext(wkt) as geom from sample"
ERROR 1: Undefined function 'geomfromtext' used.

There we need explicitly sqlite if not, we would like an error, because we know is a function from SQLite, and we want to use SQLite.

If GDAL is not compiled with SQLite for example, we would not have that dialect to use, which we also would like to not proceed and know is not available or not exists.

Is not weird to write bad some things, even on bash or programming, so is ideal when we wrote bad a file name, don't try to guess which one to use, if a dialect does not exists, don't try to use other.

We specify the dialect to use it, even if a SQL Query works in other dialect, we prepared it to work with that specific one, if works with other one is oks, and also causality, which I think, is not good idea to rely on, at least in programming, I think we like luck, but not in code u.u

Have the error also helps to handle bugs, if the query works on some systems and don't in others is troublesome, know if the dialect exists or not, to make reproducible code is great!

[lnicola]

Erroring out will probably break some functioning code, but it seems to me like a reasonable change for 4.0.

We do not have secret dialects which are not documented but still work.

Are NATIVE and DEBUG documented?

[jratike80]

Using and documenting the usage of SQL in GDAL is one of the not-so-simple tasks. It may be a mess but unfortunately it is hard to make easy. The default dialect depends on the datasource

• ogr2ogr out.gpkg sample.csv -sql "select st_geomfromtext(wkt) as geom from sample"
  default is OGR SQL
• ogr2ogr out.gpkg sample.gpkg -sql "select st_geomfromtext(wkt) as geom from sample"
  default is SQlite SQL
• ogr2ogr out.gpkg PG:"[PostGIS connection] -sql "select st_geomfromtext(wkt) as geom from sample"
  default is PostgreSQL SQL

Notice that it is not possible to select the PostgreSQL or other native dialects. So if you write always -dialect SQLite into your scripts you will get suboptimal results from PostGIS because the native functions with the same name are not used.

I think that sending a warning about user errors like -dialect foo is an improvement. Maybe GDAL could send an error as well but using some other SQL dialect as a fallback does not feel completely bad. There are similar cases which are not as visible for users for example if there are algorithms which are using GEOS but which have a native alternative that is used when GDAL is built without GEOS. But as I said, the SQL thing is unfortunately not easy to understand and requires that users read the documentation carefully from https://gdal.org/user/ogr_sql_dialect.html and from gdal.org/user/sql_sqlite_dialect.html#sql-sqlite-dialect.

Would you mind to edit the title to describe the issue better? "Missing GeomFromText" is misleading.

[rouault]

Are NATIVE and DEBUG documented?

DEBUG should definitely not be documented. It is for internal testing.
NATIVE: it is a hint in the GDAL_DMD_SUPPORTED_SQL_DIALECTS metadata to indicate that a driver has a specialized SQL implementation. It should generally not be used directly, as it is a synonymous of dialect =NULL .

[jratike80]

Are NATIVE and DEBUG documented?
Native dialects are explained in https://gdal.org/user/ogr_sql_dialect.html#non-ogr-sql with a list of affected drivers. Also the driver metadata contains that info

ogrinfo --format postgresql
 Supported SQL dialects: NATIVE OGRSQL SQLITE

[lnicola]

Also the driver metadata contains that info

That's even more confusing, does -dialect sqlite do anything on Postgres?

[jratike80]

The positive thing is that many GDAL utilities already check the user input.

gdalwarp -r spatialite base.tif foo.tif
ERROR 5: Unknown resampling method: spatialite.

[rouault]

That's even more confusing,

Did you read the changes of doc in #8844? Hopefully that should be cleared. Otherwise someone less will need to rephrase as I can't do better

does -dialect sqlite do anything on Postgres?

yes that works, although it is unlikely you'd have practical use of it

$ ogrinfo pg:dbname=autotest -sql "select sqlite_version()" -dialect sqlite -al -q

Layer name: SELECT
OGRFeature(SELECT):0
  sqlite_version() (String) = 3.31.1

[jratike80]

Spatialite has some practical functions which lack from PostGIS so there can be use for SQLite dialect with PostGIS https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html.