Hi, all. I'm writing because of a behavior I've found with Geoserver 2.16's WFS+CQL/ECQL and Postgres 9.6.x. It also appears to affect Geoserver back to at least version 2.11.x.
ECQL's IN-operator -- at least when employed against (materialized) views -- seems to perform extra IS NOT NULL checks for each value rather than just using SQL's analogous IN-operator, as one might expect. I was wondering whether there's a reason for this (possibly due to issues with other SQL-platforms), and whether there's a way to have Geoserver use SQL's IN directly. Because of the grouping symbols (parentheses) used in the SQL sent to the database, the performance hit for more than a-dozen-or-so values is quite severe. The final expression received by the database takes the form: (columnName = value1 AND columnName IS NOT NULL) OR (columnName = value2 AND columnName IS NOT NULL) ... the same result also occurs under a regular equality-check: &CQL_Filter=COLUMN1=VALUE%20OR%20COLUMN1=VALUE2 I have tried enabling the "encode functions" checkbox for the datastores I tested against, but this does not appear to have an effect on how either CQL-formulation is translated to SQL. The structure of the request I'm sending is http://localhost:8080/geoserver/wfs?service=wfs&version=2.0.0&request=GetFeature&typeName=<layer_name>&CQL_Filter=<COLUMN>%20IN%20(<VALUE1>,<VALUE2>)&outputFormat=csv Any information on this is welcome. Thanks, - Patrick O'Toole Application Developer Wyoming Natural Diversity Database<uwyo.edu/wyndd> UW Berry Biodiversity Conservation Center Department 3381, 1000 E. University Av. Laramie, WY 82071 P: 307-766-3018
_______________________________________________ Geoserver-users mailing list Please make sure you read the following two resources before posting to this list: - Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/ - The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
