rohangarg commented on PR #13201: URL: https://github.com/apache/druid/pull/13201#issuecomment-1284039437
@gianm : > Consider the case of two clauses, where the second clause uses output of the first in its condition, and where the second is otherwise convertible to filter but the first is not fully convertible. Is this handled properly? Do we have a test for this case? In this case, by 'output of the join' do you mean the right table column? If so, then the second join would not be fully converted. Both joins would stay intact and the derived/inferred filters from them would be pushed to the left side. > There's another potential issue I'm wondering about, related to reordering. If one of the earlier clauses is RIGHT OUTER or FULL OUTER (a "righty" join) then they generate nulls in columns associated with the base table, for any right-hand-side rows that don't match the join clause. In this case, it wouldn't be correct to reorder a later INNER join leftwards through the RIGHT OUTER or FULL OUTER clause. Please add a test case about this too. Thanks for catching that, you're right that fully converting inner joins after right/outer joins would lead to incorrect results since they could contain NULLs. Maybe we could keep those joins intact and also push the filter - but that can be taken up later upon more thinking. For now, I'll stop the clause conversion loop as soon as we see a 'righty' join. And will also write a test for this case. @paul-rogers : > how do we know the number? If a join is against an entire table, one can just look at the table cardinality (the sum of the row counts across all segments). If there is a filter on a table, estimating the post-filter row count is a difficult challenge -- one that all query planners wrestle with (and for which Calcite provides a cost-based algorithm.) Is this PR only considering the entire table cardinality? Currently, we apply the join-to-filter optimization when the right/build side table has been materialized fully in memory, so it means that we know the cardinality of that table. > Are we using Calcite to do the join reordering? If so, then it would be surprising if Calcite would allow invalid reordering. If we're doing it ourselves, then we do need to be careful: there are lots of tricky rules, such as the one Gian mentioned, for when join ordering is or is not allowed. Basically, if we're doing it ourselves, only reorder "plain" inner joins and we won't go wrong (though we may miss optimization opportunities.) Later, perhaps we can feed Calcite some proper cost estimates (however crude) and Calcite can do the task for us. No, we're not using the calcite's join reordering rule as of now. The current optimization can be thought of more as adaptive execution where after running all the necessary queries and building the right side table, we decide whether to push some inferred filters on the left side before running the join. Yes, you're right - currently we've decided to only do the filter inferencing for inner joins amongst a group of inner and left outer joins (and that too only if the inferred filter is on left table columns) and leave out right/outer joins. Sure, yes in future as more join support/requirement comes over we could also use the Calcite's rule in determining the join algorithm and the order. Thanks a lot @gianm and @paul-rogers for your thorough review and suggestions! -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
