[#GEOT-717] Filter expression with numeric literals

GeoTools

Filter expression with numeric literals

Created: 09/Oct/05 04:01 AM Updated: 04/Sep/08 08:04 PM

Component/s:

core main, data oraclespatial

Affects Version/s:

2.1.RC1

Fix Version/s:

2.4.6

Environment:

SLES9, Sun JVM 1.4.2_08, Tomcat 5.0.19, Geoserver 1.3.0 RC3, Oracle database server 9.2.0.4 / thick client connection

Description

« Hide

Our table contains several VARCAHR fields which identify land lots. The
field values are all numeric (e.g. 12345), but cannot be stored in
numeric fields due to some legacy software.
Everything seems to work fine unless we impose a filter like

<ogc:Filter>
<ogc:PropertyIsEqualTo>
<ogc:PropertyName>GMNR</ogc:PropertyName>
<ogc:Literal>3819</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>

Oracle "performs" a table scan, which takes approx. 10 min.
We digged into this and found the SQL-statement in catalina.out - it
contains a WHERE clause similar to:
WHERE "GMNR" = 3819
That is, the filter implementation resolves our alphnumeric value 3819
to an integer. Obviously, this causes Oracle not to use its index ...
When we add some extra characters like 3819A or similar, the WHERE
clause is altered to
WHERE "GMNR" = '3819A'
which is really quick - but doesn't reveal any results!

We were told this is probably due to a misinterpretation of the Oracle SQL Encoder.
Unfortunately I'm not yet capable of creating neither a test case nor a patch.
Anyway, this bug could be easily reconstructed with the above Filter expression.

All

Comments

Change History

Sort Order:

[ Permlink | « Hide ]

dblasby added a comment - 10/Oct/05 12:06 PM

This is a side-effect of how the Expression parser works. It really
needs to be much more aware of the schema its interacting with.

The original implementors looked at <Literal> elements and said "if this
looks like a number, then treat it like a number". Unfortunately, this
is a problem if your string looks like a number. Expression should
probably re-work itself based on the schema (FeatureType) its actually
querying against.

Re-working Expression and Filter is probably too much work for right
now, but you can probably easily change the JDBC Datastore so if you do
a query like yours correctly.

Ie. <DB VARCHAR col> = <Number> gets converted to <DB VARCHAR col> =
<Number converted to a String>. This will not work 100% of the time,
but it should work most of the time.

dave
ps. here's an example where it will not work:
..
<Literal>
0.5678567567567856785678456789456789456789456789567894567895678
</Literal>

When this is converted to a Double, you cannot re-convert it to the
original string (it will be slightly different).

[ Permlink | « Hide ]

Jürgen Link added a comment - 12/Oct/05 04:59 PM

I verified the problem with PostGIS - the effect is identical.
I looked into the code and decided to hack the basic implementation "SQLEncoder" :

    public void visit(LiteralExpression expression) throws RuntimeException {
        LOGGER.finer("exporting LiteralExpression");

        try {
            Object literal = expression.getLiteral();
            short type = expression.getType();

            switch (type) {
            case Expression.LITERAL_DOUBLE:
            case Expression.LITERAL_INTEGER:
// JL@BGS 12-10-2005:
// temporary hack - should be fixed by someone who has far more understanding ...
// out.write(literal.toString());
//
// break;
//
            case Expression.LITERAL_STRING:
                out.write("'" + literal + "'");

                break;

            case Expression.LITERAL_GEOMETRY:
                visitLiteralGeometry(expression);

                break;

            default:
                throw new RuntimeException("type: " + type + "not supported");
            }
        } catch (java.io.IOException ioe) {
            throw new RuntimeException("IO problems writing literal", ioe);
        }
    }

I can't believe we are the only ones to run into this problem. For Tables with more
than approx. 500000 entries this bug leads to extremely long response times.

As this bug is no longer tied to Oracle, I reassigned it to module "main".
Hope, that's ok for everybody.

[ Permlink | « Hide ]

Jody Garnett added a comment - 15/Nov/05 06:41 PM

Thanks for putting this into jira.

[ Permlink | « Hide ]

Jody Garnett added a comment - 06/Jul/07 02:18 PM

We need to confirm that this problem is still applicable for 2.4.0 - it may already be fixed by the move to a different SQLEncoder / Splitter - and the change to a more capable Filter API (that can smoothly morph from String to Int to match the table column type.

[ Permlink | « Hide ]

Andrea Aime added a comment - 07/Apr/08 10:04 AM

Should have been fixed indeed, but I have no time to check... anyways, assigned to Oracle module as well (if any sponsoring comes up to fix Oracle ds a bit more I'll try to check this one too)

[ Permlink | « Hide ]

Andrea Aime added a comment - 09/May/08 09:46 AM

Relaxing the issue criticality... it's probably fixed, and an issue for an unmaintained module should not pose any concern to the release process anyways