Bug in JoiningJDBCFeatureSource / FilterToSQL creates invalid SQL

Description

When making a GetFeature WFS request with a spatial filter applied, the following error is reported:

ORA-00933: SQL command not properly ended

Digging in to what is happening, the resultant SQL from the filter looks like:

SELECT COUNT(DISTINCT PUB_DATA.OZMIN_ERL_MINERALOCCVIEW.GML_ID) FROM PUB_DATA.OZMIN_ERL_MINERALOCCVIEWWHERE SDO_FILTER(SHAPE, :1 , 'mask=anyinteract querytype=WINDOW') = 'TRUE' , OriginalSql = SELECT COUNT(DISTINCT PUB_DATA.OZMIN_ERL_MINERALOCCVIEW.GML_ID) FROM PUB_DATA.OZMIN_ERL_MINERALOCCVIEWWHERE SDO_FILTER(SHAPE, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'

As can be seen, there is no space between the table name and the WHERE keyword. We believe this is an issue that affects Oracle, the problem seems to go back a number of versions, and only appeared when we migrated to Oracle from Postgres.

The solution seems simple, simply adding a space when the WHERE filter is appended, but I’m not sure which part of the code this needs to be injected

Environment

Windows / Linux

GeoServer 2.20.4

Oracle database

Attachments

gsmlp_BoreholeView.xml

15 Jun, 2022

geoserver.log

15 Jun, 2022

Activity

Marco Volpini

January 9, 2023 at 12:10 PM

(edited)

main pr https://github.com/geotools/geotools/pull/4156

28.x https://github.com/geotools/geotools/pull/4163

27.x https://github.com/geotools/geotools/pull/4162

Ian Turton

June 17, 2022 at 2:58 PM

It might be a simple as adding countSQL.append(" "); before line 1557 in `org.geotools.appschema.jdbc.JoiningJDBCFeatureSource` but without a testcase I’m not sure I want to fiddle with it.

Aaron Sedgmen

June 15, 2022 at 1:47 AM

(edited)

Same issue occurs in the Boreholes GeoSciML-Lite app schema WFS (gsmlp:BoreholeView feature type). Following GetFeature request with a bbox filter returns the “ORA-00933: SQL command not properly ended” error:

https://services.ga.gov.au/gis/boreholes/gsmlp/ows?service=WFS&version=2.0.0&request=GetFeature&typeName=gsmlp%3ABoreholeView&outputFormat=gml3&count=10&cql_filter=BBOX(shape,149.100005802891,-33.2999871238691,149.700005717857,-32.6999871196314)

Attached is the geoserver log with the GEOTOOLS_DEVELOPER_LOGGING.properties logging profile enabled, containing the stack trace.

Also attached is the app schema configuration for the gsmlp:BoreholeView feature type.

Mark Prins

May 30, 2022 at 12:10 PM

perhaps a stack trace can shed some light on where the WHERE is appended

Resize issue view side panel

Fixed

Details

Assignee

Marco Volpini

Reporter

Michael Sexton

Priority

Medium

Created May 25, 2022 at 2:23 AM

Updated January 13, 2023 at 12:00 AM

Resolved January 12, 2023 at 11:25 AM

Bug in JoiningJDBCFeatureSource / FilterToSQL creates invalid SQL

Description

Environment

Attachments

Activity

Details

Assignee

Reporter

Triage

Components

Fix versions

Affects versions

Priority