[Geoserver-users] Oracle views repeating columns

I'm trying to add an Oracle view (Oracle 11g) to GeoServer 2.1.1 but
the columns of the view are being replicated multiple times - the
query that GeoServer is running is:

SELECT * FROM (SELECT
SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS
FROM DETECTOR_DEFINITION WHERE SDO_RELATE(GEOMETRY, ?,
'mask=anyinteract querytype=WINDOW') = 'TRUE' ) WHERE ROWNUM <= 50

which is clearly wrong.
The view is defined as:

CREATE OR REPLACE FORCE VIEW "ADMINUTMC"."DETECTOR_DEFINITION" (
  "SYSTEMCODENUMBER", "SHORTDESCRIPTION", "LONGDESCRIPTION",
  "DATASOURCE_TYPEID", "NORTHING", "EASTING", "GEOMETRY",
  "NETWORKPATHREFERENCE", "TRANSPORTLINKREFERENCE", "TYPEID", "LINKDISTANCE",
  "CREATIONDATE", "DELETIONDATE", "QUALITYSTATEMENTID", "DISPLAYOPTION",
  "STR_STATUS", primary key(SYSTEMCODENUMBER)RELY DISABLE NOVALIDATE)
AS
  SELECT
    SYSTEMCODENUMBER,
    SHORTDESCRIPTION,
    LONGDESCRIPTION,
    DATASOURCE_TYPEID,
    NORTHING,
    EASTING,
    GEOMETRY,
    NETWORKPATHREFERENCE,
    TRANSPORTLINKREFERENCE,
    TYPEID,
    LINKDISTANCE,
    CREATIONDATE,
    DELETIONDATE,
    QUALITYSTATEMENTID,
    DISPLAYOPTION,
    STR_STATUS
  FROM
    ADMINUTMC.TBLDETECTORDEFINITION;

Has anyone seen anything liek this before?

Ian

--
Ian Turton

Further investigation seems to show this is only a problem with 11.2
and not with 11.1 if that helps anyone.

Ian

On 15 August 2011 17:07, Ian Turton <ijturton@anonymised.com> wrote:

I'm trying to add an Oracle view (Oracle 11g) to GeoServer 2.1.1 but
the columns of the view are being replicated multiple times - the
query that GeoServer is running is:

SELECT * FROM (SELECT
SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS
FROM DETECTOR_DEFINITION WHERE SDO_RELATE(GEOMETRY, ?,
'mask=anyinteract querytype=WINDOW') = 'TRUE' ) WHERE ROWNUM <= 50

which is clearly wrong.
The view is defined as:

CREATE OR REPLACE FORCE VIEW "ADMINUTMC"."DETECTOR_DEFINITION" (
"SYSTEMCODENUMBER", "SHORTDESCRIPTION", "LONGDESCRIPTION",
"DATASOURCE_TYPEID", "NORTHING", "EASTING", "GEOMETRY",
"NETWORKPATHREFERENCE", "TRANSPORTLINKREFERENCE", "TYPEID", "LINKDISTANCE",
"CREATIONDATE", "DELETIONDATE", "QUALITYSTATEMENTID", "DISPLAYOPTION",
"STR_STATUS", primary key(SYSTEMCODENUMBER)RELY DISABLE NOVALIDATE)
AS
SELECT
SYSTEMCODENUMBER,
SHORTDESCRIPTION,
LONGDESCRIPTION,
DATASOURCE_TYPEID,
NORTHING,
EASTING,
GEOMETRY,
NETWORKPATHREFERENCE,
TRANSPORTLINKREFERENCE,
TYPEID,
LINKDISTANCE,
CREATIONDATE,
DELETIONDATE,
QUALITYSTATEMENTID,
DISPLAYOPTION,
STR_STATUS
FROM
ADMINUTMC.TBLDETECTORDEFINITION;

Has anyone seen anything liek this before?

Ian

--
Ian Turton

--
Ian Turton

On Mon, Aug 15, 2011 at 6:37 PM, Ian Turton <ijturton@anonymised.com> wrote:

Further investigation seems to show this is only a problem with 11.2
and not with 11.1 if that helps anyone.

Makes me think about a JDBC driver issue?
The list of columns is provided by the driver database metadata api

Cheers
Andrea

--
-------------------------------------------------------
Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

-------------------------------------------------------