Sync map with Filter sometimes disables the layer

[offtherailz]

Sometimes CQL_FILTER generated are not valid. So, when the sync map and table is active, sometimes the invalid request makes the layer disable.
Example wrong filters:
empty:
sometimes with filter = 0.

() 

This(don't know what is wrong in this filter, single conditions work, together with AND generate error):

(strToLowerCase(NOME_LIVELLO) LIKE '%e%' AND ID='1')

[allyoucanmap]

I reproduced the (strToLowerCase(NOME_LIVELLO) LIKE '%e%' AND ID='1') making a new layer with id and CATEGORY fields

without the strToLowerCase filter, the response is correct
(id='1' AND CATEGORY LIKE '%E%')

the strToLowerCase filter issue doesn't happen with field different to id or ID

[aaime]

Just one note, using strToLowerCase ensures that the filter will be run in memory instead of making it hit an eventual database (it is not translated to SQL, there is no support for it in common JDBC data stores, and experimental support in postgis, but it has to be enabled in the store configuration). This might merit some discussion with Simone too.

[aaime]

Actually, that part of the filter at least... the filter is split into a form like "SUPPORTED and UNSUPPORTED", the supported part is sent to the database, the unsupported one is run in memory. So if you have say a bbox anded it will still hit the db, along with "ID='1'"

[MV88]

The problem is related to the fact that the crs used for CQL_FITLER must be the native one for that layer.
Hence we need to reproject the coordinates array of the spatial filter to the native crs.
In order to retrieve the native crs i would call a getCapabilites to the single layer when the feature grid is opened and store the value in the layer state, only if the sync tool is active.
Note that only the nativeCRS will be saved in the map configuration

@offtherailz what do you think about this solution?

[MV88]

There were other problems related on how the cql_filter was built in ol
for example the polygon was not containing the last vertix which is equal to the first one.

[MV88]

i've been able to replicate the issue related to the use of ID attribute in the cql filter.
this is the error geoserver gives me back:

<ServiceExceptionReport xmlns="http://www.opengis.net/ogc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="1.3.0" xsi:schemaLocation="http://www.opengis.net/ogc http://localhost:8081/geoserver-test/schemas/wms/1.3.0/exceptions_1_3_0.xsd?ms2-authkey=fa76ce0f-b8c2-4c30-8deb-3934c462493a">
<ServiceException>
Could not parse CQL filter list. Encountered "ID =" at line 1, column 95. Was expecting one of: "not" ... "include" ... "exclude" ... "(" ... "[" ... "id" ... "id" "in" ... "in" ... <IDENTIFIER> ... <DATE_TIME> ... <DATE> ... "-" ... <INTEGER_LITERAL> ... <FLOATING_LITERAL> ... <STRING_LITERAL> ... "true" ... "false" ... "point" ... "linestring" ... "polygon" ... "multipoint" ... "multilinestring" ... "multipolygon" ... "geometrycollection" ... "envelope" ... Parsing : (strToLowerCase(CATEGORIA) LIKE '%confe%' AND strToLowerCase(DENOMINAZIONE) LIKE '%borg%' AND ID = '8').
</ServiceException>
</ServiceExceptionReport>

another one is

Could not parse CQL filter list. Encountered "ID <" at line 1, column 47. Was expecting one of: "not" ... "include" ... "exclude" ... "(" ... "[" ... "id" ... "id" "in" ... "in" ... <IDENTIFIER> ... <DATE_TIME> ... <DATE> ... "-" ... <INTEGER_LITERAL> ... <FLOATING_LITERAL> ... <STRING_LITERAL> ... "true" ... "false" ... "point" ... "linestring" ... "polygon" ... "multipoint" ... "multilinestring" ... "multipolygon" ... "geometrycollection" ... "envelope" ... Parsing : (strToLowerCase(CATEGORIA) LIKE '%confe%' AND ID < '17').

is the ID attribute a reserved key ?