[SQL] Optimization removing a DISTINCT from the RHS of a LEFT JOIN - #6824
Conversation
This doesn't seem right. The column can also be null if there is a match but the value is null in the table. |
mythical-fred
left a comment
There was a problem hiding this comment.
Nice, tight optimization. The correctness argument is easy to check: with a LEFT join, an unmatched left row produces one NULL-padded row regardless of RHS multiplicity, and a matched left row produces rows where the right column is non-NULL (given non-nullability below the aggregate) — so the IS NULL filter deletes them whether the RHS is deduplicated or not. Guards match: LEFT only, Aggregate.Group.SIMPLE + empty agg calls + full-column grouping, and nullability checked on aggregate.getInput().getRowType() rather than the join-padded row type.
Tests are the strong part — six focused unit tests on RelBuilder plans plus SQL-level tests including a Postgres-validated segment query with a mixed IS NOT NULL / IS NULL pair, exercising exactly the branch the rule rewrites. LGTM.
Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
3933983 to
6ae14ae
Compare
mythical-fred
left a comment
There was a problem hiding this comment.
Rebased over main; content unchanged vs prior approved tip except a single showFinal() debug line removed from testSegment in AntiJoinDistinctTests.java. Approve stands.
Checklist
This will rewrite something like
by removing the
DISTINCTwhen thercolumn is from the RHS of the join (and is not nullable in the input).If the column is NULL it must be that it didn't have a match on the LHS, and thus duplicates don't matter.