Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
10.2.2.1, 10.10.2.0, 10.11.1.1
-
None
-
None
-
Release Note Needed, Repro attached
-
Seen in production, Wrong query result
Description
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.
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- is related to
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-