BUG in SQL Server Spatial FDO provider when using filter

[sreckol]

There is huge BUG in SQL Server Spatial FDO provider (and also in PostGIS provider) when using filter to Query Data.

When filter is present FDO creates SQL query, where filter text should be in brackets (especially when using OR in filter text), but it is not!?

This affect performance a lot when using large datatables and want to get subdataset. It affect MGOS a lot, which become unusable when filter is used on large tables.

Example: Filter: IMPORT_OB_ID=184 OR IMPORT_OB_ID=189

FDO generates SQL Query like this: SELECT A.IMPORT_OB_ID,A.SPATIAL_GEOMETRY FROM dbo.KAT_PD_GRAF AS A WHERE A.IMPORT_OB_ID=184 OR A.IMPORT_OB_ID=189 AND A.[SPATIAL_GEOMETRY].STIntersects(convert(geometry, @P1).STEnvelope())=1

Filter text should be in brackets: SELECT A.IMPORT_OB_ID,A.SPATIAL_GEOMETRY FROM dbo.KAT_PD_GRAF AS A WHERE (A.IMPORT_OB_ID=184 OR A.IMPORT_OB_ID=189) AND A.[SPATIAL_GEOMETRY].STIntersects(convert(geometry, @P1).STEnvelope())=1

Tested with MGOS 2.4 (FDO 3.7) and MGOS 2.5 (FDO 3.8).

[jng]

Proposed patch

[jng]

Proposed patch v2

[jng]

Fixed trunk (r6959), 3.8 (r6957) and 3.7 (r6958)

[sreckol]

There is still problem when selecting entities on map.

When zooming and panning SQL qurey is OK, but when select on map is used, then SQL query is still the same (without brackets in filter) and because of that everything hangs out on large table ...

[jng]

Ok, the original fix applied to the FdoIFilterProcessor implementation at the GenericRdbms level.

However, the SQL Server provider uses its own FdoIFilterProcessor implementation which does not have this logic.

The attached patch (864_sqlserver.patch) applies this bracketing logic.

[jng]

Patch for SQL Server provider

[jng]

Patch to roll in fix for #889

[jng]

Fixed trunk (r7089) and 3.9 (r7090)