Check that database based datastores properly support case insensitive comparisons

Andrea Aime added a comment - 03/Dec/08 9:43 AM Fixed on trunk at 31943, waiting to backport on 2.5.x when 2.5.2 is out

Christian Mueller added a comment - 04/Dec/08 11:07 AM 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.

Andrea Aime added a comment - 05/Dec/08 2:30 AM 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?

Christian Mueller added a comment - 05/Dec/08 5:15 AM 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.

Andrea Aime added a comment - 05/Dec/08 7:54 AM 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!)

Christian Mueller added a comment - 05/Dec/08 9:48 AM 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

Andrea Aime added a comment - 09/Dec/08 5:46 AM Christian, is the change proposed in GEOT-2196 enough to allow you fixing the casting issue?

Andrea Aime added a comment - 09/Dec/08 5:48 AM Fixed on 2.5.x as well. Christian, can you open a separate jira issue for the type casting problem?