problem with detecting column type from contents

[bartvde]

When a string column has number like values, Mapserver will create an invalid query SQL:

msWFSGetFeature(): WFS server error. FLTApplyFilterToLayer() failed
msSDELayerNextShape(): SDE error. SE_stream_fetch(): Underlying DBMS error. (-51)
msEvalExpression: Expression parser error. Failed to parse expression:  (404 < 100)  Or  (N618 = 004
) 
msyyparse(): Expression parser error. syntax error
msEvalExpression: Expression parser error. Failed to parse expression:  (407 < 100)  Or  (N624 = 004

[dmorissette]

Adding hobu to CC since this seems to be SDE related.

Bart: can you reproduce this same error with PostGIS or something more easily accessible than SDE?

[bartvde]

Hi Daniel, this is not related to the SDE backend, it's the OGC filter stuff (mapogcfilter.c) that has a check for a type based on the contents of the string in FLTGetMapserverExpression.

                tokens = msStringSplit(psFilterNode->pszValue,',', &nTokens);
                if (tokens && nTokens > 0)
                {
                    for (i=0; i<nTokens; i++)
                    {   
                        if (i == 0)
                        {
                            pszTmp = tokens[0];
                            nLength = strlen(pszTmp);
                            for (j=0; j<nLength; j++)
                            {
                                if (!isdigit(pszTmp[j]) &&  pszTmp[j] != '.')
                                {
                                    bString = 1;
                                    break;
                                }
                            }
                        } 

[dmorissette]

ok, I see. This one if for Assefa then. Reassigning.

[bartvde]

I think I used the wrong code fragment above, it's more about this part:

/* -------------------------------------------------------------------- */
/*      check if the value is a numeric value or alphanumeric. If it    */
/*      is alphanumeric, add quotes around attribute and values.        */
/* -------------------------------------------------------------------- */
    bString = 0;
    if (psFilterNode->psRightNode->pszValue)
    {
        nLenght = strlen(psFilterNode->psRightNode->pszValue);
        for (i=0; i<nLenght; i++)
        {
            if (!isdigit(psFilterNode->psRightNode->pszValue[i]) &&
                psFilterNode->psRightNode->pszValue[i] != '.')
            {
                /* if (psFilterNode->psRightNode->pszValue[i] < '0' || */
                /* psFilterNode->psRightNode->pszValue[i] > '9') */
                bString = 1;
                break;
            }
        }
    }

[bartvde]

preliminary patch to use _type METADATA

[bartvde]

Btw, Trac preview only shows first part of the patch! Not the part in mapogcfilter.c. Not sure why.

[assefa]

committed in 5.4.x branch r9135, r9139. field type matada ows/wfs/gml_[item name]_type is used if available.

committed in main trunk r9140

Give it a try and let me know.

[bartvde]

Related to this I have opened up an enhancement ticket (ticket:3229) to read the column types from the database (when having database sources), so that this will go okay automatically.

[bartvde]

I cannot get this to work on trunk. I am trying a query like:

<wfs:GetFeature xmlns:wfs="http://www.opengis.net/wfs" service="WFS" version="1.1.0" maxFeatures="100" xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><wfs:Query typeName="feature:AAA64" srsName="EPSG:28992" xmlns:feature="http://mapserver.gis.umn.edu/mapserver"><wfs:PropertyName>OBJECTID</wfs:PropertyName><wfs:PropertyName>ROUTE</wfs:PropertyName><wfs:PropertyName>ROUTE_CH</wfs:PropertyName><wfs:PropertyName>COUNT</wfs:PropertyName><wfs:PropertyName>BEHEERDER</wfs:PropertyName><wfs:PropertyName>LENGTH</wfs:PropertyName><wfs:PropertyName>SE_ANNO_CAD_DATA</wfs:PropertyName><wfs:PropertyName>SHAPE</wfs:PropertyName><ogc:Filter xmlns:ogc="http://www.opengis.net/ogc"><ogc:PropertyIsEqualTo matchCase="true"><ogc:PropertyName>ROUTE</ogc:PropertyName><ogc:Literal>002</ogc:Literal></ogc:PropertyIsEqualTo></ogc:Filter></wfs:Query></wfs:GetFeature>

where ROUTE is a character column, so I have specified:

"GML_ROUTE_TYPE" "Character"

but I am still getting:

<?xml version="1.0" encoding="ISO-8859-1"?>
<ows:ExceptionReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ows="http://www.opengis.net/ows" version="1.1.0" language="en-US" xsi:schemaLocation="http://www.opengis.net/ows http://schemas.opengis.net/ows/1.0.0/owsExceptionReport.xsd">
  <ows:Exception exceptionCode="mapserv" locator="NoApplicableCode">
    <ows:ExceptionText>msWFSGetFeature(): WFS server error. FLTApplyFilterToLayer() failed
msSDELayerNextShape(): SDE error. SE_stream_fetch(): Underlying DBMS error. (-51)</ows:ExceptionText>
  </ows:Exception>
</ows:ExceptionReport>

[assefa]

Bart,

I have added a debug output (saving the map just after the filter is applied) r9612 : you need to set the debug and log and web image path properly. In my case I have: CONFIG "MS_ERRORFILE" "f:/projects/mapserver-buildkit-2008/mapserver/msautotest/wxs/wfs_filter_mine.log"

DEBUG 5

.. WEB

IMAGEPATH "f:/tmp/ms_tmp/"

...

and I get in the log: [Thu Dec 10 14:13:07 2009].815000 FLTApplySimpleSQLFilter(): Map file after Filter was applied f:/tmp/ms_tmp/4b214843_221c_0._filter.map

This allows to see what was the exact filter that was set in the map file.

I also saw an issue which looks similar query that you reported using the oracle driver. It did happen when propertynames where set in the query. I have also fixed that in r9612.

Can you quickly test the same requests *without* the property names set:

<wfs:GetFeature xmlns:wfs="http://www.opengis.net/wfs" service="WFS" version="1.1.0" maxFeatures="100" xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><wfs:Query typeName="feature:AAA64" srsName="EPSG:28992" xmlns:feature="http://mapserver.gis.umn.edu/mapserver"><ogc:Filter xmlns:ogc="http://www.opengis.net/ogc"><ogc:PropertyIsEqualTo matchCase="true"><ogc:PropertyName>ROUTE</ogc:PropertyName><ogc:Literal>002</ogc:Literal></ogc:PropertyIsEqualTo></ogc:Filter></wfs:Query></wfs:GetFeature>

If possible can you also upgrade with this latest addition and see what the filter looks like?

[bartvde]

I've retested and it seems I added the METADATA to the wrong layer, duh, so now it works fine.

[assefa]

note that this had an issue with filters included in the sld. It is fixed in trunk r9911 and in the 5.6 branch r9912

[assefa]

Fix for trunk is 9913

[dmorissette]

Assefa, about the saving of the mapfile after applying SLD that was added in r9612 (comment:9), I don't think it's safe to write the mapfile to IMAGEPATH which is a web-accessible directory, and even if that feature is enabled only with DEBUG==5, users may not realize the risk and may end up exposing passwords or other important info by accident.

I'd suggest you write the file to a safer temporary location, either /tmp on Unix/Linux, or C:\TEMP on Windows, and simply fail or skip the save step if the path doesn't exist or is not writable). I see in the code that on Windows you default to C:\, but I'm not a fan of writing to C:\ and think a sub-directory should be used instead.

I think ultimately we need to use a real temp directory in MapServer for stuff like that... I have created ticket #3354 to discuss that.

[assefa]

I agree. I have entered a bug on it #3355 related to OGC chnmages that are needed. If the #3354 is implemented, I will use that. If not I will just use either /tmp or c:/temp