filter on joined table broken

[qgib]

Author Name: Tobias Wendorff (Tobias Wendorff)
Original Redmine Issue: 19636
Affected QGIS version: 3.3(master)
Redmine category:vectors

---

new description:

#27463 (comment)

old description:
When creating a table join using "joins" in "layer properties", you can't filter the resulting table. QGIS informs that it's needed to build a virtual layer to make filtering work. But even if you select "yes", the joined fields don't appear in attribute table. Also, it doesn't matter if you create a virtual join in memory or not. Sure, you create a join using "processing", but this creates a new dataset. I'm also missing a "drop none-joined rows" in here... this seems to be only available from "processing" (like 1-to-many)

I've tested this behavior on several systems. QGIS3 (even dev-nightlies) have this problem. There are no logs, about a virtual layer being created or not.

---

• demo.zip (Tobias Wendorff)
• test_filter_qgis.rar (Andrea Giudiceandrea)
• test_filter_qgis.zip (Andrea Giudiceandrea)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Why tagging this as a "regression"? Did it worked as expected in 2.18 or another LTR release?

1 to many joins in QGIS are called relations, and you manage them in the project options.

Could you please attach a sample project and data to demonstrate the problem? Thanks.

---

• category_id was changed from Virtual Layers to Vectors
• status_id was changed from Open to Feedback
• regression was changed from 1 to 0

[qgib]

Author Name: Tobias Wendorff (Tobias Wendorff)

---

Giovanni Manghi wrote:

Why tagging this as a "regression"? Did it worked as expected in 2.18 or another LTR release?

See other report. I thought a regression is limited to the current major version.

1 to many joins in QGIS are called relations, and you manage them in the project options.

I wasn't able to get it to work, but I'll figure it out in the next days :-)

Could you please attach a sample project and data to demonstrate the problem? Thanks.

After building an example, I've figured out that the problem occurs only SOMETIMES, if I'm joining against delimited text files! I'll build a sample project of course.

[qgib]

Author Name: Tobias Wendorff (Tobias Wendorff)

---

My god! After some more trial & error I've figured out something REALLY unexpected. +You will not trust your eyes+: the join works, but the filter does NOT work, if one of the column headers starts with a digit!

• field-name: "a2" working
• field-name: "2a" not working
• field-name: "1" not working
• field-name: "1_1" not working
• field-name: "1_a" not working
• field-name 1: "abc", field-name 2: "2abc" not working

Check the demo file attached (join already created)

Edit: typo (join works, filter doesn't)

---

• 13171 was configured as demo.zip

---

• demo.zip (Tobias Wendorff)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Check the demo file attached (join already created)

Confused, again. The Join in your sample project works. I also tried (on master) to create another join using as join field "1_1" from "demo_table" and it works (even if it does not make sense as there is nothing in "1_1" that can be joined in "demo_join").

Also here #27463 (comment) you state that you see the problem when joining delimited text layers, but in the sample you attached you use json, which is not a delimited text layer.

My god! After some more trial & error I've figured...
field-name: "a2" working
field-name: "2a" not working

does this apply only to columns being used to define the join, or is just enough to be any column in the joined vector/table (anyway it seems I'm not confirming, see above)?

Please clarify. Thanks.

[qgib]

Author Name: Tobias Wendorff (Tobias Wendorff)

---

Giovanni Manghi wrote:

Confused, again. The Join in your sample project works. I also tried (on master) to create another join using as join field "1_1" from "demo_table" and it works (even if it does not make sense as there is nothing in "1_1" that can be joined in "demo_join").

Sorry, fixed it: the join works, but filter does not.

Also here #27463 (comment) you state that you see the problem when joining delimted text layers, but in the sample you attached you use json, which is not a delimited text layer.

I first thought it's a problem of CSV. Then I did some more debugging and figured out that every file with an attribute table with having a number at first position of the column name makes the filter non working. Again: join works, filter doesn't.

My god! After some more trial & error I've figured...
field-name: "a2" working
field-name: "2a" not working

does this apply only to columns being used to define the join, or is just enough to be any column in the joined vector/table (anyway it seems I'm not confirming, see above)?

It apply to any column. I'm joining "gitter_id_100m" (which has no leading number), the hoin works, but the filter doesn't.

[qgib]

Author Name: Andrea Giudiceandrea (@agiudiceandrea)

---

I've made some tests with QGIS 2.18 and now the issue reported by Tobias is clearer to me:

normally, when you try to filter a layer (using the "Filter..." option from it's contextual menu) that is joined with a table, the "Filter on joined fields" window appears and if you click on "Yes" then: a virtual layer is created and added to the map and the Query Builder window of the virtual layer opens up with the fields of the original layer and the also the joined fields listed

but, if the layer or the joined table contains a field whose name starts with a digit, then the virtual layer is not created (although the "Filter on joined fields" window appears and "Yes" is clicked) and the Query Builder window that opens up is that of the original layer so the joined fields aren't listed

I can confirm this strange behavior in QGIS 2.18.19.

[qgib]

Author Name: Andrea Giudiceandrea (@agiudiceandrea)

---

It seems that SQL has problems with layer/table or field names whose first character is a digit.

To handle this case (and also the additional case of white spaces in layer/table or field names), I think we can enclose layer/table and field names in double quotes (or square brackets) in the SQL query string generated by QgsVirtualLayerDefinitionUtils::fromJoinedLayer [1] during the virtual layer creation process.

[1] https://github.com/qgis/QGIS/blob/master/src/core/qgsvirtuallayerdefinitionutils.cpp

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Andrea Giudiceandrea wrote:

It seems that SQL has problems with layer/table or field names whose first character is a digit.

To handle this case (and also the additional case of white spaces in layer/table or field names), I think we can enclose layer/table and field names in double quotes (or square brackets) in the SQL query string generated by QgsVirtualLayerDefinitionUtils::fromJoinedLayer [1] during the virtual layer creation process.

[1] https://github.com/qgis/QGIS/blob/master/src/core/qgsvirtuallayerdefinitionutils.cpp

yeah I imagined that the problem could be that. Is too much asking for another patch?

[qgib]

Author Name: Andrea Giudiceandrea (@agiudiceandrea)

---

Giovanni Manghi wrote:

yeah I imagined that the problem could be that. Is too much asking for another patch?

There's another problem I realized only now that I have tested the tool...

It seems (with QGIS 2.18.23 and 3.3.0) that the Filter tool for joined layers doesn't work as expected even with normal fields/tables/layers names (i.e. not starting with a digit or containing spaces):
the virtual layer automatically created through the Filter tool does not contain the features geometry but only the attribute data of the vector layer on which is based.

It's strange to me that Tobias hasen't noticed this bigger problem.

Could you, Tobias and/or Giovanni, confirm this?

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Andrea Giudiceandrea wrote:

Giovanni Manghi wrote:

yeah I imagined that the problem could be that. Is too much asking for another patch?

There's another problem I realized only now that I have tested the tool...

It seems (with QGIS 2.18.23 and 3.3.0) that the Filter tool for joined layers doesn't work as expected even with normal fields/tables/layers names (i.e. not starting with a digit or containing spaces):
the virtual layer automatically created through the Filter tool does not contain the features geometry but only the attribute data of the vector layer on which is based.

It's strange to me that Tobias hasen't noticed this bigger problem.

Could you, Tobias and/or Giovanni, confirm this?

I have just tested this scenario on master/linux and can't confirm.

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

I have just tested this scenario on master/linux and can't confirm.

let me rephrase this:
applying a filter on a layer with a join works for me, meaning that the filtered features are complete with geometries.

What I see is that the joined attributes do NOT show in the filter dialog even if the tables do have "normal" column names, i.e. nothing starting with a number.

So it seems to me that the description of this issue must be re-written: filtering of joined layers is not working at all, regardless the naming of the columns.

Will wait for confirmation before doing that.

[qgib]

Author Name: Tobias Wendorff (Tobias Wendorff)

---

Andrea Giudiceandrea wrote:

It's strange to me that Tobias hasen't noticed this bigger problem.

Hehe, I've only tested, if the filter comes up or not. Thanks for finding this out :-)

Giovanni Manghi wrote:

What I see is that the joined attributes do NOT show in the filter dialog even if the tables do have "normal" column names, i.e. nothing starting with a number.

I can't reproduce this. For me, the joined attributes only do NOT show in the filter dialog if the table has at least ONE column name starting with a number. But be aware: I've joined a dataset without geometry to a base table, which has geometry. It might be different, if you join two layers containing a geometry.

[qgib]

Author Name: Andrea Giudiceandrea (@agiudiceandrea)

---

My system is: Windows 7 64 bit
QGIS version tested: 2.18.23 64 bit - 3.3.0 (05fb0f4)

Given data

In the map window:

• vector layer (point geometry): testlayer - 4 point features | attribute fields: layerid, layerfield

layerid    layerfield
ID01       layer value 1
ID02       layer value 2
ID03       layer value 3
ID04       layer value 4

• table layer (no geometry): testtable - 4 records | fields: layerid, tablefield

tableid    tablefield
ID01       CAT_A
ID02       CAT_B
ID03       CAT_A
ID04       CAT_B

A join is added to testlayer (layer properties/joins window): Joinlayer: testtable | Join field: tableid | Target field: layerid

Test projects (for 2.18 and 3) and layer files attached.

Steps

• from testlayer contextual menu select "Filter..."
• the "Filter on Joined fields" window pops up, then click "Yes"

Expected behavior ( see https://vimeo.com/123287077 and https://github.com/qgis/QGIS/blob/master/src/app/qgisapp.cpp#L9372-L9399 )

1. testlayer is hidden
2. a vector virtual layer (point geometry) named testlayer (virtual) is created and added to the map with the same style of testlayer
3. the virtual layer testlayer (virtual) Query Builder window is shown with testlayer fields and joined fields from testtable listed in the "Fields" section
4. clicking "OK"
   a) without entering any filter expression ('cause I want to see all the features):

• testlayer (virtual) draws in the map 4 point features and its attribute table shows the related 4 features records with the values of fields from testlayer and joined fields from testtable
  b) after entering a filter expression ("testtable_tablefield" = 'CAT_A'):
• testlayer (virtual) draws in the map 2 point features and its attribute table shows the related 2 features records with the values of fields from testlayer and joined fields from testtable

Actual behavior (on my system)

• QGIS 3.3.0 (05fb0f4)

1. OK
2. NOT OK : a +table+ virtual layer (+no geometry+) is created and added to the map
3. OK

a) NOT OK : +no feature is drawn in the map+ although testlayer (virtual) +attribute table window shows 4 features+ (total: 4, filtered: 4, selected: 0) with the related values of fields from testlayer and joined fields from testtable
b) NOT OK : +no feature is drawn in the map+ although testlayer (virtual) +attribute table window shows 2 features+ (total: 2, filtered: 2, selected: 0) with the related values of fields from testlayer and joined fields from testtable

• QGIS 2.18.23

1. OK
2. NOT OK : a +table+ virtual layer (+no geometry+) is created and added to the map
3. OK

a) NOT OK : +no feature is drawn in the map+ and testlayer (virtual) +attribute table window shows no features+ (total: 4, filtered: 0, selected: 0)
b) NOT OK : +no feature is drawn in the map+ although testlayer (virtual) +attribute table window shows 2 features+ (total: 2, filtered: 2, selected: 0) with the related values of fields from testlayer and joined fields from testtable

In the case of at least one field name starting with a digit (but also also containing spaces and also for layer names) as reported by Tobias, this is what happens:

Actual behavior (on my system)

• QGIS 3.3.0 (05fb0f4) and QGIS 2.18.23

1. OK
2. NOT OK : +no virtual layer is added to the map+ (actually the virtual layer is created and instantly deleted)
3. NOT OK : the original testlayer Query Builder window is shown with only testlayer fields listed in the "Fields" section (and without joined fields from testtable)
4. NOT OK : irrelevant

Partial workaround

A partial workaround to the geometry missing bug is to edit the virtual layer settings (from the virtual layer contextual menu "Edit virtual layer"):

in the Query section there is the sql query string

SELECT t.rowid AS uid, t.layerid, t.layerfield, j1.tablefield AS testtable_tablefield FROM testlayer_50a60fbc_fff6_4549_8351_44b27aa9b923 AS t LEFT JOIN testtable_d6bbdc34_5a2d_40de_a787_f671e0eaa284 AS j1 ON t."layerid"=j1."tableid"

just add t.geometry among the fields to select:

SELECT t.geometry, t.rowid AS uid, t.layerid, t.layerfield, j1.tablefield AS testtable_tablefield FROM testlayer_50a60fbc_fff6_4549_8351_44b27aa9b923 AS t LEFT JOIN testtable_d6bbdc34_5a2d_40de_a787_f671e0eaa284 AS j1 ON t."layerid"=j1."tableid"

and then click Add or OK to overwrite the virtual layer settings.

---

• 13195 was configured as test_filter_qgis.rar

---

• test_filter_qgis.rar (Andrea Giudiceandrea)

[qgib]

Author Name: Andrea Giudiceandrea (@agiudiceandrea)

---

• 13196 was configured as test_filter_qgis.zip

---

• test_filter_qgis.zip (Andrea Giudiceandrea)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Andrea Giudiceandrea wrote:

My system is: Windows 7 64 bit
QGIS version tested: 2.18.23 64 bit - 3.3.0 (05fb0f4)

yeah, I confirm this observations... is safe to say that this functionality does not work as it should be on both 2.18 and 3.*

Could any of you raise this matter in the developers mailing list? thanks!

---

• operating_system was changed from Microsoft Windows 7, 64-bit to
• description was changed from When creating a table join using "joins" in "layer properties", you can't filter the resulting table. QGIS informs that it's needed to build a virtual layer to make filtering work. But even if you select "yes", the joined fields don't appear in attribute table. Also, it doesn't matter if you create a virtual join in memory or not. Sure, you create a join using "processing", but this creates a new dataset. I'm also missing a "drop none-joined rows" in here... this seems to be only available from "processing" (like 1-to-many)

I've tested this behavior on several systems. QGIS3 (even dev-nightlies) have this problem. There are no logs, about a virtual layer being created or not. to new description:

https://issues.qgis.org/issues/19636#note-13

old description:
When creating a table join using "joins" in "layer properties", you can't filter the resulting table. QGIS informs that it's needed to build a virtual layer to make filtering work. But even if you select "yes", the joined fields don't appear in attribute table. Also, it doesn't matter if you create a virtual join in memory or not. Sure, you create a join using "processing", but this creates a new dataset. I'm also missing a "drop none-joined rows" in here... this seems to be only available from "processing" (like 1-to-many)

I've tested this behavior on several systems. QGIS3 (even dev-nightlies) have this problem. There are no logs, about a virtual layer being created or not.

• status_id was changed from Feedback to Open
• version was changed from 3.2.1 to 3.3(master)

[qgib]

Author Name: Andrea Giudiceandrea (@agiudiceandrea)

---

Giovanni Manghi wrote:

Could any of you raise this matter in the developers mailing list? thanks!

I'll write a report asap in qgis-developer with a proposed patch.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• description was changed from new description:

https://issues.qgis.org/issues/19636#note-13

old description:
When creating a table join using "joins" in "layer properties", you can't filter the resulting table. QGIS informs that it's needed to build a virtual layer to make filtering work. But even if you select "yes", the joined fields don't appear in attribute table. Also, it doesn't matter if you create a virtual join in memory or not. Sure, you create a join using "processing", but this creates a new dataset. I'm also missing a "drop none-joined rows" in here... this seems to be only available from "processing" (like 1-to-many)

I've tested this behavior on several systems. QGIS3 (even dev-nightlies) have this problem. There are no logs, about a virtual layer being created or not. to new description:

#27463 (comment)

old description:
When creating a table join using "joins" in "layer properties", you can't filter the resulting table. QGIS informs that it's needed to build a virtual layer to make filtering work. But even if you select "yes", the joined fields don't appear in attribute table. Also, it doesn't matter if you create a virtual join in memory or not. Sure, you create a join using "processing", but this creates a new dataset. I'm also missing a "drop none-joined rows" in here... this seems to be only available from "processing" (like 1-to-many)

I've tested this behavior on several systems. QGIS3 (even dev-nightlies) have this problem. There are no logs, about a virtual layer being created or not.

[qgib]

Author Name: Andrea Giudiceandrea (@agiudiceandrea)

---

[QGIS-Developer] "Filter on joined fields" and Virtual layers not working as expected
http://osgeo-org.1560.x6.nabble.com/QGIS-Developer-quot-Filter-on-joined-fields-quot-and-Virtual-layers-not-working-as-expected-td5375672.html
PR 7724 Filter on joined fields: add geometry for spatial layer and handle special field/layer names
#7724

[qgib]

Author Name: Nyall Dawson (@nyalldawson)

---

#7724

---

• pull_request_patch_supplied was changed from 0 to 1

[qgib]

Author Name: Andrea Giudiceandrea (@agiudiceandrea)

---

Applied in changeset 05fda10.

---

• status_id was changed from Open to Closed
• done_ratio was changed from 0 to 100