[
https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13784101#comment-13784101
]
Rick Hillegas commented on DERBY-6358:
--------------------------------------
In an offline discussion with Dag, Knut, and Dyre, it was suggested that we
might be able to reduce the performance regression by continuing to flatten the
inner joins into a cartesian product but only push "safe" predicates down. Safe
predicates would be ones which wouldn't have side-effects or raise exceptions
at runtime. We might be able to start out with the predicates which are pushed
into the Store (simple comparisons of columns to columns and columns to
constants). Over time, we could add other predicates to the safe list on a
case-by-case basis.
> 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.11.0.0
> 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.1#6144)
