[ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16050135#comment-16050135 ]
wangwenli commented on DERBY-6358: ---------------------------------- the reporter says: {quote} See part 2 of the SQL Standard, section 7.4 (<table expression>), general rule 1 {quote} i copied the rule here: {quote} If all optional clauses are omitted, then the result of the <table expression> is the same as the result of the <from clause>. Otherwise, each specified clause is applied to the result of the previously specified clause and the result of the <table expression> is the result of the application of the last specified clause. {quote} i cann't see putting where clause before join violate the rule, any detail explanation? > 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)