You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Marco Belladelli (Jira)" <ji...@apache.org> on 2023/05/19 14:30:00 UTC
[jira] [Created] (DERBY-7154) Hash join optimization error for join with multiple nested joins
Marco Belladelli created DERBY-7154:
---------------------------------------
Summary: 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.16.1.1, 10.15.2.0
Reporter: Marco Belladelli
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: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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)