New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

WFS PostGIS: malformed SQL query with PropertyIsEqualTo on a Date column #5265

Closed
edigiacomo opened this Issue Mar 25, 2016 · 2 comments

Comments

Projects
None yet
2 participants
@edigiacomo
Contributor

edigiacomo commented Mar 25, 2016

Hello,
I have a PostGIS database with a table containing a Date column.

Using MapServer 7.0.1, a WFS GetFeature using PropertyIsEqualTo on the date column results in a malformed SQL query:

$ mapserv -nh QUERY_STRING="map=test.map&service=WFS&request=GetFeature&version=2.0.0&typename=test&filter=<ogc:Filter><ogc:PropertyIsEqualTo><ogc:PropertyName>datetime</ogc:PropertyName><ogc:Literal>2015-08-10T23:55:00</ogc:Literal></ogc:PropertyIsEqualTo></ogc:Filter>"
...
msPostGISLayerWhichShapes query: select "id","datetime",encode(ST_AsBinary(ST_Force2D("geometry"),'NDR'),'hex') as geom,"id" from test where geometry && ST_GeomFromText('POLYGON((-180 -90,-180 90,180 90,180 -90,-180 -90))',find_srid('','test','geometry')) and (("datetime" =  between date_trunc('second',timestamp '2015-08-10T23:55:00') and date_trunc('second',timestamp '2015-08-10T23:55:00') + interval '1 second' - interval '1 second'))
msPostGISLayerWhichShapes query status: PGRES_FATAL_ERROR (7)
msPostGISLayerWhichShapes(): Error (ERROR:  syntax error at or near "date_trunc"
LINE 1: ...','test','geometry')) and (("datetime" =  between date_trunc...
                                                             ^
) executing query: select "id","datetime",encode(ST_AsBinary(ST_Force2D("geometry"),'NDR'),'hex') as geom,"id" from test where geometry && ST_GeomFromText('POLYGON((-180 -90,-180 90,180 90,180 -90,-180 -90))',find_srid('','test','geometry')) and (("datetime" =  between date_trunc('second',timestamp '2015-08-10T23:55:00') and date_trunc('second',timestamp '2015-08-10T23:55:00') + interval '1 second' - interval '1 second'))
...

The query doesn't fail with PropertyIsGreaterThan or PropertyIsLessThan.

The problem is the = between in the SQL query (https://github.com/mapserver/mapserver/blob/branch-7-0/mappostgis.c#L3392). The functions related to PropertyIsGreaterThan or PropertyIsLessThan, don't fail because they don't append an operator, but only the value to ther SQL query (https://github.com/mapserver/mapserver/blob/branch-7-0/mappostgis.c#L3428 and https://github.com/mapserver/mapserver/blob/branch-7-0/mappostgis.c#L3480).

A solution could be to remove the = before, but I don't know the code well enough to write a PR.

MapServer version:

$ mapserv -v
MapServer version 7.0.1 OUTPUT=PNG OUTPUT=JPEG SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=ICONV SUPPORTS=FRIBIDI SUPPORTS=WMS_SERVER SUPPORTS=WFS_SERVER SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI SUPPORTS=GEOS INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE

Here the mapfile:

    NAME ""
    IMAGETYPE PNG
    STATUS ON
    EXTENT -180 -90 180 90
    SIZE 1024 768

    CONFIG "MS_ERRORFILE" "stderr"
    CONFIG "CPL_DEBUG" "ON"
    DEBUG 5

    PROJECTION
        "init=epsg:4326"
    END

    LAYER
        DEBUG 5
        NAME "test"
        PROJECTION
            "init=epsg:4326"
        END
        METADATA
           gml_types "auto"
           wfs_enable_request "*"
           gml_include_items "all"
           gml_featureid "id"
        END
        TYPE POLYGON
        CONNECTIONTYPE POSTGIS
        CONNECTION "dbname=NAME user=USER password=PASSWORD host=HOST"
        DATA 'geometry from test using unique id'
    END

END

edigiacomo added a commit to edigiacomo/mapserver that referenced this issue Mar 25, 2016

Fix #5265
In PostGIS filter, when there's a MS_TOKEN_LITERAL_TIME right after
MS_TOKEN_LITERAL_TIME, the first is ignored, because the right operator
("between") is set with the MS_TOKEN_LITERAL_TIME.
@edigiacomo

This comment has been minimized.

Show comment
Hide comment
@edigiacomo

edigiacomo Mar 25, 2016

Contributor

Studying the code, I noticed that the SQL is buggy with a MS_TOKEN_LITERAL_TIME:

[datetime] = `2015-01-01`

But with a MS_TOKEN_BINDING_TIME it's well formed:

`[datetime]` = `2015-01-01`

Maybe the issue could be related to a more generic scenario when a MS_TOKEN_LITERAL_TIME is used with a MS_TOKEN_COMPARISON_EQ operator.

Contributor

edigiacomo commented Mar 25, 2016

Studying the code, I noticed that the SQL is buggy with a MS_TOKEN_LITERAL_TIME:

[datetime] = `2015-01-01`

But with a MS_TOKEN_BINDING_TIME it's well formed:

`[datetime]` = `2015-01-01`

Maybe the issue could be related to a more generic scenario when a MS_TOKEN_LITERAL_TIME is used with a MS_TOKEN_COMPARISON_EQ operator.

edigiacomo added a commit to edigiacomo/mapserver that referenced this issue Mar 25, 2016

Fix #5265
In PostGIS filter, a MS_TOKEN_COMPARISON_EQ followed by a
MS_TOKEN_LITERAL_TIME is ignored, because the operator if set with the
MS_TOKEN_LITERAL_TIME.
@sdlime

This comment has been minimized.

Show comment
Hide comment
@sdlime

sdlime Mar 28, 2016

Member

Hi @edigiacomo, thanks for the patch! I'll review ASAP (been gone on spring break)... --Steve

Member

sdlime commented Mar 28, 2016

Hi @edigiacomo, thanks for the patch! I'll review ASAP (been gone on spring break)... --Steve

@sdlime sdlime closed this in #5266 Jun 17, 2016

sdlime added a commit that referenced this issue Jun 17, 2016

Merge pull request #5266 from edigiacomo/issues/5265
Fix filter of MS_TOKEN_LITERAL_TIME in PostGIS (#5265)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment