Compound join conditions and IsJoinClause

[ozgune]

We recently ran into an issue related to compound join conditions (#58) in a private Slack channel. @robin900 looked deeper into the issue and noted that fixing the issue is tricky due to multiple code paths that do join planning. I'm copy/pasting his notes from the Slack chat below.

The two most common patterns for us, when there is a compound join condition, are:

1) a JOIN b on a.dist_column = b.dist_column AND a.some_tstz <@ b.some_tstzrange 2) a JOIN b on a.dist_column = b.dist_column AND a.tstz < b.tstz

We also have a rare a JOIN b on a.dist_column = b.dist_column AND a.ctid < b.ctid but we can work around the ctid problem.

My understanding, after reviewing the citus code, is that it would take significant refactoring to evaluate the list of clauses all at once, instead of IsJoinClause() called in series on a clauseList.

I note that the dummy join a.fk_account_id = b.fk_account_id AND b.created_at = lower(a.during) works merely because IsJoinClause returns true for both clauses.

They use = and left and right expr reference different tables (different ->varno).

Reading the citus code, i see a confusion of what question the function IsJoinClause answers. This confusion leads to my confusion.

I see 2, perhaps 3, uses of the function:

1. In multi_join_order.c, to determine fixed join order for implicit joins; plus inside ApplicableJoinClauses where it’s just checking whether the left table and right table in each clause are applicable, and it’s not clear that equality operator is necessary.
2. In multi_logical_planner.c, to determine which clauses should participate in joins but not clear if equality operator is necessary for the logic to work.
3. A rough equivalent of IsJoinClause for lateral join optimization: https://github.com/citusdata/citus/blob/aa15043b0905a1fdba7f649d4986204c5efdf1e2/src/backend/distributed/planner/multi_logical_optimizer.c#L3431

[ozgune]

@robin900 -- the reason for these different code paths is historical. multi_join_order.c holds the main logic for join order planning. multi_logical_planner.c may contain join order related logic for OUTER JOINs.

The third code path for IsJoinClause relates to lateral joins and it's only enabled through a config flag. For fixing compound join conditions, we probably don't need to touch this function.

[ozgune]

I'm noting #26 and #264 as issues related to this one.

[mtuncer]

IsJoinClause is called form 3 different locations

1. ValidateClauseList() --> to make sure each expression is supported one. This enforces any join expression between tables is equi join. This function is also called from 2 places to validate query in regular planner and subquery pushdown planner.
2. JoinClauseList() --> extract join clauses from given clause list. This function is called from 4 different places for different purposes.
   • FixedJoinOrderList (outer joins) to detect join type (broadcast, colocated etc)
   • ApplicableJoinClauses() to find out applicable join clauses between given tables. This function is called from multi_join_order for join order selection, and from multi_logical_planner ApplyJoinRule to fill in join clause list for MultiJoin node.
   • multi_logical_optimizer.c:JoinOnPartitionColumn() to detect if two tables are joined on partition column
   • multi_logical_planner to send in join clauses for join order selection.
3. multi_phsical_planner.c:JoinSequenceArray()--> to determine applicability of join pruning with existing range tables and the new range table to be added to join sequence.

clause being a join clause and equi-join clause has sometimes same, sometimes different consequences. We need to operate on equi-joins when we are dealing with join sequence selection and join pruning, we can relax equi-join requirement for the rest of the places.

Testing is important here. Basic testing involving just 2 tables would not find any issues as likely problems to occur in join order selection. Complex join queries must be used.