db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
Date Wed, 02 Oct 2013 15:54:44 GMT

    [ 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)

Mime
View raw message