Bug report #19636: filter on joined table broken - QGIS Application - QGIS Issue Tracking

Bug report #19636

filter on joined table broken

Added by Tobias Wendorff over 6 years ago. Updated about 6 years ago.

Status:	Closed
Priority:	Normal
Assignee:	-
Category:	Vectors
Affected QGIS version:	3.3(master)	Regression?:	No
Operating System:		Easy fix?:	No
Pull Request or Patch supplied:	Yes	Resolution:
Crashes QGIS or corrupts data:	No	Copied to github as #:	27463

Description

new description:

#19636-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.

demo.zip (5.29 KB) Tobias Wendorff, 2018-08-21 02:45 AM

test_filter_qgis.rar (9.91 KB) Andrea Giudiceandrea, 2018-08-24 01:56 PM

test_filter_qgis.zip (10.3 KB) Andrea Giudiceandrea, 2018-08-25 01:30 PM

Associated revisions

Revision 05fda103
Added by Andrea Giudiceandrea about 6 years ago

Filter on joined fields: add geometry for spatial layer and handle sp…

…ecial field/layer names

Fixes #19636

"Filter on joined fields" does not work properly in the following two circumstances:

- when the (original) layer is a "spatial layer": the virtual layer created is an attribute only / non-spatial layer, instead of a spatial one

- when at least a field name or a layer name starts with a digit or contains white spaces (or probably also other special characters): the virtual layer is not created at all (actually is created and instantly deleted) without warning the user

This fixes both and also updates the related tests accordingly and adds another test.* Enclose field/layer names in double quotes.

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

More details: https://issues.qgis.org/issues/19636#note-13
Projects and layers to test the bugs: https://issues.qgis.org/attachments/download/13196/test_filter_qgis.zip

History

#1 Updated by Giovanni Manghi over 6 years ago

Category changed from Virtual Layers to Vectors
Status changed from Open to Feedback
Regression? changed from Yes to No

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.

#2 Updated by Tobias Wendorff over 6 years ago

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.

#3 Updated by Tobias Wendorff over 6 years ago

File demo.zip added

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)

#4 Updated by Giovanni Manghi over 6 years ago

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 #19636-2 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.

#5 Updated by Tobias Wendorff over 6 years ago

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 #19636-2 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.

#6 Updated by Andrea Giudiceandrea over 6 years ago

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.

#7 Updated by Andrea Giudiceandrea over 6 years ago

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

#8 Updated by Giovanni Manghi over 6 years ago

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?

#9 Updated by Andrea Giudiceandrea about 6 years ago

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?

#10 Updated by Giovanni Manghi about 6 years ago

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.

#11 Updated by Giovanni Manghi about 6 years ago

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.

#12 Updated by Tobias Wendorff about 6 years ago

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.

#13 Updated by Andrea Giudiceandrea about 6 years ago

File test_filter_qgis.rar added

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

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 )

testlayer is hidden
a vector virtual layer (point geometry) named testlayer (virtual) is created and added to the map with the same style of testlayer
the virtual layer testlayer (virtual) Query Builder window is shown with testlayer fields and joined fields from testtable listed in the "Fields" section
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 (05fb0f4350)

OK
NOT OK : a table virtual layer (no geometry) is created and added to the map
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

OK
NOT OK : a table virtual layer (no geometry) is created and added to the map
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 (05fb0f4350) and QGIS 2.18.23

OK
NOT OK : no virtual layer is added to the map (actually the virtual layer is created and instantly deleted)
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)
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.