Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-182

jdbc: do not add varchar-to-varchar casts

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      When using Optiq's jdbc support with a query condition on a varchar column, this sometimes fails because Optiq introduces varchar-to-varchar casts.

      For example, using the foodmart sample with hsqldb, performing this query:

      ```
      select "lname" from "customer"
      where "lname" = 'this string is longer than 30 characters'
      ```

      results in this query on hsqldb:

      ```
      SELECT "lname"
      FROM "foodmart"."customer"
      WHERE CAST("lname" AS VARCHAR(40) CHARACTER SET "ISO-8859-1") = 'this string is longer than 30 characters'
      ```

      which hsqldb does not understand:

      ```
      Caused by: java.sql.SQLSyntaxErrorException: unexpected token: CHARACTER required: ) : line: 3
      at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
      at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
      at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
      at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
      at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
      at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
      at net.hydromatic.optiq.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:139)
      ... 39 more
      ```

      I actually experienced this problem in the context of HBase Phoenix, which has a similar problems with not understanding cast syntax.

      Optiq introduces casts to the least restrictive type (= the longer string), therefore this issue only occurs when comparing with a string longer than the declared type of lname, which is varchar(30).

      I guess that these casts have no additional value, especially since they cast to the least restrictive type, and they also obfuscate the generated sql, therefore I would propose to remove them.

      The change proposed here fixes the problem and adds a test. It removes the cast in JdbcImplementor, thus when going from RexNodes to SqlNodes, but the cast will still be visible when requesting "explain plan".

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/pull/182
      Created by: bdumon
      Labels:
      Created at: Thu Mar 13 12:09:37 CET 2014
      State: closed

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              github-import GitHub Import
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: