Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Fri, 16 Jun 2017 17:55:01 +0000 (UTC)
From: "Sergey Shelukhin (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12671359.1380547055000.35485.1497635701581@Atlassian.JIRA>
In-Reply-To: <JIRA.12671359.1380547055000@Atlassian.JIRA>
References: <JIRA.12671359.1380547055000@Atlassian.JIRA> <JIRA.12671359.1380547055040@jira-lw-us.apache.org>
Subject: [jira] [Commented] (DERBY-6358) WHERE clause should be evaluated
 after the joins in the FROM clause
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Fri, 16 Jun 2017 17:55:10 -0000


    [ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16052190#comment-16052190 ] 

Sergey Shelukhin commented on DERBY-6358:
-----------------------------------------

from clause appears to include joins as per 7.6 (I am not reading this with a lot of attention right now :)). 
So, the clauses should be applied to the result of the join, at least logically.

> 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.4.14#64029)