GeoTools
  1. GeoTools
  2. GEOT-2187

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

          Activity

          Hide
          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
          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
          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
          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
          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
          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
          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
          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
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: