cql_filter with strToUpperCase() fails with "Cannot do natural order without a primary key"

Question

I'm trying to convert a query into uppercase in a GeoServer WFS request. According to the Filter Function reference this can be achieved using strToUpperCase().

This is working successfully on a sample GeoServer site where both of these queries return the expected result "STATE_NAME": "Illinois"

• CQL_FILTER=STATE_NAME='Illinois'
• CQL_FILTER=strToUpperCase(STATE_NAME)='ILLINOIS'

However, on my own GeoServer (version 2.10.2), an equivalent cql_filter (against a text field) works correctly without strToUpperCase:

http://apollo:8080/geoserver/loceng/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=loceng:rv_claims_lodgement_geo&CQL_FILTER=locality%3D%27Rozelle%27

... but fails with the following error when I include the strToUpperCase filter function:

http://apollo:8080/geoserver/loceng/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=loceng:rv_claims_lodgement_geo&CQL_FILTER=strToUpperCase(locality)%3D%27ROZELLE%27

<ServiceException>
   java.lang.RuntimeException: java.io.IOException java.io.IOException
   Cannot do natural order without a primary key, please add it or
   specify a manual sort over existing attributes
</ServiceException>

The layer's data source is a PostgreSQL table with the field defined as character varying, and there is an ascending index on that field.

Other layers in my local GeoServer, which are sourced from the same PostgreSQL instance, do not exhibit this error when I use strToUpperCase so the problem appears to be isolated to this layer/PostgreSQL table.

How can I debug this problem and allow the use of strToUpperCase on this dataset?

Answer 1

It turns out there were two problems:

• firstly I needed to add the Primary Key, as mentioned in the GeoServer error message
• the second problem was with the character varying field definition in PostgreSQL. I changed this to text and GeoServer's strToUpperCase() now works correctly.

Answer 2

Why would you even use strToUpperCase there, if you just need case insensitive comparison, would it be easier to use strEqualsIgnoreCase from the docs you linked

strEqualsIgnoreCase a:String, b:String  Returns true if the two strings are equal ignoring case considerations