SQL encoding of PropertyIsEqualTo adds an unecessary "AND attribute IS NOT NULL"
Description
Environment
Attachments
Activity
You are right, the above method is not a perfect workaround. I will try instead to create a new Filter Class where I will have 2 Filter Objects. The original One and the Not Null One. And return an AND Filter Object of both, in the guardAgainstNulls method.
Eh, I wish I remember the details, but I don't (I will have no time to look into it for a while). Is the code still passing all the tests (run them in the gt-jdbc-h2 module)? Did you add any new?
If so, you can try to make your own pull request according to https://github.com/geotools/geotools/blob/master/CONTRIBUTING.md
That said, collecting information in a field seems problematic, what if there are multiple nested logical operations? The visit will accumulate state and start crossing outer filters with innner ones... does not sound like something desirable.
Hi,
I have pulled out the geotools code and had a look in the method NullHandlingVisitor::guardAgainstNulls. The class was commited with the pull request: https://github.com/geotools/geotools/pull/358.
I have modified a little the code and tried to keep all the "Property IS NOT NULL Filter" , in a common AND condition: Here are my changes:
class NullHandlingVisitor extends DuplicatingFilterVisitor {
private FeatureType schema;
private And notIsNullAllFilter;
.....
private Filter guardAgainstNulls(Filter filter, Expression potentialPropertyName) {
if (potentialPropertyName instanceof PropertyName) {
PropertyName pn = (PropertyName) potentialPropertyName;
String name = pn.getPropertyName();
if (isNillable(name)) {
Not notNull = ff.not(ff.isNull(ff.property(name)));
if(notIsNullAllFilter == null ) {
notIsNullAllFilter = ff.and(Arrays.asList(new Filter[]{notNull}));
if (filter instanceof And) {
And and = (And) filter;
List<Filter> children = new ArrayList<Filter>(and.getChildren());
children.add(notIsNullAllFilter);
return ff.and(children);
} else {
return ff.and(filter, notIsNullAllFilter);
}
}
else{
List<Filter> children = notIsNullAllFilter.getChildren();
children.add(notNull);
}
}
}
return filter;
}Do you think my code will work? I do not know all the implications.
Regards,
Sorin
Hi,
In order to code the issue I have to understand very well the side effects of the changing. I will try to look onto it. For the moment I have fixed the issue by using a Comparation Function: "in"
Thank you,
Sorin
It does not indeed. I believe that's a side effect of some work I did years ago to handle the difference between OGC two state logic and SQL three state logic (but I'm not sure, it's just a guess):
http://osgeo-org.1560.x6.nabble.com/Fun-with-filter-negation-in-sql-stores-td5101506.html
So, someone needs to rework the encoding in order to get back the performance for the simple case, but avoid the error for the negation.
If this is a matter of urgency for you I suggest you propose a pull request yourself, making sure all tests are still passing after the change, or look for commercial support in order to get this improved. Links:
Process for contributing youself: https://github.com/geotools/geotools/blob/master/CONTRIBUTING.md
Commercial support http://geoserver.org/support/
Hi
we access the WMS service by sending a request having a ogc:Filter. The Geoserver interprets the filter and creates SQL likes:
SELECT "gid",encode(ST_AsBinary(ST_Simplify(ST_Force2D("the_geom"), 488.15834624618293, true)),'base64') as "the_geom" FROM "public"."points_view" WHERE ("the_geom" && ST_GeomFromText('POLYGON ((309613.80322206434 5989409.96286553, 309613.80322206434 6289017.14787394, 1313683.0713944233 6289017.14787394, 1313683.0713944233 5989409.96286553, 309613.80322206434 5989409.96286553))', 3857) AND (("gid" = 39513 AND "gid" IS NOT NULL ) OR ("gid" = 39516 AND "gid" IS NOT NULL ) OR ("gid" = 39509 AND "gid" IS NOT NULL ) OR ("gid" = 39570 AND "gid" IS NOT NULL ) OR ("gid" = 39572 AND "gid" IS NOT NULL ) OR ("gid" = 39573 AND "gid" IS NOT NULL ) OR ("gid" = 39750 AND "gid" IS NOT NULL ) OR ("gid" = 40171 AND "gid" IS NOT NULL ) OR ("gid" = 40305 AND "gid" IS NOT NULL ) OR ("gid" = 40456 AND "gid" IS NOT NULL ) OR ….There are case where we have to wait 20 sec. for postgres to respond. With the "gid" IS NOT NULL part out the Query is 2 sec long.
Below is the SLD we send.
<ogc:Filter><ogc:Or><ogc:PropertyIsEqualTo><ogc:PropertyName>gid</ogc:PropertyName><ogc:Literal>39513</ogc:Literal></ogc:PropertyIsEqualTo><ogc:PropertyIsEqualTo><ogc:PropertyName>gid</ogc:PropertyName><ogc:Literal>39516</ogc:Literal></ogc:PropertyIsEqualTo><ogc:PropertyIsEqualTo><ogc:PropertyName>gid</ogc:PropertyName><ogc:Literal>39509</ogc:Literal></ogc:PropertyIsEqualTo><ogc:PropertyIsEqualTo><ogc:PropertyName>gid</ogc:PropertyName><ogc:Literal>89082</ogc:Literal></ogc:PropertyIsEqualTo><ogc:PropertyIsEqualTo><ogc:PropertyName>gid</ogc:PropertyName><ogc:Literal>89150</ogc:Literal></ogc:PropertyIsEqualTo></ogc:Or></ogc:Filter>Thank You,
Sorin