You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2016/03/14 18:07:33 UTC
[jira] [Updated] (CALCITE-1153) Invalid cast created during SQL
Join in Oracle
[ https://issues.apache.org/jira/browse/CALCITE-1153?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde updated CALCITE-1153:
---------------------------------
Affects Version/s: (was: 1.7.0)
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):
{code}
create table myschema.a_table(
description varchar2(10)
);
create table myschema.b_table(
description20 varchar2(20)
);
{code}
When the join is attempted
{noformat}
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;
{noformat}
The following CAST pattern is not acceptable to Oracle (11.2)
{noformat}
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)
{noformat}
Discovered this in Drill:
{noformat}
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();
...}
{noformat}
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.
{noformat}
create table myschema.a_table(
description varchar2(10)
);
create table myschema.b_table(
description20 varchar2(20)
);
{noformat}
When the join is attempted
{noformat}
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;
{noformat}
The following CAST pattern is not acceptable to Oracle (11.2)
{noformat}
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)
{noformat}
Specifically, CHARACTER SET "ISO-8859-1" is not allowed in the cast
statement:
{noformat}
*CAST*({ expr | *MULTISET* (subquery) } *AS* type_name)
{noformat}
https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions016.htm
was:
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
> Invalid cast created during SQL Join in Oracle
> ----------------------------------------------
>
> Key: CALCITE-1153
> URL: https://issues.apache.org/jira/browse/CALCITE-1153
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Chris Atkinson
> Assignee: Julian Hyde
> Fix For: next
>
>
> 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):
> {code}
> create table myschema.a_table(
> description varchar2(10)
> );
> create table myschema.b_table(
> description20 varchar2(20)
> );
> {code}
> When the join is attempted
> {noformat}
> 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;
> {noformat}
> The following CAST pattern is not acceptable to Oracle (11.2)
> {noformat}
> 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)
> {noformat}
> Discovered this in Drill:
> {noformat}
> 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();
> ...}
> {noformat}
> 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.
> {noformat}
> create table myschema.a_table(
> description varchar2(10)
> );
> create table myschema.b_table(
> description20 varchar2(20)
> );
> {noformat}
> When the join is attempted
> {noformat}
> 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;
> {noformat}
> The following CAST pattern is not acceptable to Oracle (11.2)
> {noformat}
> 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)
> {noformat}
> Specifically, CHARACTER SET "ISO-8859-1" is not allowed in the cast
> statement:
> {noformat}
> *CAST*({ expr | *MULTISET* (subquery) } *AS* type_name)
> {noformat}
> https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions016.htm
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)