[QGIS-Developer] "Filter on joined fields" and Virtual layers not working as expected

[andreaerdna]

The Virtual layers provider and the "Filter on joined fields" functionality
(that is very useful although not documented, probably 'cause was meant to
work "automagically"), have been introduced in QGIS by Hugo Mercier a couple
of years ago [1].

It seems that now they not work as expected anymore in QGIS 3.3.0
(05fb0f4350) and 2.18.23 too, but probably since earlier versions.

I realised this trying to reproduce the bug #19636 [2] reported by Tobias
Wendorff about strange behaviour of the "Filter on joined fields"
functionality when a field name starts with a digit.


** Filter on joined fields **

Basically, "Filter on joined fields" should work as follows:
- sets invisible the (original) layer with joined fields we want to filter
on
- build an SQL query based on the join(s) properties of the original layer
and adds to the map a virtual layer based on that SQL query with the same
geometry and style of the original layer
- opens the virtual layer Query Builder in order to enter a filter
expression

There are two circumstances in which it does not work properly (more
details, with test projects and layers, in [3]):

- when the (original) layer is a "spatial layer" (that is in most of the use
cases): the virtual layer created is an attribute only / non-spatial layer,
instead of a spatial one, so no features geometries are visible in the map
and obviously the style is not applied, making the functionality not really
useful

- when at least a field name or a layer name starts with a digit or contains
white spaces (or probably other special characters): the virtual layer is
not created at all (actually is created and instantly deleted) without error
messages, so the feature is totally broken

It seems to me the bug is in qgsvirtuallayerdefinitionutils.cpp [4].
In facts, the SQL query generated by
QgsVirtualLayerDefinitionUtils::fromJoinedLayer:
- does not contain the "geometry" field of the original spatial layer among
the selected fields
- field and layer names are not all enclosed in double quotes

I'll submit a PR with a simple patches that address both the problems (and
update the tests accordingly), I hope.


** Virtual layers **

There's a additional boring bug that limits virtual layers and consequently
also "Filter on joined fields" usefulness:

- the virtual layers show no feature when no filter string is set in the
Query builder, instead of showing all the features

step to reproduce the issue:

- add a layer (eg. 'testlayer' [3] with 4 features) in the map
- create a virtual layer through "Add/Edit Virtual Layer" with a simple SQL
query definition like 'SELECT * FROM testlayer'; the virtual layer is added
to the map and shows all the 4 features.
- with QGIS 2.18.23: open the virtual layer Query builder [Ctrl+F] (the
"filter expression" box is empty); click "Test" button -> "The where clause
returned 4 row(s)" -> "OK"; close the Query builder clicking "OK": the
features are disappeared from the map and the attribute table window shows
no feature (total: 4, filtered: 0, selected: 0)
- with QGIS 3.3.0: to spot the bug, first open the virtual layer Query
builder (the "filter expression" box is empty); enter a filter expression
and close the Query builder clicking "OK": the virtual layer shows a subset
of features in the map;  open again the Query builder and delete the filter
expression previously entered (manually or using the "Clear" button); click
"Test" button -> "The where clause returned 4 row(s)" -> "OK"; close the
Query builder clicking "OK": the features are disappeared from the map and
the attribute table window shows no feature (total: 4, filtered: 0,
selected: 0).

To show again all the features, an always true expression (like " 1 = 1 ")
must be entered in the Query builder as a filter expression.

Anyway, when the issue comes up, a bunch of warnings are logged in the
Vlayer messages tab like:

WARNING    Query preparation error on SELECT 0,"geometry" FROM _query WHERE
: near " ": syntax error
or
WARNING    Query preparation error on SELECT 0,"tableid","tablefield" FROM
_query WHERE : near " ": syntax error
or
WARNING    Query preparation error on SELECT "uid","geometry" FROM _query
WHERE AND "geometry" is not null AND
MbrIntersects("geometry",BuildMbr(19.2606,36.4148,26.5954,39.0184)): near
"AND": syntax error

depending on the SQL query definition of the virtual layer.

So I think that there is something wrong in the
QgsSpatiaLiteFeatureIterator::prepareStatement where the SQLite statement is
prepared: I guess that, or the function fails to notice that the filter
expression is empty and adds the WHERE clause without condition or an
unneeded AND operator, or the filter expression variable is not properly
emptied somewhere else.

Unfortunately, I don't have a simple patch to fix the bug...

I hope this can help bring the Virtual layers and the "Filter on joined
fields" back to full functionality.

Best regards.

Andrea Giudiceandrea

[1] PR  #2568 Virtual layers https://github.com/qgis/QGIS/pull/2568 - PR
#2647 Virtual layers GUI integration https://github.com/qgis/QGIS/pull/2647
[2] Bug report #19636 filter on joined table broken
https://issues.qgis.org/issues/19636
[3] https://issues.qgis.org/issues/19636#note-13 -
https://issues.qgis.org/attachments/download/13196/test_filter_qgis.zip
[4]
https://github.com/qgis/QGIS/blob/master/src/core/qgsvirtuallayerdefinitionutils.cpp#L24
[5]
https://github.com/qgis/QGIS/blob/master/src/providers/spatialite/qgsspatialitefeatureiterator.cpp#L313



--
Sent from: http://osgeo-org.1560.x6.nabble.com/QGIS-Developer-f4099106.html