[CALCITE-1153] Invalid CAST when push JOIN down to Oracle

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.7.0
• Component/s: core
• Flags: Patch
• Assignee: Julian Hyde
• Reporter: Chris Atkinson
• Created: 14/Mar/16 10:12
• Updated: 22/Mar/16 01:18
• Resolved: 17/Mar/16 22:20

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

• links to: Email thread
• links to: PR 211