possible bug in Oracle-NG datastore

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

possible bug in Oracle-NG datastore

Stefano Iacovella
Dear all

Next week I upgraded a geoserver installation with the oracle-ng datastore.
The geoserver release is 1.7.7 and the oracle DBMS containing the data is a 10.2.0.4 running on Red Hat.
Apparently simultaneously a kind of WFS getFeature requests stopped functioning.

The WFS request is a sort of:

http://server/geoserver/wfs?request=GetFeature&version=1.1.0&typeName=cat:PARTICELLECATASTO&outputFormat=GML2&FILTER=<Filter><And><PropertyIsEqualTo><PropertyName>CODICECOMUNE</PropertyName><Literal>B354</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>FOGLIO</PropertyName><Literal>9</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>NUMERO</PropertyName><Literal>187</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>SEZIONE</PropertyName><Literal>A</Literal></PropertyIsEqualTo></And></Filter>

This kind of request does not return any feature, althought it should be return exactly one feature as it did previously the upgrade.

If I remove the number clause form the filter the request works and return a gml containing a set of feature.

The Oracle Table containing the data is :

Name                           Null     Type             
------------------------------ -------- -----------------
IDFEATURE                      NOT NULL NUMBER           
CODICECOMUNE                   NOT NULL CHAR(4)          
FOGLIO                         NOT NULL NUMBER(5)        
SEZIONE                        NOT NULL CHAR(1)          
NUMERO                         NOT NULL CHAR(5)          
QUALITACOD                              NUMBER(3)        
QUALITADESCR                            VARCHAR2(50)     
SUPERFICIENOMINALE                      NUMBER(12)       
SUPERFICIENOMPORZIONE                   NUMBER(12)       
AREA                                    NUMBER(12,2)     
ALLEGATO                                CHAR(1)          
SVILUPPO                                CHAR(1)          
POLIGONO                                SDO_GEOMETRY()   

I set the log to maximum level but can't find any error traced:

2009-12-01 17:12:49,296 DEBUG [geoserver.requests] - Query is net.opengis.wfs.impl.QueryTypeImpl@3cad55 (group: [], propertyName: [], function: null, filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]], sortBy: [], featureVersion: null, handle: null, srsName: null, typeName: [{<a href="http://www.regione.sardegna.it/}PARTICELLECATASTO">http://www.regione.sardegna.it/}PARTICELLECATASTO])
 To gt2: Query:
   feature type: PARTICELLECATASTO
   filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]]
   [properties:  ALL ]
2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - CREATE CONNECTION
2009-12-01 17:12:49,296 TRACE [geotools.core] - ENTRY 2
2009-12-01 17:12:49,296 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,296 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,296 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,296 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting LogicFilter
2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - SELECT count(*) AS gt_result_ FROM (SELECT * FROM (SELECT * FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM <= 2000) gt_limited_
2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - 1 = B354
2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - 2 = 9
2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - 3 = 187
2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - 4 = A
2009-12-01 17:12:49,312 DEBUG [geotools.jdbc] - CLOSE CONNECTION
2009-12-01 17:12:49,312 INFO [geoserver.wfs] -
Request: getFeature
    handle = null
    service = WFS
    version = 1.1.0
    baseUrl = http://10.2.51.73:8080/geoserver/
    providedVersion = 1.1.0
    query = [net.opengis.wfs.impl.QueryTypeImpl@3cad55 (group: null, propertyName: null, function: null, filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]], sortBy: null, featureVersion: null, handle: null, srsName: null, typeName: [{<a href="http://www.regione.sardegna.it/}PARTICELLECATASTO]">http://www.regione.sardegna.it/}PARTICELLECATASTO])]
    maxFeatures = null
    outputFormat = GML2
    resultType = results
    traverseXlinkDepth = null
    traverseXlinkExpiry = null
    formatOptions = {}
2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - CREATE CONNECTION
2009-12-01 17:12:49,328 TRACE [geotools.core] - ENTRY 2
2009-12-01 17:12:49,328 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,328 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,328 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,328 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting LogicFilter
2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - SELECT * FROM (SELECT IDFEATURE,CODICECOMUNE,FOGLIO,SEZIONE,NUMERO,QUALITACOD,QUALITADESCR,SUPERFICIENOMINALE,SUPERFICIENOMPORZIONE,AREA,ALLEGATO,SVILUPPO,POLIGONO as POLIGONO FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM <= 2000
2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - 1 = B354
2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - 2 = 9
2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - 3 = 187
2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - 4 = A
2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - CLOSE CONNECTION
2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - CREATE CONNECTION
2009-12-01 17:12:49,344 TRACE [geotools.core] - ENTRY 2
2009-12-01 17:12:49,344 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,344 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,344 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,344 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting LogicFilter
2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - SELECT * FROM (SELECT IDFEATURE,CODICECOMUNE,FOGLIO,SEZIONE,NUMERO,QUALITACOD,QUALITADESCR,SUPERFICIENOMINALE,SUPERFICIENOMPORZIONE,AREA,ALLEGATO,SVILUPPO,POLIGONO as POLIGONO FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM <= 2000
2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - 1 = B354
2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - 2 = 9
2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - 3 = 187
2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - 4 = A
2009-12-01 17:12:49,359 DEBUG [geotools.gml] - closing reader org.geoserver.security.decorators.ReadOnlyFeatureIterator@15c41ed
2009-12-01 17:12:49,359 DEBUG [geotools.jdbc] - CLOSE CONNECTION
2009-12-01 17:12:51,906 DEBUG [geoserver.filters] - filtering http://10.2.51.73:8080/geoserver/wfs
2009-12-01 17:12:51,906 DEBUG [geotools.xml] - No schemaLocation found, using 'http://www.opengis.net/ogc jar:file:/C:/opt/Tomcat6020/webapps/geoserver/WEB-INF/lib/gt-xsd-filter-2.5.8.jar!/org/geotools/filter/v1_0/filter.xsd
2009-12-01 17:12:51,906 DEBUG [geotools.xml] - Found override for http://www.opengis.net/ogc: jar:file:/C:/opt/Tomcat6020/webapps/geoserver/WEB-INF/lib/gt-xsd-filter-2.5.8.jar!/org/geotools/filter/v1_0/filter.xsd ==> jar:file:/C:/opt/Tomcat6020/webapps/geoserver/WEB-INF/lib/gt-xsd-filter-2.5.8.jar!/org/geotools/filter/v1_0/filter.xsd
2009-12-01 17:12:51,906 INFO [geoserver.wfs] -
Request: getServiceInfo
2009-12-01 17:12:51,906 DEBUG [geoserver.requests] - Query is net.opengis.wfs.impl.QueryTypeImpl@185ea80 (group: [], propertyName: [], function: null, filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]], sortBy: [], featureVersion: null, handle: null, srsName: null, typeName: [{<a href="http://www.regione.sardegna.it/}PARTICELLECATASTO">http://www.regione.sardegna.it/}PARTICELLECATASTO])
 To gt2: Query:
   feature type: PARTICELLECATASTO
   filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]]
   [properties:  ALL ]
2009-12-01 17:12:51,906 DEBUG [geotools.jdbc] - CREATE CONNECTION
2009-12-01 17:12:51,922 TRACE [geotools.core] - ENTRY 2
2009-12-01 17:12:51,922 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,922 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,922 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,922 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting LogicFilter
2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - SELECT count(*) AS gt_result_ FROM (SELECT * FROM (SELECT * FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM <= 2000) gt_limited_
2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - 1 = B354
2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - 2 = 9
2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - 3 = 187
2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - 4 = A
2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - CLOSE CONNECTION
2009-12-01 17:12:51,922 INFO [geoserver.wfs] -
Request: getFeature
    handle = null
    service = WFS
    version = 1.1.0
    baseUrl = http://10.2.51.73:8080/geoserver/
    providedVersion = 1.1.0
    query = [net.opengis.wfs.impl.QueryTypeImpl@185ea80 (group: null, propertyName: null, function: null, filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]], sortBy: null, featureVersion: null, handle: null, srsName: null, typeName: [{<a href="http://www.regione.sardegna.it/}PARTICELLECATASTO]">http://www.regione.sardegna.it/}PARTICELLECATASTO])]
    maxFeatures = null
    outputFormat = GML2
    resultType = results
    traverseXlinkDepth = null
    traverseXlinkExpiry = null
    formatOptions = {}
2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - CREATE CONNECTION
2009-12-01 17:12:51,938 TRACE [geotools.core] - ENTRY 2
2009-12-01 17:12:51,938 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,938 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,938 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,938 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting LogicFilter
2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - SELECT * FROM (SELECT IDFEATURE,CODICECOMUNE,FOGLIO,SEZIONE,NUMERO,QUALITACOD,QUALITADESCR,SUPERFICIENOMINALE,SUPERFICIENOMPORZIONE,AREA,ALLEGATO,SVILUPPO,POLIGONO as POLIGONO FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM <= 2000
2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - 1 = B354
2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - 2 = 9
2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - 3 = 187
2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - 4 = A
2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - CLOSE CONNECTION
2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - CREATE CONNECTION
2009-12-01 17:12:51,954 TRACE [geotools.core] - ENTRY 2
2009-12-01 17:12:51,954 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,954 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,954 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,954 TRACE [geotools.core] - ENTRY 14
2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting LogicFilter
2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting PropertyName
2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - SELECT * FROM (SELECT IDFEATURE,CODICECOMUNE,FOGLIO,SEZIONE,NUMERO,QUALITACOD,QUALITADESCR,SUPERFICIENOMINALE,SUPERFICIENOMPORZIONE,AREA,ALLEGATO,SVILUPPO,POLIGONO as POLIGONO FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM <= 2000
2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - 1 = B354
2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - 2 = 9
2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - 3 = 187
2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - 4 = A
2009-12-01 17:12:51,969 DEBUG [geotools.gml] - closing reader org.geoserver.security.decorators.ReadOnlyFeatureIterator@9a15b3
2009-12-01 17:12:51,969 DEBUG [geotools.jdbc] - CLOSE CONNECTION

Thank you for any hint

Stefano Iacovella

------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug in Oracle-NG datastore

aaime
Stefano Iacovella ha scritto:

> Dear all
>
> Next week I upgraded a geoserver installation with the oracle-ng datastore.
> The geoserver release is 1.7.7 and the oracle DBMS containing the data
> is a 10.2.0.4 running on Red Hat.
> Apparently simultaneously a kind of WFS getFeature requests stopped
> functioning.
>
> The WFS request is a sort of:
>
> http://server/geoserver/wfs?request=GetFeature&version=1.1.0&typeName=cat:PARTICELLECATASTO&outputFormat=GML2&FILTER= 
> <http://server/geoserver/wfs?request=GetFeature&version=1.1.0&typeName=cat:PARTICELLECATASTO&outputFormat=GML2&FILTER=><Filter><And><PropertyIsEqualTo><PropertyName>CODICECOMUNE</PropertyName><Literal>B354</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>FOGLIO</PropertyName><Literal>9</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>NUMERO</PropertyName><Literal>187</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>SEZIONE</PropertyName><Literal>A</Literal></PropertyIsEqualTo></And></Filter>
>
> This kind of request does not return any feature, althought it should be
> return exactly one feature as it did previously the upgrade.
>
> If I remove the number clause form the filter the request works and
> return a gml containing a set of feature.
>
> The Oracle Table containing the data is :
>
> Name                           Null     Type            
> ------------------------------ -------- -----------------
> IDFEATURE                      NOT NULL NUMBER          
> CODICECOMUNE                   NOT NULL CHAR(4)          
> FOGLIO                         NOT NULL NUMBER(5)        
> SEZIONE                        NOT NULL CHAR(1)          
> NUMERO                         NOT NULL CHAR(5)          

This might be the reason for the problem, but I'm not sure.
What might be happening is that the code is setting 9 as a
number instead of a char in the prepared statement params.

In theory this kind of error should not happen, there is
code trying to guess the type of the attribute the comparison
is using, but maybe you're hitting a case that's not well
covered. Can you narrow down your filter to a case where
the filter itself is minimal, but the code still misbehaves?

The old Oracle datastore was not affecetd because it is not
using prepared statements.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug in Oracle-NG datastore

Rahkonen Jukka (Tike)
Hi,

I remember that in the days of Geoserver 1.5.0 we had to create a fake column with leading letter character into database to handle this situation with VARCHAR(2) column filled always only with numbers.  I am pretty sure that we have not had this problem this year with 1.6, 1.7 and 2.0 series, but we are using only WFS 1.0.0.

-Jukka Rahkonen-

-----Alkuperäinen viesti-----
Lähettäjä: Andrea Aime [mailto:[hidden email]]
Lähetetty: ti 1.12.2009 19:11
Vastaanottaja: Stefano Iacovella
Kopio: [hidden email]
Aihe: Re: [Geoserver-users] possible bug in Oracle-NG datastore
 
Stefano Iacovella ha scritto:

> Dear all
>
> Next week I upgraded a geoserver installation with the oracle-ng datastore.
> The geoserver release is 1.7.7 and the oracle DBMS containing the data
> is a 10.2.0.4 running on Red Hat.
> Apparently simultaneously a kind of WFS getFeature requests stopped
> functioning.
>
> The WFS request is a sort of:
>
> http://server/geoserver/wfs?request=GetFeature&version=1.1.0&typeName=cat:PARTICELLECATASTO&outputFormat=GML2&FILTER= 
> <http://server/geoserver/wfs?request=GetFeature&version=1.1.0&typeName=cat:PARTICELLECATASTO&outputFormat=GML2&FILTER=><Filter><And><PropertyIsEqualTo><PropertyName>CODICECOMUNE</PropertyName><Literal>B354</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>FOGLIO</PropertyName><Literal>9</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>NUMERO</PropertyName><Literal>187</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>SEZIONE</PropertyName><Literal>A</Literal></PropertyIsEqualTo></And></Filter>
>
> This kind of request does not return any feature, althought it should be
> return exactly one feature as it did previously the upgrade.
>
> If I remove the number clause form the filter the request works and
> return a gml containing a set of feature.
>
> The Oracle Table containing the data is :
>
> Name                           Null     Type            
> ------------------------------ -------- -----------------
> IDFEATURE                      NOT NULL NUMBER          
> CODICECOMUNE                   NOT NULL CHAR(4)          
> FOGLIO                         NOT NULL NUMBER(5)        
> SEZIONE                        NOT NULL CHAR(1)          
> NUMERO                         NOT NULL CHAR(5)          

This might be the reason for the problem, but I'm not sure.
What might be happening is that the code is setting 9 as a
number instead of a char in the prepared statement params.

In theory this kind of error should not happen, there is
code trying to guess the type of the attribute the comparison
is using, but maybe you're hitting a case that's not well
covered. Can you narrow down your filter to a case where
the filter itself is minimal, but the code still misbehaves?

The old Oracle datastore was not affecetd because it is not
using prepared statements.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users





------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Excellent bug report

Ben Caradoc-Davies
In reply to this post by Stefano Iacovella
Stefano,

while I cannot help you with your bug, I would like to thank you for
your excellent bug report. The inclusion of the query, a description of
the result, the table definition, and debug logs including generated SQL
provide developers with just about everything they could ask for.

Users, this bug report by Stefano is exemplary. If you ever want a bug
fixed, a report of this high quality will give us the best chance of
fixing it.

Kind regards,
Ben.


On 02/12/09 00:54, Stefano Iacovella wrote:

> Dear all
>
> Next week I upgraded a geoserver installation with the oracle-ng datastore.
> The geoserver release is 1.7.7 and the oracle DBMS containing the data is a 10.2.0.4 running on Red Hat.
> Apparently simultaneously a kind of WFS getFeature requests stopped functioning.
>
> The WFS request is a sort of:
>
> http://server/geoserver/wfs?request=GetFeature&version=1.1.0&typeName=cat:PARTICELLECATASTO&outputFormat=GML2&FILTER=<Filter><And><PropertyIsEqualTo><PropertyName>CODICECOMUNE</PropertyName><Literal>B354</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>FOGLIO</PropertyName><Literal>9</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>NUMERO</PropertyName><Literal>187</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>SEZIONE</PropertyName><Literal>A</Literal></PropertyIsEqualTo></And></Filter>
>
> This kind of request does not return any feature, althought it should be return exactly one feature as it did previously the upgrade.
>
> If I remove the number clause form the filter the request works and return a gml containing a set of feature.
>
> The Oracle Table containing the data is :
>
> Name                           Null     Type
> ------------------------------ -------- -----------------
> IDFEATURE                      NOT NULL NUMBER
> CODICECOMUNE                   NOT NULL CHAR(4)
> FOGLIO                         NOT NULL NUMBER(5)
> SEZIONE                        NOT NULL CHAR(1)
> NUMERO                         NOT NULL CHAR(5)
> QUALITACOD                              NUMBER(3)
> QUALITADESCR                            VARCHAR2(50)
> SUPERFICIENOMINALE                      NUMBER(12)
> SUPERFICIENOMPORZIONE                   NUMBER(12)
> AREA                                    NUMBER(12,2)
> ALLEGATO                                CHAR(1)
> SVILUPPO                                CHAR(1)
> POLIGONO                                SDO_GEOMETRY()
>
> I set the log to maximum level but can't find any error traced:
>
> 2009-12-01 17:12:49,296 DEBUG [geoserver.requests] - Query is net.opengis.wfs.impl.QueryTypeImpl@3cad55 (group: [], propertyName: [], function: null, filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]], sortBy: [], featureVersion: null, handle: null, srsName: null, typeName: [{http://www.regione.sardegna.it/}PARTICELLECATASTO])
>   To gt2: Query:
>     feature type: PARTICELLECATASTO
>     filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]]
>     [properties:  ALL ]
> 2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - CREATE CONNECTION
> 2009-12-01 17:12:49,296 TRACE [geotools.core] - ENTRY 2
> 2009-12-01 17:12:49,296 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,296 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,296 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,296 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting LogicFilter
> 2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,296 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - SELECT count(*) AS gt_result_ FROM (SELECT * FROM (SELECT * FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM<= 2000) gt_limited_
> 2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - 1 = B354
> 2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - 2 = 9
> 2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - 3 = 187
> 2009-12-01 17:12:49,296 DEBUG [geotools.jdbc] - 4 = A
> 2009-12-01 17:12:49,312 DEBUG [geotools.jdbc] - CLOSE CONNECTION
> 2009-12-01 17:12:49,312 INFO [geoserver.wfs] -
> Request: getFeature
>      handle = null
>      service = WFS
>      version = 1.1.0
>      baseUrl = http://10.2.51.73:8080/geoserver/
>      providedVersion = 1.1.0
>      query = [net.opengis.wfs.impl.QueryTypeImpl@3cad55 (group: null, propertyName: null, function: null, filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]], sortBy: null, featureVersion: null, handle: null, srsName: null, typeName: [{http://www.regione.sardegna.it/}PARTICELLECATASTO])]
>      maxFeatures = null
>      outputFormat = GML2
>      resultType = results
>      traverseXlinkDepth = null
>      traverseXlinkExpiry = null
>      formatOptions = {}
> 2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - CREATE CONNECTION
> 2009-12-01 17:12:49,328 TRACE [geotools.core] - ENTRY 2
> 2009-12-01 17:12:49,328 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,328 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,328 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,328 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting LogicFilter
> 2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,328 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - SELECT * FROM (SELECT IDFEATURE,CODICECOMUNE,FOGLIO,SEZIONE,NUMERO,QUALITACOD,QUALITADESCR,SUPERFICIENOMINALE,SUPERFICIENOMPORZIONE,AREA,ALLEGATO,SVILUPPO,POLIGONO as POLIGONO FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM<= 2000
> 2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - 1 = B354
> 2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - 2 = 9
> 2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - 3 = 187
> 2009-12-01 17:12:49,328 DEBUG [geotools.jdbc] - 4 = A
> 2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - CLOSE CONNECTION
> 2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - CREATE CONNECTION
> 2009-12-01 17:12:49,344 TRACE [geotools.core] - ENTRY 2
> 2009-12-01 17:12:49,344 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,344 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,344 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,344 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting LogicFilter
> 2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:49,344 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - SELECT * FROM (SELECT IDFEATURE,CODICECOMUNE,FOGLIO,SEZIONE,NUMERO,QUALITACOD,QUALITADESCR,SUPERFICIENOMINALE,SUPERFICIENOMPORZIONE,AREA,ALLEGATO,SVILUPPO,POLIGONO as POLIGONO FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM<= 2000
> 2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - 1 = B354
> 2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - 2 = 9
> 2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - 3 = 187
> 2009-12-01 17:12:49,344 DEBUG [geotools.jdbc] - 4 = A
> 2009-12-01 17:12:49,359 DEBUG [geotools.gml] - closing reader org.geoserver.security.decorators.ReadOnlyFeatureIterator@15c41ed
> 2009-12-01 17:12:49,359 DEBUG [geotools.jdbc] - CLOSE CONNECTION
> 2009-12-01 17:12:51,906 DEBUG [geoserver.filters] - filtering http://10.2.51.73:8080/geoserver/wfs
> 2009-12-01 17:12:51,906 DEBUG [geotools.xml] - No schemaLocation found, using 'http://www.opengis.net/ogc jar:file:/C:/opt/Tomcat6020/webapps/geoserver/WEB-INF/lib/gt-xsd-filter-2.5.8.jar!/org/geotools/filter/v1_0/filter.xsd
> 2009-12-01 17:12:51,906 DEBUG [geotools.xml] - Found override for http://www.opengis.net/ogc: jar:file:/C:/opt/Tomcat6020/webapps/geoserver/WEB-INF/lib/gt-xsd-filter-2.5.8.jar!/org/geotools/filter/v1_0/filter.xsd ==>  jar:file:/C:/opt/Tomcat6020/webapps/geoserver/WEB-INF/lib/gt-xsd-filter-2.5.8.jar!/org/geotools/filter/v1_0/filter.xsd
> 2009-12-01 17:12:51,906 INFO [geoserver.wfs] -
> Request: getServiceInfo
> 2009-12-01 17:12:51,906 DEBUG [geoserver.requests] - Query is net.opengis.wfs.impl.QueryTypeImpl@185ea80 (group: [], propertyName: [], function: null, filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]], sortBy: [], featureVersion: null, handle: null, srsName: null, typeName: [{http://www.regione.sardegna.it/}PARTICELLECATASTO])
>   To gt2: Query:
>     feature type: PARTICELLECATASTO
>     filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]]
>     [properties:  ALL ]
> 2009-12-01 17:12:51,906 DEBUG [geotools.jdbc] - CREATE CONNECTION
> 2009-12-01 17:12:51,922 TRACE [geotools.core] - ENTRY 2
> 2009-12-01 17:12:51,922 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,922 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,922 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,922 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting LogicFilter
> 2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,922 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - SELECT count(*) AS gt_result_ FROM (SELECT * FROM (SELECT * FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM<= 2000) gt_limited_
> 2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - 1 = B354
> 2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - 2 = 9
> 2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - 3 = 187
> 2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - 4 = A
> 2009-12-01 17:12:51,922 DEBUG [geotools.jdbc] - CLOSE CONNECTION
> 2009-12-01 17:12:51,922 INFO [geoserver.wfs] -
> Request: getFeature
>      handle = null
>      service = WFS
>      version = 1.1.0
>      baseUrl = http://10.2.51.73:8080/geoserver/
>      providedVersion = 1.1.0
>      query = [net.opengis.wfs.impl.QueryTypeImpl@185ea80 (group: null, propertyName: null, function: null, filter: [[ CODICECOMUNE = B354 ] AND [ FOGLIO = 9 ] AND [ NUMERO = 187 ] AND [ SEZIONE = A ]], sortBy: null, featureVersion: null, handle: null, srsName: null, typeName: [{http://www.regione.sardegna.it/}PARTICELLECATASTO])]
>      maxFeatures = null
>      outputFormat = GML2
>      resultType = results
>      traverseXlinkDepth = null
>      traverseXlinkExpiry = null
>      formatOptions = {}
> 2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - CREATE CONNECTION
> 2009-12-01 17:12:51,938 TRACE [geotools.core] - ENTRY 2
> 2009-12-01 17:12:51,938 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,938 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,938 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,938 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting LogicFilter
> 2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,938 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - SELECT * FROM (SELECT IDFEATURE,CODICECOMUNE,FOGLIO,SEZIONE,NUMERO,QUALITACOD,QUALITADESCR,SUPERFICIENOMINALE,SUPERFICIENOMPORZIONE,AREA,ALLEGATO,SVILUPPO,POLIGONO as POLIGONO FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM<= 2000
> 2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - 1 = B354
> 2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - 2 = 9
> 2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - 3 = 187
> 2009-12-01 17:12:51,938 DEBUG [geotools.jdbc] - 4 = A
> 2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - CLOSE CONNECTION
> 2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - CREATE CONNECTION
> 2009-12-01 17:12:51,954 TRACE [geotools.core] - ENTRY 2
> 2009-12-01 17:12:51,954 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,954 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,954 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,954 TRACE [geotools.core] - ENTRY 14
> 2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting LogicFilter
> 2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
> 2009-12-01 17:12:51,954 DEBUG [geotools.filter] - exporting PropertyName
> 2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - SELECT * FROM (SELECT IDFEATURE,CODICECOMUNE,FOGLIO,SEZIONE,NUMERO,QUALITACOD,QUALITADESCR,SUPERFICIENOMINALE,SUPERFICIENOMPORZIONE,AREA,ALLEGATO,SVILUPPO,POLIGONO as POLIGONO FROM IDT.PARTICELLECATASTO WHERE (CODICECOMUNE = ? AND FOGLIO = ? AND NUMERO = ? AND SEZIONE = ?)) WHERE ROWNUM<= 2000
> 2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - 1 = B354
> 2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - 2 = 9
> 2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - 3 = 187
> 2009-12-01 17:12:51,954 DEBUG [geotools.jdbc] - 4 = A
> 2009-12-01 17:12:51,969 DEBUG [geotools.gml] - closing reader org.geoserver.security.decorators.ReadOnlyFeatureIterator@9a15b3
> 2009-12-01 17:12:51,969 DEBUG [geotools.jdbc] - CLOSE CONNECTION
>
> Thank you for any hint
>
> Stefano Iacovella
>


--
Ben Caradoc-Davies <[hidden email]>
Software Engineer, CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre
26 Dick Perry Ave, Kensington WA 6151, Australia

------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug in Oracle-NG datastore

Stefano Iacovella-4
In reply to this post by aaime


2009/12/1 Andrea Aime <[hidden email]>

Name                           Null     Type             ------------------------------ -------- -----------------
IDFEATURE                      NOT NULL NUMBER           CODICECOMUNE                   NOT NULL CHAR(4)          FOGLIO                         NOT NULL NUMBER(5)        SEZIONE                        NOT NULL CHAR(1)          NUMERO                         NOT NULL CHAR(5)          

This might be the reason for the problem, but I'm not sure.
What might be happening is that the code is setting 9 as a
number instead of a char in the prepared statement params.

In theory this kind of error should not happen, there is
code trying to guess the type of the attribute the comparison
is using, but maybe you're hitting a case that's not well
covered. Can you narrow down your filter to a case where
the filter itself is minimal, but the code still misbehaves?

The old Oracle datastore was not affecetd because it is not
using prepared statements.

Cheers
Andrea

Andrea I did some further investigation about it.
Looking at the DDL I noted the field NUMERO, which is the filter component no more working, is CHAR(5) while I was pretty sure it was VARCHAR2(5). The filed contains the "number" identifying a cadastral parcel. It is supposed to be an integer but there are several parcels where it is an integer plus at least one alphabetical character. The table is populated form an ETL. The ETL extracts data from an other DB where the NUMERO FIELD is filled with trailing zero. The zero are removed and the resulting information is inserted into the table published with geoserver.
So I tried several filter and the results are:

<Filter><And><PropertyIsEqualTo><PropertyName>CODICECOMUNE</PropertyName><Literal>B354</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>FOGLIO</PropertyName><Literal>9</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>NUMERO</PropertyName><Literal>187</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>SEZIONE</PropertyName><Literal>A</Literal></PropertyIsEqualTo></And></Filter>

Does not return any feature

http://localhost:8080/geoserver/wfs?request=GetFeature&version=1.1.0&typeName=cat:PARTICELLECATASTO&outputFormat=GML2&FILTER=<Filter><And><PropertyIsEqualTo><PropertyName>NUMERO</PropertyName><Literal>176B</Literal></PropertyIsEqualTo></And></Filter>

Does not return any feature

I created an other table where I can easily change the field type. The original structure was:

CREATE TABLE SDEUSER.PARTICELLE
(
  IDFEATURE  NUMBER(10),
  NUMERO     CHAR(5 BYTE),
  FOGLIO     NUMBER(10),
  COMUNE     CHAR(5 BYTE),
  POLIGONO   MDSYS.SDO_GEOMETRY
)

I populated the table with sample data extracted from the original table. The behaviour was exactly the same. then I made an alter table to turn the CHAR(5 BYTE) into VARCHAR2(5 BYTE). And now it does work!
I guessed the problem may be a CHAR field with data populated with a string lower than the length allowed. So I tried to populate the sample table with values of exactly five char. Again it does work


I was the "designer" of the table so I have to apologize for what seems to be the main reason for the strange behaviour.
Thanks a lot for your help

Stefano

------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Excellent bug report

Stefano Iacovella-4
In reply to this post by Ben Caradoc-Davies
thank you Ben

Andrea, Justin, Simone and all the geoserver's developers make every day an excellent work both developing both supporting the users. I feel that trying to be clever and polite with them is a minimal acknowledgements to their job.

Stefano

2009/12/2 Ben Caradoc-Davies <[hidden email]>
Stefano,

while I cannot help you with your bug, I would like to thank you for your excellent bug report. The inclusion of the query, a description of the result, the table definition, and debug logs including generated SQL provide developers with just about everything they could ask for.

Users, this bug report by Stefano is exemplary. If you ever want a bug fixed, a report of this high quality will give us the best chance of fixing it.

Kind regards,
Ben.



------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug in Oracle-NG datastore

aaime
In reply to this post by Stefano Iacovella-4
Stefano Iacovella ha scritto:

>
>
> 2009/12/1 Andrea Aime <[hidden email] <mailto:[hidden email]>>
>
>
>         Name                           Null     Type            
>         ------------------------------ -------- -----------------
>         IDFEATURE                      NOT NULL NUMBER          
>         CODICECOMUNE                   NOT NULL CHAR(4)          FOGLIO
>                                 NOT NULL NUMBER(5)        SEZIONE      
>                          NOT NULL CHAR(1)          NUMERO              
>                   NOT NULL CHAR(5)          
>
>
>     This might be the reason for the problem, but I'm not sure.
>     What might be happening is that the code is setting 9 as a
>     number instead of a char in the prepared statement params.
>
>     In theory this kind of error should not happen, there is
>     code trying to guess the type of the attribute the comparison
>     is using, but maybe you're hitting a case that's not well
>     covered. Can you narrow down your filter to a case where
>     the filter itself is minimal, but the code still misbehaves?
>
>     The old Oracle datastore was not affecetd because it is not
>     using prepared statements.
>
>     Cheers
>     Andrea
>
> Andrea I did some further investigation about it.
> Looking at the DDL I noted the field NUMERO, which is the filter
> component no more working, is CHAR(5) while I was pretty sure it was
> VARCHAR2(5). The filed contains the "number" identifying a cadastral
> parcel. It is supposed to be an integer but there are several parcels
> where it is an integer plus at least one alphabetical character. The
> table is populated form an ETL. The ETL extracts data from an other DB
> where the NUMERO FIELD is filled with trailing zero. The zero are
> removed and the resulting information is inserted into the table
> published with geoserver.
> So I tried several filter and the results are:
>
> <Filter><And><PropertyIsEqualTo><PropertyName>CODICECOMUNE</PropertyName><Literal>B354</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>FOGLIO</PropertyName><Literal>9</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>NUMERO</PropertyName><Literal>187</Literal></PropertyIsEqualTo><PropertyIsEqualTo><PropertyName>SEZIONE</PropertyName><Literal>A</Literal></PropertyIsEqualTo></And></Filter>
>
> Does not return any feature
>
> http://localhost:8080/geoserver/wfs?request=GetFeature&version=1.1.0&typeName=cat:PARTICELLECATASTO&outputFormat=GML2&FILTER= 
> <http://localhost:8080/geoserver/wfs?request=GetFeature&version=1.1.0&typeName=cat:PARTICELLECATASTO&outputFormat=GML2&FILTER=><Filter><And><PropertyIsEqualTo><PropertyName>NUMERO</PropertyName><Literal>176B</Literal></PropertyIsEqualTo></And></Filter>
>
> Does not return any feature
>
> I created an other table where I can easily change the field type. The
> original structure was:
>
> CREATE TABLE SDEUSER.PARTICELLE
> (
>   IDFEATURE  NUMBER(10),
>   NUMERO     CHAR(5 BYTE),
>   FOGLIO     NUMBER(10),
>   COMUNE     CHAR(5 BYTE),
>   POLIGONO   MDSYS.SDO_GEOMETRY
> )
>
> I populated the table with sample data extracted from the original
> table. The behaviour was exactly the same. then I made an alter table to
> turn the CHAR(5 BYTE) into VARCHAR2(5 BYTE). And now it does work!
> I guessed the problem may be a CHAR field with data populated with a
> string lower than the length allowed. So I tried to populate the sample
> table with values of exactly five char. Again it does work
>
>
> I was the "designer" of the table so I have to apologize for what seems
> to be the main reason for the strange behaviour.
> Thanks a lot for your help

Aaaah.... right right, I remember this silly Oracle behaviour now that
you mention it.
In Oracle with char type 'ABC' is not equal to '  ABC', spaces _do_
count in the comparison.

Duh, sorry I did not think about it before, and happy you finally solved
the problem :-)

Wondering, for Oracle we should probably manually pad the strings
coming in for the char(x) type... unfortunately I don't think we
are carrying around with the feature type the actual original sql
type and, just as important, its length.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug in Oracle-NG datastore

Stefano Iacovella-4


2009/12/2 Andrea Aime <[hidden email]>

Aaaah.... right right, I remember this silly Oracle behaviour now that
you mention it.
In Oracle with char type 'ABC' is not equal to '  ABC', spaces _do_
count in the comparison.

Duh, sorry I did not think about it before, and happy you finally solved
the problem :-)

Wondering, for Oracle we should probably manually pad the strings
coming in for the char(x) type... unfortunately I don't think we
are carrying around with the feature type the actual original sql
type and, just as important, its length.

Cheers
Andrea


The sql client behaviour was misleading me.

I tried to query the table from the sql developer with an equivalent to the former wfs filter  


select * from particellecatasto where codicecomune = 'B354' and foglio = 9 and sezione = 'A' and numero = '187';


this query returns exactly one record. Changing the selection I see there are some blank character in the NUMERO field, obviously as it was defined as CHAR(5BYTE).

select '|' || numero || '|' from particellecatasto where codicecomune = 'B354' and foglio = 9 and sezione = 'A' and numero = '187';


|187  |

May be the oracle client does the "magic" to avoid problems with blank characters?

Anyway a varchar2 is a better choice to avoid problems.


Thank you

Stefano

------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug in Oracle-NG datastore

aaime
Stefano Iacovella ha scritto:

> select * from particellecatasto where codicecomune = 'B354' and foglio =
> 9 and sezione = 'A' and numero = '187';
>
>
> this query returns exactly one record. Changing the selection I see
> there are some blank character in the NUMERO field, obviously as it was
> defined as CHAR(5BYTE).
>
> select '|' || numero || '|' from particellecatasto where codicecomune =
> 'B354' and foglio = 9 and sezione = 'A' and numero = '187';
>
>
> |187  |
>
> May be the oracle client does the "magic" to avoid problems with blank
> characters?

Nope, it's just that Oracle behaves differently for plain SQL
and for prepared statements, in the latter it will make an exact
match only. I actually found that this issue popped up in the
past too, here is the report:
http://jira.codehaus.org/browse/GEOT-2293

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug in Oracle-NG datastore [SEC=Unclassified]

miles_jordan
In reply to this post by Stefano Iacovella-4
Stefano Iacovella wrote:

>
>
> 2009/12/2 Andrea Aime <[hidden email]>
>
>
>
>       Aaaah.... right right, I remember this silly Oracle behaviour now that
>       you mention it.         In Oracle with char type 'ABC' is not equal to '
> ABC', spaces _do_     count in the comparison.
>
>       Duh, sorry I did not think about it before, and happy you finally
> solved        the problem :-)
>
>       Wondering, for Oracle we should probably manually pad the strings
>       coming in for the char(x) type... unfortunately I don't think we
>       are carrying around with the feature type the actual original sql
>       type and, just as important, its length.
>
>
>       Cheers
>       Andrea
>
>
>
>
> The sql client behaviour was misleading me.
>
> I tried to query the table from the sql developer with an equivalent
> to the former wfs filter
>
>
> select * from particellecatasto where codicecomune = 'B354' and foglio
> = 9 and sezione = 'A' and numero = '187';
>
>
> this query returns exactly one record. Changing the selection I see
> there are some blank character in the NUMERO field, obviously as it
> was defined as CHAR(5BYTE).
>
> select '|' || numero || '|' from particellecatasto where codicecomune
> = 'B354' and foglio = 9 and sezione = 'A' and numero = '187';
>
>
> |187  |
>
> May be the oracle client does the "magic" to avoid problems with blank
> characters?
>
> Anyway a varchar2 is a better choice to avoid problems.

Oh, hehe, surprised I didn't see that one coming too :)

It may well be that SQL Developer changes the '187' in your SQL statement to '187  '. Oracle have been known to do stranger things. After all, it does know about your table structure, but helps not when trying to debug queries!

As a general rule with Oracle, you should only ever use the CHAR type when you are storing exactly the same amount of characters for that field in every row. For example 'aaa','aab','abb'. If you have some uncertainty about how many characters will be in that field you should choose varchar2, because it will require much less storage.

In the real world you can opt to always choose varchar2 anyway, even if you are storing fixed length strings, because there should be next to no performance difference.

Regards,

Miles


___________________________________________________________________________

    Australian Antarctic Division - Commonwealth of Australia
IMPORTANT: This transmission is intended for the addressee only. If you are not the
intended recipient, you are notified that use or dissemination of this communication is
strictly prohibited by Commonwealth law. If you have received this transmission in error,
please notify the sender immediately by e-mail or by telephoning +61 3 6232 3209 and
DELETE the message.
        Visit our web site at http://www.antarctica.gov.au/
___________________________________________________________________________
------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users