When styling a postgis layer with sld mapserver does bad casting of data (6.4.2)

[barryWarnock]

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.

[sdlime]

What version are you using? --Steve

[barryWarnock]

We are on version 6.4.2, sorry I should have included that in the issue

[tbonfort]

@barryWarnock can you report if the issue is still present in 7.0? If not, I suspect there's little chance of it being fixed inside the 6.4 branch unless you fund or look into it yourself.

[barryWarnock]

After updating to 7.0 the words = '142_na text' request is now properly interpreted as a string but the filter:

<ogc:Filter><ogc:PropertyIsEqualTo>
<ogc:PropertyName>words</ogc:PropertyName>
<ogc:Literal>4</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>

is now converting the 4 to a float producing the query:

select "words",encode(ST_AsBinary(ST_Force2D("wkb_geometry"),'NDR'),'hex') as geom,"ogc_fid" from z155f1a72fef4fb_types where wkb_geometry && ST_GeomFromText('POLYGON((3956364.52822967 3799323.09848486,3956364.52822967 5274852.04209296,5402120.21143023 5274852.04209296,5402120.21143023 3799323.09848486,3956364.52822967 3799323.09848486))',find_srid('','z155f1a72fef4fb_types','wkb_geometry')) and (((("words" = 4.000000))))

so 7.0 stopped treating a string like a number if its first character was a digit but strings that are exclusively made up of digits seem to be treated as floats now.

[sdlime]

I’ll be taking a look at this. What is the data type of the words column in this case – we probably have to look at the column metadata to know how to handle this ambiguous case.

From: barryWarnock [mailto:notifications@github.com]
Sent: Thursday, September 10, 2015 10:59 AM
To: mapserver/mapserver mapserver@noreply.github.com
Cc: Lime, Steve D (MNIT) Steve.Lime@state.mn.us
Subject: Re: [mapserver] When styling a postgis layer with sld mapserver does bad casting of data (6.4.2) (#5154)

After updating to 7.0 the words = '142_na text' request is now properly interpreted as a string but the filter:

ogc:Filterogc:PropertyIsEqualTo

ogc:PropertyNamewords/ogc:PropertyName

ogc:Literal4/ogc:Literal

/ogc:PropertyIsEqualTo

/ogc:Filter

is now converting the 4 to a float producing the query:

select "words",encode(ST_AsBinary(ST_Force2D("wkb_geometry"),'NDR'),'hex') as geom,"ogc_fid" from z155f1a72fef4fb_types where wkb_geometry && ST_GeomFromText('POLYGON((3956364.52822967 3799323.09848486,3956364.52822967 5274852.04209296,5402120.21143023 5274852.04209296,5402120.21143023 3799323.09848486,3956364.52822967 3799323.09848486))',find_srid(<

span class="pl-s">'','z155f1a72fef4fb_types','wkb_geometry')) and (((("words" = 4.000000))))

so 7.0 stopped treating a string like a number if its first character was a digit but strings that are exclusively made up of digits seem to be treated as floats now.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/5154#issuecomment-139290798.

[barryWarnock]

The column type is text.

[barryWarnock]

Has anything been found relating to this issue?

[barryWarnock]

I hate to bump but has there been any progress on this issue?

[sdlime]

No progress but thank you for the reminder. I will do my best to spend a bit of time on it this week.

[sdlime]

What happens if you set an explicit type on the literal?

<ogc:Literal type="xsd:string">4</ogc:Literal>

Steve

[kartoffelfee]

seems not to be fixed in 7.2 beta?
My Workaround: using PropertyIsLike