[CALCITE-1018] SortJoinTransposeRule not firing due to getMaxRowCount(RelSubset) returning null

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.6.0
• Assignee: Wei Xue
• Reporter: Wei Xue
• Created: 11/Dec/15 02:26
• Updated: 26/Sep/20 00:08
• Resolved: 12/Dec/15 03:19

Description

getMaxRowCount(RelSubset) defaults to getMaxRowCount(RelNode) and returns null, so if the Join rel has its outer child rel as a RelSubset, the SortJoinTransposeRule will fail the checkInputForCollationAndLimit check and will not proceed to push down a limit through join.
Before fix of CALCITE-995 and CALCITE-987, getMaxRowCount(RelSubset) would return positive infinity, which would cause similar situation but an opposite effect as firing the rule infinitely when the join's outer child is a RelSubset.
Neither of the above situation was reflected in the test cases for SortJoinTransposeRule in Calcite, since the RelSubset condition was not covered by RelOptRule test which uses HepPlanner running with just one or no more than a couple of rules together.
Basically we need a more accurate way to get max row count for RelSubset (similarly for HexVertex as well), otherwise checkInputForCollationAndLimit would either always fail or always succeed for a Limit over a Join over a RelSubset. But I assume, to be real accurate, we'd have to introduce a similar mechanism to one that computes bestCost in RelSubset, which I doubt would be worth it.
Another way, which might seem a little ugly, is to add something like "isSortPushedThrough()" in Sort rel, similar to "isSemiJoinDone()" in Join, in order to avoid the rule being fired infinitely.
In my Phoenix project, I applied a temporary fix, but it proved to work:

public class PhoenixRelMdMaxRowCount {
    public static final RelMetadataProvider SOURCE =
            ReflectiveRelMetadataProvider.reflectiveSource(
                BuiltInMethod.MAX_ROW_COUNT.method, new PhoenixRelMdMaxRowCount());

    private PhoenixRelMdMaxRowCount() {
    }

    public Double getMaxRowCount(RelSubset rel) {
        for (RelNode node : rel.getRels()) {
            if (node instanceof Sort) {
                Sort sort = (Sort) node;
                if (sort.fetch != null) {
                    return (double) RexLiteral.intValue(sort.fetch);
                }
            }
        }
        
        return Double.POSITIVE_INFINITY;
    }
}

Issue Links

• is blocked by: CALCITE-1019 RelMdUtil.checkInputForCollationAndLimit() was wrong with alreadySorted check
• is related to: CALCITE-794 Detect cycles when computing statistics
• is related to: CALCITE-987 Push limit 0 will result in an infinite loop
• is related to: CALCITE-995 Sort transpose rules might fall in an infinite loop
• relates to: CALCITE-4281 fix getMaxRowCount for RelSubset to return reasonable values