[DERBY-7154] Hash join optimization error for join with multiple nested joins

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.15.2.0, 10.16.1.1
• Component/s: SQL
• Labels: join subquery
• Urgency: Normal
• Issue & fix info: Repro attached
• Bug behavior facts: Seen in production
• Assignee: Unassigned
• Reporter: Marco Belladelli
• Created: 19/May/23 14:29
• Updated: 22/May/23 19:03

Description

While executing a query that joins a table to another nested-join structure we encountered the following error:

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

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:

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'; 

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.