Hi, CQL does not have any relationship with SQL, other than a similar syntax, what happens is that it gets parsed into an in-memory representation that matches 1-1 the OGC Filter Encoding specification. If you check it, you'll find it has no notion of a IN operator, so the expression is turned into a chained equality of ORs. If the datastore behind it happens to be a relational database, eventually this in memory representation becomes a SQL where clause.
However, since https://osgeo-org.atlassian.net/browse/GEOT-6273 the code should be attempting to recompose it into a "IN" clause for SQL, and we have deploys in which it does. GeoServer 2.16.0 contains that change. I can't remember though if that requires the column to be non nullable, or not... and here comes the "IS NOT NULL" part. The filters are, as we said, OGC filters. OGC filter has a two valued logic, filters are either true or false. When encoding them into a database query, this behavior must be preserved... but databases have a 3-valued logic (see https://modern-sql.com/concept/three-valued-logic), which causes them to return different matches than OGC filters when there are nulls, breaking OGC compliance. So there is code that adds those "is not null" to make the generated SQL behave like the OGC filter. This part of code has also been updated to try to force an encoding of type "a in (v1, v2, v3) and a is not null" when GEOT-6273 has been fixed. I'm surprised you're still finding the behavior described in your mail in 2.16.x, it may be that it's related to a particular combination of table structure and/or data types? Unsure. If you are interested in investigating it further, let me know and I'll give you pointers into the source code Cheers Andrea On Wed, Dec 11, 2019 at 8:46 AM P O'Toole <p.oto...@uwyo.edu> wrote: > 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%20*COLUMN1*=*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 <http://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 > -- Regards, Andrea Aime == GeoServer Professional Services from the experts! Visit http://goo.gl/it488V for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions S.A.S. Via di Montramito 3/A 55054 Massarosa (LU) phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 http://www.geo-solutions.it http://twitter.com/geosolutions_it ------------------------------------------------------- *Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail.*
_______________________________________________ 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
