NextJoin::isWorse logic is incorrect #549
Description
We have ‘this’ plan and ‘other’. isWorse should return true if there is no way ‘this’ is cheaper than ‘other’.
If both plans have same distribution, then we just compare cost(this) to cost(other) and return true if cost(this) is larger.
However, if distributions are different, then we should check if cost(other) + cost(shuffle) is still less then cost(this). In other words, 'other' is so cheap that even after adding shuffle it is still cheaper than 'this' w/o shuffle. Obviously, other is much better plan and no point considering 'this'.
However, we check cost(this) + cost(shuffle) against cost(other). The fact that this plan is costlier than other after adding shuffle doesn’t mean this plan is no good. The shuffle may not be needed if other’s distribution is not interesting.
@MBkkt I believe you have discovered this issue earlier and commented about it somewhere. I cannot find this comment easily. If you have a link handy, please, add here. Do you already have a PR to fix this? If not, I'll make one.