[jira] [Commented] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
[ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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 > (), 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)
[jira] [Commented] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
[
https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16050135#comment-16050135
]
wangwenli commented on DERBY-6358:
--
the reporter says:
{quote}
See part 2 of the SQL Standard, section 7.4 (), general rule 1
{quote}
i copied the rule here:
{quote}
If all optional clauses are omitted, then the result of the
is the same as
the result of the . Otherwise, each specified clause is applied to
the result of
the previously specified clause and the result of the 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
> (), 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)
[jira] [Commented] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
[ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14205556#comment-14205556 ] Ashutosh Chauhan commented on DERBY-6358: - Any update on this bug? I am from Apache Hive team. We strive to support multiple databases for our metastore and we hate to add Derby-specific workarounds. In particular, we would like to get rid of https://github.com/apache/hive/blob/trunk/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java#L931 This is causing us continuous grief : [HIVE-8739|https://issues.apache.org/jira/browse/HIVE-8739?focusedCommentId=14201301page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14201301] Is anyone working on this actively? 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.3.4#6332)
[jira] [Commented] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
[ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14205666#comment-14205666 ] Myrna van Lunteren commented on DERBY-6358: --- Alas, no one has volunteered to work on this at the moment. 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.3.4#6332)
[jira] [Commented] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
[ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13906101#comment-13906101 ] Mike Matrigali commented on DERBY-6358: --- Here is the 10.2 top of branch result from running script in JIRA description: ij version 10.2 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.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)
[jira] [Commented] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
[ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13784090#comment-13784090 ] Rick Hillegas commented on DERBY-6358: -- Note that the problem can't be worked around by using a subquery or a view. Both are flattened into the outer query block: -- using a view doesn't help. same problem. create view v as select t1.a x, t2.a y from t1 inner join t2 on t1.a = t2.a; select * from v where cast( v.x as int ) 5; -- using a suquery doesn't help. same problem. select * from ( select t1.a x, t2.a y from t1 inner join t2 on t1.a = t2.a ) v where cast( v.x as int ) 5; 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)
[jira] [Commented] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
[ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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)
[jira] [Commented] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
[ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13784192#comment-13784192 ] Sergey Shelukhin commented on DERBY-6358: - FWIW, in my case I was able to work around by adding (case when (all relevant join conditions) then cast(...) else null end) 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)
[jira] [Commented] (DERBY-6358) WHERE clause should be evaluated after the joins in the FROM clause
[ https://issues.apache.org/jira/browse/DERBY-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13781791#comment-13781791 ] Rick Hillegas commented on DERBY-6358: -- This bug was discussed on the following user thread: http://apache-database.10148.n7.nabble.com/order-of-evaluation-for-filters-in-the-query-td134264.html 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)
