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

Invalid CAST when push JOIN down to Oracle

    Details

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

      Description

      The code generates a casts to ensure a match in varchar length during a join (in Oracle SQL this explicit cast is not required but that's a different issue):

      create table myschema.a_table(
        description varchar2(10)
      );
      
      create table myschema.b_table(
        description20 varchar2(20)
      );
      

      When the join is attempted

      0: jdbc:drill:zk=local> select *
      . . . . . . . . . . . >   from utd_utpdba.UTPDBA.A_TABLE
      . . . . . . . . . . . >  inner join utd_utpdba.UTPDBA.B_TABLE
      . . . . . . . . . . . >     on A_TABLE.DESCRIPTION = B_TABLE.DESCRIPTION20;
      

      The following CAST pattern is not acceptable to Oracle (11.2)

      Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.
      
      sql SELECT *
      FROM (SELECT "DESCRIPTION", CAST("DESCRIPTION" AS VARCHAR(20) CHARACTER SET
      "ISO-8859-1") "$f2"
      FROM "UTPDBA"."A_TABLE") "t"
      INNER JOIN "UTPDBA"."B_TABLE" ON "t"."$f2" = "B_TABLE"."DESCRIPTION20"
      plugin utd_utpdba
      Fragment 0:0
      
      [Error Id: 2f9a1975-fa57-4b07-8642-4dca3d03ae39 on x.x.x.x:31010]
      
        (java.sql.SQLSyntaxErrorException) ORA-00907: missing right parenthesis
      
          oracle.jdbc.driver.T4CTTIoer.processError():450
      ....
          java.lang.Thread.run():745 (state=,code=0)
      

      Discovered this in Drill:

      drill/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcPrel.java:
      ...
      org.apache.calcite.adapter.jdbc.JdbcImplementor
      ...
      public JdbcPrel(...){...
      final SqlDialect dialect = convention.getPlugin().getDialect();
          final JdbcImplementor jdbcImplementor = new JdbcImplementor(
              dialect,
              (JavaTypeFactory) getCluster().getTypeFactory());
          final JdbcImplementor.Result result =
              jdbcImplementor.visitChild(0, input.accept(new SubsetRemover()));
          sql = result.asQuery().toSqlString(dialect).getSql();
      ...}
      

      The following is still applicable... the cast isn't valid for Oracle SQL:

      I'm trying to join two VARCHAR2 columns of differing length. The generate
      SQL casts the shorter to match the longer.

      create table myschema.a_table(
        description varchar2(10)
      );
      
      create table myschema.b_table(
        description20 varchar2(20)
      );
      

      When the join is attempted

      0: jdbc:drill:zk=local> select *
      . . . . . . . . . . . >   from utd_utpdba.UTPDBA.A_TABLE
      . . . . . . . . . . . >  inner join utd_utpdba.UTPDBA.B_TABLE
      . . . . . . . . . . . >     on A_TABLE.DESCRIPTION = B_TABLE.DESCRIPTION20;
      

      The following CAST pattern is not acceptable to Oracle (11.2)

      Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup
      the SQL query.
      
      sql SELECT *
      FROM (SELECT "DESCRIPTION", CAST("DESCRIPTION" AS VARCHAR(20) CHARACTER SET
      "ISO-8859-1") "$f2"
      FROM "UTPDBA"."A_TABLE") "t"
      INNER JOIN "UTPDBA"."B_TABLE" ON "t"."$f2" = "B_TABLE"."DESCRIPTION20"
      plugin utd_utpdba
      Fragment 0:0
      
      [Error Id: 2f9a1975-fa57-4b07-8642-4dca3d03ae39 on x.x.x.x:31010]
      
        (java.sql.SQLSyntaxErrorException) ORA-00907: missing right parenthesis
      
          oracle.jdbc.driver.T4CTTIoer.processError():450
      ....
          java.lang.Thread.run():745 (state=,code=0)
      

      Specifically, CHARACTER SET "ISO-8859-1" is not allowed in the cast
      statement:

      *CAST*({ expr | *MULTISET* (subquery) } *AS* type_name)
      

      https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions016.htm

        Issue Links

          Activity

          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/9dd4993a ; added test case in http://git-wip-us.apache.org/repos/asf/calcite/commit/22aa8a80 . Thanks for the PR, Chris Atkinson !
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.7.0 (2016-03-22).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.7.0 (2016-03-22).

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              catkinson Chris Atkinson
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development