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
Attachments
Issue Links
- links to