[
https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14205556#comment-14205556
]
Ashutosh Chauhan commented on DERBY-6358:
-----------------------------------------
Any update on this bug? I am from Apache Hive team. We strive to support
multiple databases for our metastore and we hate to add Derby-specific
workarounds. In particular, we would like to get rid of
https://github.com/apache/hive/blob/trunk/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java#L931
This is causing us continuous grief :
[HIVE-8739|https://issues.apache.org/jira/browse/HIVE-8739?focusedCommentId=14201301&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14201301]
Is anyone working on this actively?
> WHERE clause should be evaluated after the joins in the FROM clause
> -------------------------------------------------------------------
>
> Key: DERBY-6358
> URL: https://issues.apache.org/jira/browse/DERBY-6358
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.1, 10.10.2.0, 10.11.1.1
> Reporter: Rick Hillegas
>
> The WHERE clause is supposed to be evaluated after the inner and outer joins
> specified in the FROM clause. See part 2 of the SQL Standard, section 7.4
> (<table expression>), general rule 1. However, it appears that Derby flattens
> the inner joins into a cartesian product and mixes their ON clauses into the
> WHERE clause. As a result, WHERE clause fragments can be evaluated before the
> ON clauses. The following script shows this problem:
> connect 'jdbc:derby:memory:db;create=true';
> create table t1( a varchar( 10 ) );
> create table t2( a varchar( 10 ) );
> insert into t1( a ) values ( 'horse' ), ( 'apple' ), ( 'star' ), ( '6' );
> insert into t2( a ) values ( '6' );
> -- ok if the cast is performed in the select list
> select cast( t1.a as int )
> from t1 inner join t2 on t1.a = t2.a;
> -- should succeed.
> -- but we see a casting error because the WHERE clause is evaluated before
> the ON clause
> select *
> from t1 inner join t2 on t1.a = t2.a
> where cast( t1.a as int ) > 5;
> Fixing this bug may result in serious performance degradation for many
> queries. A release note will be needed to tell users how to re-write their
> queries in order to get the old performance. For instance, the user may need
> to flatten the inner joins themselves, rewriting the query as a cartesian
> product with a WHERE clause.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
