GeoTools

Check that database based datastores properly support case insensitive comparisons

Details

Description

I cannot actually test the db2 backend, but I'm going to add a test in the jdbc-ng suite, which will affect db2 as well (so Christian should notice)

Issue Links

is related to

Improvement - An improvement or enhancement to an existing feature or task. GEOT-2196 have attribute descriptor user data contain native database type information

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.
relates to

Bug - A problem which impairs or prevents the functions of the product. GEOS-2251 Database backed datastores fail to honour the matchCase attribute

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Andrea Aime added a comment -
Fixed on trunk at 31943, waiting to backport on 2.5.x when 2.5.2 is out
Show
Andrea Aime added a comment - Fixed on trunk at 31943, waiting to backport on 2.5.x when 2.5.2 is out
Hide
Permalink
Christian Mueller added a comment -
I checked the db2 implications:

   select count(*) from f1 where lower('ABC') = lower(?)

is not a valid db2 syntax.

There are 2 possibilities

1) select count(*) from f1 where lower('ABC') = lower(cast (? as varchar(4096)))
2) select count(*) from f1 where lower('ABC') = lower('Abc')

add 1)
Would be a challenge for each data type

add 2)
I prefered this one and duplicated the logic from

FilterToSQl>>visit(Literal expression, Object context)

I am aware that there is no test case checking the literals for different db systems, so I assume that e. g. date/timestamp literals wold not work for db2 at the moment. However, strings and numerical Datatypes should work.
















Show
Christian Mueller added a comment - I checked the db2 implications:    select count(*) from f1 where lower('ABC') = lower(?) is not a valid db2 syntax. There are 2 possibilities 1) select count(*) from f1 where lower('ABC') = lower(cast (? as varchar(4096))) 2) select count(*) from f1 where lower('ABC') = lower('Abc') add 1) Would be a challenge for each data type add 2) I prefered this one and duplicated the logic from FilterToSQl>>visit(Literal expression, Object context) I am aware that there is no test case checking the literals for different db systems, so I assume that e. g. date/timestamp literals wold not work for db2 at the moment. However, strings and numerical Datatypes should work.
Hide
Permalink
Andrea Aime added a comment -
Case insensitve comparison seems to make sense (to me) only for string based comparisons. Anyways, Justin just opened a jira in which he proposes to add the native type information to the attribute type metadata (GEOT-2196) so that would help in implementing option 1)

Option two I really do not understand, I see two literals compared in your sample, the jira is about comparing a feature attribute with a literal or another attribute.

About date/timestamp literals not working with db2, what do you mean?
Show
Andrea Aime added a comment - Case insensitve comparison seems to make sense (to me) only for string based comparisons. Anyways, Justin just opened a jira in which he proposes to add the native type information to the attribute type metadata ( GEOT-2196 ) so that would help in implementing option 1) Option two I really do not understand, I see two literals compared in your sample, the jira is about comparing a feature attribute with a literal or another attribute. About date/timestamp literals not working with db2, what do you mean?
Hide
Permalink
Christian Mueller added a comment -
To make things clear: The exact statement issuing the error is

SELECT count(*) FROM "geotools"."ft1" WHERE lower("stringProperty") = lower(?)

The problem is that DB2 does not allow parameter markers as function arguments without a cast. We had this situation in all
spatial functions, eg. "where db2gse.st_intersects(cast (? as blob(2g)), geom)=1" and not "where db2gse.st_intersects(?,geom)=1".

Solution 1)
SELECT count(*) FROM "geotools"."ft1" WHERE lower("stringProperty") = lower(cast (? as varchar(4096)))

Solution 2)
Do not use parameter markers and use the db2 literals instead
SELECT count(*) FROM "geotools"."ft1" WHERE lower("stringProperty") = lower('Abc')

I decided for Solution 2. The Prepared DB2 Dialect does not use ? for literals, it behaves like the Non Prepared Dialect in this case.
I am not sure if all data types will be converted in the correct db2 literal syntax, naming date/timestamp as an example.










Show
Christian Mueller added a comment - To make things clear: The exact statement issuing the error is SELECT count(*) FROM "geotools"."ft1" WHERE lower("stringProperty") = lower(?) The problem is that DB2 does not allow parameter markers as function arguments without a cast. We had this situation in all spatial functions, eg. "where db2gse.st_intersects(cast (? as blob(2g)), geom)=1" and not "where db2gse.st_intersects(?,geom)=1". Solution 1) SELECT count(*) FROM "geotools"."ft1" WHERE lower("stringProperty") = lower(cast (? as varchar(4096))) Solution 2) Do not use parameter markers and use the db2 literals instead SELECT count(*) FROM "geotools"."ft1" WHERE lower("stringProperty") = lower('Abc') I decided for Solution 2. The Prepared DB2 Dialect does not use ? for literals, it behaves like the Non Prepared Dialect in this case. I am not sure if all data types will be converted in the correct db2 literal syntax, naming date/timestamp as an example.
Hide
Permalink
Andrea Aime added a comment -
Ah ok, got it, thanks for the explanation. Yeah, adding unit tests for date/time is something I would like to do, I believe I opened a jira as well... just still haven't found the time to work on it (24 hours day is too short, gimme a 48 one!)
Show
Andrea Aime added a comment - Ah ok, got it, thanks for the explanation. Yeah, adding unit tests for date/time is something I would like to do, I believe I opened a jira as well... just still haven't found the time to work on it (24 hours day is too short, gimme a 48 one!)
Hide
Permalink
Christian Mueller added a comment -
But I am quite unhappy with this situation. At the moment I use ? for spatial params and string literals for all others.

The rule in DB2 is

If you need a ? as a sql function param, you have to use the cast syntax.

This holds true for spatial and non spatial params.

I good idea would be to have a method in the Dialect

void encodeJdbcParamAsSQLFunctionParam( typeInfo) {
   out.write("?");
}
The DB2 Dialect could do its special encoding using the typeInfo as proposed in GEOT-2196
Show
Christian Mueller added a comment - But I am quite unhappy with this situation. At the moment I use ? for spatial params and string literals for all others. The rule in DB2 is If you need a ? as a sql function param, you have to use the cast syntax. This holds true for spatial and non spatial params. I good idea would be to have a method in the Dialect void encodeJdbcParamAsSQLFunctionParam( typeInfo) {    out.write("?"); } The DB2 Dialect could do its special encoding using the typeInfo as proposed in GEOT-2196
Hide
Permalink
Andrea Aime added a comment -
Christian, is the change proposed in GEOT-2196 enough to allow you fixing the casting issue?
Show
Andrea Aime added a comment - Christian, is the change proposed in GEOT-2196 enough to allow you fixing the casting issue?
Hide
Permalink
Andrea Aime added a comment -
Fixed on 2.5.x as well. Christian, can you open a separate jira issue for the type casting problem?
Show
Andrea Aime added a comment - Fixed on 2.5.x as well. Christian, can you open a separate jira issue for the type casting problem?

People

  • Assignee:
    Andrea Aime
    Reporter:
    Andrea Aime
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: