Bug report #19721

Virtual layers show no feature when no filter string is set in the Query builder

Added by Andrea Giudiceandrea over 5 years ago. Updated over 5 years ago.

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

Description

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

QGIS 2.18.23 and QGIS 3.3.0-master (05fb0f4350) are affected.

Steps to reproduce the issue:

  • add a layer (eg. 'testlayer' with 4 features) in the map
  • create a virtual layer through "Add/Edit Virtual Layer" with even only a simple SQL query definition like 'SELECT * FROM testlayer'; the virtual layer is added to the map and shows all the 4 features.

then

  • 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.

See also
[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

Associated revisions

Revision 99696b6e
Added by Andrea Giudiceandrea over 5 years ago

Fix Virtual layers display with empty subsetstring

Fixes #19721

Virtual layers incorrectly show no feature when the expression filter string is empty, instead of correctly showing all the features.

Subset string needs to be tested against Empty instead of Null in order to correctly populate the wheres list and prepare a well formed SQL query string.

Revision 0521bfc0
Added by Andrea Giudiceandrea over 5 years ago

Fix Virtual layers display with empty subsetstring

Fixes #19721

Virtual layers incorrectly show no feature when the expression filter string is empty, instead of correctly showing all the features.

Subset string needs to be tested against Empty instead of Null in order to correctly populate the wheres list and prepare a well formed SQL query string.

(cherry picked from commit 99696b6ee10ea18a35a4cf23f8e506af69ee1f4e)

History

#1 Updated by Andrea Giudiceandrea over 5 years ago

Better looking at the code, it seems the bug is in QgsVirtualLayerFeatureIterator::QgsVirtualLayerFeatureIterator where
"subset" is tested for Null instead that for Empty 

QString subset = mSource->mSubset; 
    if ( !subset.isNull() ) 
{ 
      wheres << subset; 
    }

This leads to the insertion of an empty string in the "wheres" list, thus to the incorrect preparation of an SQL query with the WHERE clause without condition or the unneeded AND operator.

PR 7739 "Fix Virtual layers incorrectly displaying when expression filter is empty" https://github.com/qgis/QGIS/pull/7739

#2 Updated by Andrea Giudiceandrea over 5 years ago

  • % Done changed from 0 to 100
  • Status changed from Open to Closed

#3 Updated by Giovanni Manghi over 5 years ago

  • Resolution set to fixed/implemented

Also available in: Atom PDF