Query of spreadsheet layer not working consistently

[mablmabl]

I have created a layer based on this small test file: test_file.xlsx

Everything works fine but when I apply this query to the layer: "EEZ" = 'SE' AND "Station" NOT IN ('FLADEN','CL7') AND "Depth" > 30 everthing seems to work fine and the Test button in the Query builder returns 28 records as expected. BUT on the map the stations with depths less than 30 are still visible and much more than 28 records are shown.

If I copy the info in the excel file to a text file and create a layer based on this text file instead the query works as expected and only 28 stations are shown on the map.

Any suggestion on why the spreadsheet layer behaves in this way? Workaround?

I run QGIS 3.18.3 on windows 7.

[effjot]

That’s quite interesting. This happens to me, too, on Linux and QGIS 3.22.4, with both your Excel file and when converted to LibreOffice or to the old xls format. (I couldn’t reproduce it with some of my files, though.)

The filter works correctly on the attribute table and “count features” in the layer panel. It doesn’t filter all points on the map, though, as you already explained.

I found out that order of the filter statements matters. If I put the Station not in… at the end, the comparisons for EEZ and Depth work, but the stations Fladen and CL7 still appear on the map. Without the Station not in…, the filter works as expected. Somehow, negation (both with not in and separate comparisons with != or not like) messes up the terms after it. But I’m not sure how exactly. Adding parentheses doesn’t help either.

As far as I know, this plugin is mainly a frontend to create .vrt files. The “actual work” is done by GDAL. Also, QGIS delegates the data source filter to the data provider, in this case GDAL. I stumbled across this https://github.com/qgis/QGIS/issues/33454 where the data provider filter and QGIS’ own filter expressions don’t match. Adapted to your case, you can try it in the Python console and get 28 features:

layer = iface.activeLayer()
request = QgsFeatureRequest()
request = request.setFilterExpression("EEZ = 'SE' AND Station NOT IN ('FLADEN','CL7') AND Depth > 30")
t = 0
for x in layer.getFeatures(request):
    t += 1
print(t)

The Spreadsheet plugin documentation mentions some limitations of GDAL, but I’m not sure if these would apply to your case. Maybe you would need to narrow down the issue with the GDAL people.

[effjot]

oh, I just realised, you could test QGIS filter expressions in the “select features by expression” and in the rule-based symbology. Maybe you could use the latter as a workaround.

[arnaud-morvan]

I justed tested on Ubuntu 20.04 with QGIS 3.16 and 3.22 When I set the filter in layer properties / source / provider feature filter, points are correctly filtered on map. If I add the same filter in Attributes table / Advanced filter, only the attibutes table is filtered (this is expected).

So I do not reproduce any issue.

[effjot]

I’ve probably not expressed myself clearly. “The filter works correctly on the attribute table” meant that I set a data source filter (Ctrl-F) and that the attribute table correctly only contained only the filtered items, wheras on the map the filter didn’t work correctly. I have not used the filter feature of the attribute table.

Today, I tested it on Windows with QGIS 3.24.3 (GDAL 3.4.3) and the filter works as expected. When I’m back home tonight I’ll have a look at my Debian installation for the exact version numbers.

[effjot]

My Debian installation is QGIS 3.22.6 with GDAL 3.2.2 and has the issue:

• new empty project
• add spreadsheet layer provided by OP
• set filter expression as given by OP; test says “28 features” (correct)
• map shows 50 features (counted both by hand and with identify tool)
• with expression "EEZ" = 'SE' AND "Depth" > 30 AND "Station" NOT IN ('FLADEN','CL7') the map shows 30 features

I guess the problem is caused by GDAL and has been fixed sometime between 3.2.2 and 3.4.3.

[arnaud-morvan]

Ok, so I close the issue. Feel free to reopen if needed.