[
https://issues.apache.org/jira/browse/DERBY-7154?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Richard N. Hillegas updated DERBY-7154:
---------------------------------------
Bug behavior facts: Seen in production
Issue & fix info: Repro attached
Urgency: Normal
> 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: derby-7154.sql, 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: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)
