[
https://issues.apache.org/jira/browse/DERBY-7154?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Marco Belladelli updated DERBY-7154:
------------------------------------
Description:
While executing a query that joins a table to another nested-join structure we
encountered the following error:
{code:text}
[42Y63][30000] Hash join requires an optimizable equijoin predicate on a column
in the selected index or heap. An optimizable equijoin predicate does not exist
on any column in table or index ''. Use the 'index' optimizer override to
specify such an index or the heap on table '' {code}
I've attached a script to create a simple schema needed to reproduce this issue
as well as another with the query itself.
The query is the following:
{code:sql}
select
z1_0.*
from
Zoo z1_0
join
(Mammal m1_0
join
Cat m1_1
on m1_0.animal=m1_1.mammal
left join
Dog m1_2
on m1_0.animal=m1_2.mammal
join
Animal m1_3
on m1_0.animal=m1_3.id)
on z1_0.id=m1_0.zoo_id
and m1_0.name='Walrus'; {code}
We noticed that we don't get any error when:
* moving the "Animal" join anywhere before the "Dog" one;
* making the "Dog" join non-left;
* removing the {{and m1_0.name='Walrus'}} condition from the root query join.
We tested this query with Apache Derby Embedded, both version 10.15.2.0 and
10.16.1.1.
was:
While executing a query that joins a table to another nested-join structure we
encountered the following error:
{code:java}
[42Y63][30000] Hash join requires an optimizable equijoin predicate on a column
in the selected index or heap. An optimizable equijoin predicate does not exist
on any column in table or index ''. Use the 'index' optimizer override to
specify such an index or the heap on table '' {code}
I've attached a script to create a simple schema needed to reproduce this issue
as well as another with the query itself.
The query is the following:
{code:java}
select
z1_0.*
from
Zoo z1_0
join
(Mammal m1_0
join
Cat m1_1
on m1_0.animal=m1_1.mammal
left join
Dog m1_2
on m1_0.animal=m1_2.mammal
join
Animal m1_3
on m1_0.animal=m1_3.id)
on z1_0.id=m1_0.zoo_id
and m1_0.name='Walrus'; {code}
We noticed that we don't get any error when:
* moving the "Animal" join anywhere before the "Dog" one;
* making the "Dog" join non-left;
* removing the {{and m1_0.name='Walrus'}} condition from the root query join.
We tested this query with Apache Derby Embedded, both version 10.15.2.0 and
10.16.1.1.
> Hash join optimization error for join with multiple nested joins
> ----------------------------------------------------------------
>
> Key: DERBY-7154
> URL: https://issues.apache.org/jira/browse/DERBY-7154
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.15.2.0, 10.16.1.1
> Reporter: Marco Belladelli
> Priority: Major
> Labels: join, subquery
> Attachments: problematic_query.sql, schema_creation.sql
>
>
> While executing a query that joins a table to another nested-join structure
> we encountered the following error:
> {code:text}
> [42Y63][30000] Hash join requires an optimizable equijoin predicate on a
> column in the selected index or heap. An optimizable equijoin predicate does
> not exist on any column in table or index ''. Use the 'index' optimizer
> override to specify such an index or the heap on table '' {code}
> I've attached a script to create a simple schema needed to reproduce this
> issue as well as another with the query itself.
> The query is the following:
> {code:sql}
> select
> z1_0.*
> from
> Zoo z1_0
> join
> (Mammal m1_0
> join
> Cat m1_1
> on m1_0.animal=m1_1.mammal
> left join
> Dog m1_2
> on m1_0.animal=m1_2.mammal
> join
> Animal m1_3
> on m1_0.animal=m1_3.id)
> on z1_0.id=m1_0.zoo_id
> and m1_0.name='Walrus'; {code}
> We noticed that we don't get any error when:
> * moving the "Animal" join anywhere before the "Dog" one;
> * making the "Dog" join non-left;
> * removing the {{and m1_0.name='Walrus'}} condition from the root query join.
>
> We tested this query with Apache Derby Embedded, both version 10.15.2.0 and
> 10.16.1.1.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
