.NET framework error when enabling where clause in sql query

Question

I am facing a weird issue wherein on disabling/enabling certain condition in where clause, my Select query throws .net framework error.

Here is the CREATE table script.

Table test_classes:

CREATE TABLE [dbo].[test_classes]
(
    [CLASSID] [int] NOT NULL,
    [PARENTID] [int] NULL,
    [CATID] [int] NOT NULL,
    [CLASS_NAME] [nvarchar](255) NOT NULL,
    [ORIGINAL_NAME] [nvarchar](255) NULL,
    [GEOMETRY] [tinyint] NOT NULL,
    [READ_ONLY] [bit] NOT NULL,
    [DISPLAY_STYLES] [image] NULL,
    [FEATURE_COUNT] [int] NOT NULL,
    [TEMPOWNER] [int] NULL,
    [OPTIONS] [int] NOT NULL,
    [POLYGON_TYPE] [int] NULL,
    [CLASS_EXTRA] [nvarchar](1024) NULL,
    [MAPID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Table test_polygon:

CREATE TABLE [dbo].[test_polygon]
(
    [FID] [nvarchar](36) NOT NULL,
    [EXTENT_L] [float] NOT NULL,
    [EXTENT_T] [float] NOT NULL,
    [EXTENT_R] [float] NOT NULL,
    [EXTENT_B] [float] NOT NULL,
    [COORDINATES] [image] NULL,
    [CHAINS] [smallint] NOT NULL,
    [CLASSID] [int] NOT NULL,
    [SPATIAL_KEY] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Due to word limitation (due to image datatype), here is the INSERT input: GDrive SQL Link

SELECT SQL query:

select 
    Class_Name, FID, 
    geometry::STGeomFromWKB(b1+b2,0) as polygon, 
    Class_ID, Original_Name
from 
    (Select 
         cl.Class_Name, p.FID,
         substring(CAST(p.Coordinates AS varbinary(max)),1,1) as b1,
         substring(CAST(p.Coordinates AS varbinary(max)),3,999999) as b2,
         cl.ClassID as Class_ID,
         cl.Original_Name
     From       
         test_polygon p
     Inner Join 
         test_classes cl on cl.ClassID = p.ClassID) s_polygon
--where Class_ID = 215                  --Filter#1
--where Class_Name = 'L1_County'        --Filter#2

To note, Class_ID 215 represents 'L1_County' class_name.

Problem is, if you enable Filter#1, then the output is as expected. But when I only enable Filter#2 then the query fails with .NET Error.

Expected output :

Class_Name  FID               polygon       Class_ID    Original_Name
----------- ----------------  ------------- ----------- ------------------------
L1_County   Northamptonshire  <long value>  215         B8USR_4DB8184E88092424 

Error I get :

Msg 6522, Level 16, State 1, Line 4
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: 24119: The Polygon input is not valid because the start and end points of the exterior ring are not the same. Each ring of a polygon must have the same start and end points.

System.FormatException:
at Microsoft.SqlServer.Types.GeometryValidator.ValidatePolygonRing(Int32 iRing, Int32 cPoints, Double firstX, Double firstY, Double lastX, Double lastY)
at Microsoft.SqlServer.Types.Validator.Execute(Transition transition)
at Microsoft.SqlServer.Types.ForwardingGeoDataSink.EndFigure()
at Microsoft.SqlServer.Types.WellKnownBinaryReader.ReadLineStringPoints(ByteOrder byteOrder, UInt32 cPoints, Boolean readZ, Boolean readM)
at Microsoft.SqlServer.Types.WellKnownBinaryReader.ReadLinearRing(ByteOrder byteOrder, Boolean readZ, Boolean readM)
at Microsoft.SqlServer.Types.WellKnownBinaryReader.ParseWkbPolygonWithoutHeader(ByteOrder byteOrder, Boolean readZ, Boolean readM)
at Microsoft.SqlServer.Types.WellKnownBinaryReader.ParseWkb(OpenGisType> type) > at Microsoft.SqlServer.Types.WellKnownBinaryReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromBinary(OpenGisType type, SqlBytes binary, Int32 srid) .

What I am trying to ask is, Why do I get error when WHERE clause has Class_Name and not when Class_ID.

I am using SQL Server 2012 Enterprise edition. Error replicates in SQL Server 2008 as well.

edit:

Estimated Execution plan for Filter#1 :

Estimated Execution plan for Filter#2 :

Answer 1

I will summarise comments:

You are seeing this issue because your table contains invalid data. The reason you do not see it when searching by test_polygon.Class_ID is that Class_ID is passed as a predicate to the table scan. When test_classes.Class_Name is used as filter the search predicate is applied to test_classes table. Since geometry::STGeomFromWKB "Compute Scalar" happens before "Join" it causes all rows of test_polygon to be evaluated by this function, including rows containing invalid data.

Update: Even though the plans look the same, they are not, as predicate conditions are different for different filters (WHERE conditions) and therefore outputs of table scans operators are different.

The is no standard way to force the order of evaluation in SQL Server query as by design you are not supposed to.

There are two options:

1. Materialise (store in a table) the result of the sub-query. This, simply, splits the query into two separate queries, one to find records and the second query to compute data on the found results. The intermediate results are stored in a (temp) table.
2. Use "hacks" that allow you to coerce SQL Server to evaluate query a certain way.

Below is an example of a "hack":

select 
    Class_Name, FID, 
    CASE WHEN Class_Name = Class_Name THEN geometry::STGeomFromWKB(b1+b2,0) ELSE NULL END as polygon,
    Class_ID, Original_Name
from 
    (Select 
         cl.Class_Name, p.FID,
         substring(CAST(p.Coordinates AS varbinary(max)),1,1) as b1,
         substring(CAST(p.Coordinates AS varbinary(max)),3,999999) as b2,
         cl.ClassID as Class_ID,
         cl.Original_Name
     From       
         test_polygon p
     Inner Join 
         test_classes cl on cl.ClassID = p.ClassID) s_polygon
--where Class_ID = 215                  --Filter#1
where Class_Name = 'L1_County'        --Filter#2

By adding a dummy CASE expression that looks at test_classes.Class_Name we are forcing SQL Server to evaluate it after the JOIN has been resolved.

The plan:

Useful Article: http://dataeducation.com/cursors-run-just-fine/