[ 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)