[jira] [Commented] (DERBY-7154) Hash join optimization error for join with multiple nested joins

["Richard N. Hillegas (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-7154?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17725078#comment-17725078 ] 

Richard N. Hillegas commented on DERBY-7154:
--------------------------------------------

Attached derby-7154.sql. This combines the repro into a single ij script. I see the the error when I run the combined script against the development trunk.

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