We are trying to style a layer stored in a postgis database using slds but when mapserver is generating the query for postgis it is improperly casting literal types.
for example with the data:
words (string) | numbers (int) | floats (floating point numbers)
part of canada | 1 | 1.0
142_na text | 66 | 6.6
4 | 3 | 3.0
text1 | 423 | 42.3
when we try to get the feature where words = 'part of canada' with the sld filter:
<ogc:Filter><ogc:PropertyIsEqualTo>
<ogc:PropertyName>words</ogc:PropertyName>
<ogc:Literal>part of canada</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>
it works fine but if we try to get the feature where words = '142_na text' with the sld filter:
<ogc:Filter><ogc:PropertyIsEqualTo>
<ogc:PropertyName>words</ogc:PropertyName>
<ogc:Literal>142_na text</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>
it fails with the error :
msDrawMap(): Image handling error. Failed to draw layer named 'z155df3786583f6_types'. msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR: syntax error at or near "_na" LINE 1: ...583f6_types','wkb_geometry')) and (( ("words"= 142_na text) ... ^
the full database query from the Mapserver log for this layer:
select "words",encode(ST_AsBinary(ST_Force2D("wkb_geometry"),'NDR'),'hex') as geom,"ogc_fid" from z155df3786583f6_types where wkb_geometry && ST_GeomFromText('POLYGON((2812450.22856771 4291961.15168331,2812450.22856771 6297949.81882978,4240795.86837761 6297949.81882978,4240795.86837761 4291961.15168331,2812450.22856771 4291961.15168331))',find_srid('','z155df3786583f6_types','wkb_geometry')) and (( ("words"= 142_na text) ))It looks like mapserver just sees that the first character is a number and decides to leave quotes off the whole value even though both the row type and literal type are actually strings.
If we try to get the feature where words = '4' with the sld filter:
<ogc:Filter><ogc:PropertyIsEqualTo>
<ogc:PropertyName>words</ogc:PropertyName>
<ogc:Literal>4</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>
it fails with the error :
msDrawMap(): Image handling error. Failed to draw layer named 'z155df3786583f6_types'. msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR: operator does not exist: character varying = integer LINE 1: ...55df3786583f6_types','wkb_geometry')) and (( ("words"= 4) )) ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
As you can see this literal is also being treated as a number instead of a string but we get a different error because now instead of there being unquoted spaces postgres is now being asked to compare an int to a string.
We are trying to style a layer stored in a postgis database using slds but when mapserver is generating the query for postgis it is improperly casting literal types.
for example with the data:
words (string) | numbers (int) | floats (floating point numbers)
part of canada | 1 | 1.0
142_na text | 66 | 6.6
4 | 3 | 3.0
text1 | 423 | 42.3
when we try to get the feature where words = 'part of canada' with the sld filter:
it works fine but if we try to get the feature where words = '142_na text' with the sld filter:
it fails with the error :
msDrawMap(): Image handling error. Failed to draw layer named 'z155df3786583f6_types'. msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR: syntax error at or near "_na" LINE 1: ...583f6_types','wkb_geometry')) and (( ("words"= 142_na text) ... ^the full database query from the Mapserver log for this layer:
It looks like mapserver just sees that the first character is a number and decides to leave quotes off the whole value even though both the row type and literal type are actually strings.
If we try to get the feature where words = '4' with the sld filter:
it fails with the error :
msDrawMap(): Image handling error. Failed to draw layer named 'z155df3786583f6_types'. msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR: operator does not exist: character varying = integer LINE 1: ...55df3786583f6_types','wkb_geometry')) and (( ("words"= 4) )) ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.As you can see this literal is also being treated as a number instead of a string but we get a different error because now instead of there being unquoted spaces postgres is now being asked to compare an int to a string.