SQLServerSpatial removes parenthesis in filters

[beginbenoit]

The SQLServerSpatial provider removes parenthesis that are not used for an IN clause. This causes major issues with the actual clause logic not being applied properly if you use any OR operators.

For exemple, if you set a filter such as "(ColumnA = '1' OR ColumnA = '2') AND ColumnB = '3'", the resulting filter will not have the parenthesis and the logic becomes completely broken.

This creates an issue in software such as Mapguide when you use the API. If you also apply a spatial filter. Without any parenthesis anywhere, the query does not respect the logic.

To validate that it is a provider specific issue, I tested the same filter and API manipulation against the SDF provider and it returns the proper records for my filter logic.

To reproduce the issue, I simply used FDO Toolbox 1.2.0.1267 and ran the same query on an SQL Server table and an SDF. The query returns the proper results for the SDF provider, but the SQL Server provider does not.

I also confirmed using the API directly. I create an ISelect on an SQL Server provider, set the filter and then inspect the filter via debugging and notice my parenthesis have dissapeared.

[jng]

Is this related to #864 in any way?

[beginbenoit]

Replying to jng:

Is this related to #864 in any way?

I did not believe so since the issue didn't stem from actually putting a filter with parenthesis. The issue was that to improve performance, parenthesis should automatically be put when having both an alphanumeric filter and a spatial filter. In the bug report the filter did not have any parenthesis.

Perhaps in the underlying code the change fixes both issues, I'm not familiar enough with the source code to validate.

[jng]

Just as a little test, if you flip the two operands around, does the ColumnA = '1' OR ColumnA = '2' operand remain bracketed?

If so, I think I have the fix in place as part of the fix for #864

[jng]

Try these providers and see if these providers fix your issue.

32-bit: ​http://download.osgeo.org/mapguide/patches/fdo3.9_ticket864/SQLServerSpatialProvider_x86_ticket_864_and_889.zip

64-bit: ​http://download.osgeo.org/mapguide/patches/fdo3.9_ticket864/SQLServerSpatialProvider_x64_ticket_864_and_889.zip

If you're using FDO Toolbox, you must use the 1.3 release.

[beginbenoit]

Replying to jng:

Try these providers and see if these providers fix your issue.

32-bit: ​http://download.osgeo.org/mapguide/patches/fdo3.9_ticket864/SQLServerSpatialProvider_x86_ticket_864_and_889.zip

64-bit: ​http://download.osgeo.org/mapguide/patches/fdo3.9_ticket864/SQLServerSpatialProvider_x64_ticket_864_and_889.zip

If you're using FDO Toolbox, you must use the 1.3 release.

Tested it against the 1.3 release of FDO Toolbox and the patched version does indeed work. However, I traced the SQL Server database and noticed it now seems to still change the filter syntax a little bit.

So my filter was modified to be (ColumnA = '1' OR ColumnA = '2') AND (ColumnB = '3'). It respects the logic and fixes the issue.

Sorry for the extra ticket on the issue, I was under the impression it was a different issue. Cheers!

[jng]

It is a different issue, I just caught it while fixing the other one and it happened to be in the same method I was fixing up.

Fixed trunk (r7089) and 2.6 (r7090)