My development team is seeing strange behaviour with Denodo execution plans when using left outer joins and filters.
We have a query that has multiple left joins to get data from a lookup table. When we execute the query, it takes approximately 20 seconds to return all data. This is the expected exection time.
When we add a filter to one of the tables on the right of the left outer join, the query takes over 8 minutes to complete.
Reviewing the execution trace of both queries, we find that the query with the filter adds a Projection Plan at every left outer join, which includes Projected Fields for every column of every view on the right side of the join, even the columns not used in the query. It also adds an additional inner join as the final join. These projection plans with every column and inner joins are causing the query to run extremely slow. Over 8 minutes to complete.
Researching the knowledge base and support articles, I found a reference to left joins being converted to inner joins via the query optimizer due to an enabled setting called Automatic Simplification of Queries.
If I add a CONTEXT clause to disable Automatic Simplification of Queries to my filtered query, CONTEXT ('simplify'='off'), the inner join and additional Projection Plans with Projected Fields are not added to the execution plan. The execution plan looks similar to the unfiltered query and the data is returned in 20 seconds.
Is there a way to overcome this issue without disabling Automatic Simplification of Queries on queries impacted by this feature?
Is this a bug in Denodo 7's execution engine or is this by design?
