Bug report #11076

MSSQL vector layer filtering problem

Added by Stefan Loehr almost 6 years ago. Updated almost 6 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/MSSQL
Affected QGIS version:2.4.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:19409

Description

There seems to be a bug in the MSSQL provider when accessed from python.
If you retrieve objects from a featurelayer with a QgsFeatureRequest, the filter expression ist not applied.
For example if you use this python code to retrieve features:

featReq=QgsFeatureRequest()
it = my_mssql_layer.getFeatures( featReq )

the resulting SQL Query as reported from SQL Server Profiler is:

select [objectid],... lots of columns ... ,[shape] from [dbo].[testpolygons]

Thats ok. It returns all features.
But if you add a filter expression:

featReq=QgsFeatureRequest()
featReq=featReq.setFilterExpression ('users>1')
my_feature_iterator = my_mssql_layer.getFeatures( featReq )

the resulting SQL is corrupt:

select [objectid],... lots of columns ... ,[shape] from [dbo].[flurstueck_f] where [shape].STIntersects([geometry]::STGeomFromText('POLYGON((0.00000000 0.00000000, 0.00000000 0.00000000, 0.00000000 0.00000000, 0.00000000 0.00000000, 0.00000000 0.00000000))',25832)) = 1 and [objectid] = 30681189080760422

As you can see, a filter rectangle with unusable coordinates and an ID filter with a non existant id-number is applied.
This query returns nothing of course.

It seems that the FilterType of the QgsFeatureRequest is incorrectly interpreted as soon as the "setFilterExpression" is executed.
The result value of print featReq.filterType() befor the "setFilterExpression" is "0" and afterwards "3".
As I understand it, "3" is FilterType::FilterExpression, but the SQL Query is constructed as if the FilterType was "FilterRect" or something.

You can test this behavior by loading an MSSQL Layer in qgis and pasting following python code in the console:
(insert correct layername and filterexpression)

LegInt = iface.legendInterface()
layers = LegInt.layers()
for layer in layers:
    layerType = layer.type()
    if layer.name() == "testpolygone":
        my_mssql_layer=layer
        break

featReq=QgsFeatureRequest()
featReq=featReq.setFilterExpression ('users>1')
my_feature_iterator = my_mssql_layer.getFeatures( featReq )

for feature in my_feature_iterator:
    attrs = feature.attributes()
        print "Feature ID: " + str(feature.id()) + " " + str(attrs)
    break

I tested it in 2.2 and 2.4 on Windows.

I'm not shure which Category is correct for this issue. Perhaps rather "PyQGIS Console" ???

Associated revisions

Revision 7fe45db7
Added by Jürgen Fischer almost 6 years ago

mssql provider: fix filter type interpretation (fixes #11076)

History

#1 Updated by Stefan Loehr almost 6 years ago

  • Assignee set to Matthias Kuhn

The issue seems somehow related to #10900

#2 Updated by Jürgen Fischer almost 6 years ago

  • Assignee deleted (Matthias Kuhn)

#3 Updated by Jürgen Fischer almost 6 years ago

  • Status changed from Open to Closed

#4 Updated by Jürgen Fischer almost 6 years ago

  • Category changed from Data Provider to Data Provider/MSSQL

Also available in: Atom PDF