Opened 5 years ago

Closed 4 years ago

#864 closed defect (fixed)

BUG in SQL Server Spatial FDO provider when using filter

Reported by: sreckol Owned by: danstoica
Priority: critical Milestone: 3.8.0
Component: SQLServer Spatial Version: 3.8.0
Severity: 3 Keywords: MSSQL, PostGis Filter
Cc: sreckol External ID:

Description

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

Attachments (4)

864.patch (942 bytes) - added by jng 4 years ago.
Proposed patch
864v2.patch (4.2 KB) - added by jng 4 years ago.
Proposed patch v2
864_sqlserver.patch (2.3 KB) - added by jng 4 years ago.
Patch for SQL Server provider
864_sqlserver_v2.patch (2.2 KB) - added by jng 4 years ago.
Patch to roll in fix for #889

Download all attachments as: .zip

Change History (8)

Changed 4 years ago by jng

Attachment: 864.patch added

Proposed patch

Changed 4 years ago by jng

Attachment: 864v2.patch added

Proposed patch v2

comment:1 Changed 4 years ago by jng

Resolution: fixed
Status: newclosed

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

comment:2 Changed 4 years ago by sreckol

Cc: sreckol added
Resolution: fixed
Status: closedreopened

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

comment:3 Changed 4 years ago by 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.

Changed 4 years ago by jng

Attachment: 864_sqlserver.patch added

Patch for SQL Server provider

Changed 4 years ago by jng

Attachment: 864_sqlserver_v2.patch added

Patch to roll in fix for #889

comment:4 Changed 4 years ago by jng

Resolution: fixed
Status: reopenedclosed

Fixed trunk (r7089) and 3.9 (r7090)

Note: See TracTickets for help on using tickets.