db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
Date Wed, 19 Feb 2014 21:28:19 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mike Matrigali updated DERBY-6358:
----------------------------------

    Affects Version/s: 10.10.1.4
                       10.2.2.1

tried also against top of 10.10 branch and results look same as 10.2 and as described in JIRA
against 10.11.  So assume this is not a regression.   Behavior is likely to be in all apache
releases up through 10.11.  Only marking those I have tested:

ij version 10.10
CONNECTION0* -  jdbc:derby:wombat
* = current connection
ij> create table t1( a varchar( 10 ) );
0 rows inserted/updated/deleted
ij> create table t2( a varchar( 10 ) );
0 rows inserted/updated/deleted
ij> insert into t1( a ) values ( 'horse' ), ( 'apple' ), ( 'star' ), ( '6' );
4 rows inserted/updated/deleted
ij> insert into t2( a ) values ( '6' );
1 row inserted/updated/deleted
ij> -- 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;
1
-----------
6

1 row selected
ij> -- 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;
ERROR 22018: Invalid character string format for type INTEGER.


> 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.1.4, 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.5#6160)

Mime
View raw message