[Qgis-user] SQL language in DB Manager

[Antonio Valanzano]

Does someone know which dialect of SQL QGIS uses when working with virtual
layers ?

I have got a shapefile named "subway" and when i run the following query
using the DB Manager

SELECT s.gid, s.geometry
FROM subway s
WHERE (strpos(s.routes, 'Q') <> 0);

it produces a result that I can add to the map canvas as a virtual layer.

Such result is in contrast with the QGIS documentation for version 3.22


----------------------------------------------------------------------------------------------------------------------------
14.2.5. Creating virtual layers
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id24>
  The SQL query will be executed, regardless of the underlying provider of
the airports layer, even if this provider does not directly support SQL
queries.

14.2.5.2. Supported query language
<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#id26>

<https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#supported-query-language>

The underlying engine uses SQLite and SpatiaLite to operate.

It means you can use all of the SQL your local installation of SQLite
understands.
--------------------------------------------------------------------------------------------------------------------------


However  the query I have used contains a function "strpos" which is not
part of the SQL language as understood by SQLite.

If I run the same query on a layer which is part of a Spatialite database
the DB Manager produces the error "no such function: strpos".

If I run the same query on a layer which is part of a PostGIS database the
DB Manager produces the same result of the virtual layer created from the
shapefile.


So the question is "which dialect does QGIS use for running the query"?

Does it depend on the type of layer on which the query is applied ?

Is there a default SQL, which is used in case of a provider that does not
directly support SQL queries (such as shapefiles)?


Any info would help.

Antonio Valanzano
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20230228/2dd08d4f/attachment.htm>