Examples with TopNRankingSymbolMatcher io.trino.sql.planner.assertions.TopNRankingSymbolMatcher used on opensource projects

Example 1 with TopNRankingSymbolMatcher

use of io.trino.sql.planner.assertions.TopNRankingSymbolMatcher in project trino by trinodb.

the class TestWindowFilterPushDown method assertFilterAboveWindow.

private void assertFilterAboveWindow(String rankingFunction, RankingType rankingType) {
    @Language("SQL") String sql = format("SELECT * FROM " + "(SELECT %s() OVER (PARTITION BY suppkey ORDER BY orderkey) partition_ranking FROM lineitem) " + "WHERE partition_ranking < 10", rankingFunction);
    assertPlanWithSession(sql, optimizeTopNRanking(true), true, anyTree(anyNot(FilterNode.class, topNRanking(pattern -> pattern.rankingType(rankingType).maxRankingPerPartition(9), anyTree(tableScan("lineitem"))))));
    assertPlanWithSession(sql, optimizeTopNRanking(false), true, anyTree(node(FilterNode.class, anyTree(node(WindowNode.class, anyTree(tableScan("lineitem")))))));
    // remove subplan if predicate on row number symbol can't be satisfied
    assertPlanWithSession(format("SELECT * FROM (SELECT name, %s() OVER(ORDER BY name) FROM nation) t(name, ranking) WHERE ranking < 0", rankingFunction), optimizeTopNRanking(true), true, output(ImmutableList.of("name", "ranking"), values("name", "ranking")));
    // optimize to TopNRanking on the basis of predicate; remove filter because predicate is satisfied
    assertPlanWithSession(format("SELECT * FROM (SELECT name, %s() OVER(ORDER BY name) FROM nation) t(name, ranking) WHERE ranking > 0 AND ranking < 2", rankingFunction), optimizeTopNRanking(true), true, output(ImmutableList.of("name", "ranking"), topNRanking(pattern -> pattern.rankingType(rankingType).maxRankingPerPartition(1).specification(ImmutableList.of(), ImmutableList.of("name"), ImmutableMap.of("name", ASC_NULLS_LAST)), any(tableScan("nation", ImmutableMap.of("name", "name")))).withAlias("ranking", new TopNRankingSymbolMatcher())));
    // optimize to TopNRanking on the basis of predicate; remove filter because predicate is satisfied
    assertPlanWithSession(format("SELECT * FROM (SELECT name, %s() OVER(ORDER BY name) FROM nation) t(name, ranking) WHERE ranking <= 1", rankingFunction), optimizeTopNRanking(true), true, output(ImmutableList.of("name", "ranking"), topNRanking(pattern -> pattern.rankingType(rankingType).maxRankingPerPartition(1).specification(ImmutableList.of(), ImmutableList.of("name"), ImmutableMap.of("name", ASC_NULLS_LAST)), any(tableScan("nation", ImmutableMap.of("name", "name")))).withAlias("ranking", new TopNRankingSymbolMatcher())));
    // optimize to TopNRanking on the basis of predicate; remove filter because predicate is satisfied
    assertPlanWithSession(format("SELECT * FROM (SELECT name, %s() OVER(ORDER BY name) FROM nation) t(name, ranking) WHERE ranking <= 1 AND ranking > -10", rankingFunction), optimizeTopNRanking(true), true, output(ImmutableList.of("name", "ranking"), topNRanking(pattern -> pattern.rankingType(rankingType).maxRankingPerPartition(1).specification(ImmutableList.of(), ImmutableList.of("name"), ImmutableMap.of("name", ASC_NULLS_LAST)), any(tableScan("nation", ImmutableMap.of("name", "name")))).withAlias("ranking", new TopNRankingSymbolMatcher())));
    // optimize to TopNRanking on the basis of predicate; cannot remove filter because predicate is not satisfied
    assertPlanWithSession(format("SELECT * FROM (SELECT name, %s() OVER(ORDER BY name) FROM nation) t(name, ranking) WHERE ranking > 1 AND ranking < 3", rankingFunction), optimizeTopNRanking(true), true, output(ImmutableList.of("name", "ranking"), filter("(ranking > BIGINT '1') AND (ranking < BIGINT '3')", topNRanking(pattern -> pattern.rankingType(rankingType).maxRankingPerPartition(2).specification(ImmutableList.of(), ImmutableList.of("name"), ImmutableMap.of("name", ASC_NULLS_LAST)), any(tableScan("nation", ImmutableMap.of("name", "name")))).withAlias("ranking", new TopNRankingSymbolMatcher()))));
}

Example 2 with TopNRankingSymbolMatcher

use of io.trino.sql.planner.assertions.TopNRankingSymbolMatcher in project trino by trinodb.

the class TestPushdownFilterIntoWindow method assertKeepFilter.

private void assertKeepFilter(String rankingFunctionName) {
    ResolvedFunction ranking = tester().getMetadata().resolveFunction(tester().getSession(), QualifiedName.of(rankingFunctionName), fromTypes());
    tester().assertThat(new PushdownFilterIntoWindow(tester().getPlannerContext())).on(p -> {
        Symbol rowNumberSymbol = p.symbol("row_number_1");
        Symbol a = p.symbol("a", BIGINT);
        OrderingScheme orderingScheme = new OrderingScheme(ImmutableList.of(a), ImmutableMap.of(a, SortOrder.ASC_NULLS_FIRST));
        return p.filter(expression("cast(3 as bigint) < row_number_1 and row_number_1 < cast(100 as bigint)"), p.window(new WindowNode.Specification(ImmutableList.of(a), Optional.of(orderingScheme)), ImmutableMap.of(rowNumberSymbol, newWindowNodeFunction(ranking, a)), p.values(p.symbol("a"))));
    }).matches(filter("cast(3 as bigint) < row_number_1 and row_number_1 < cast(100 as bigint)", topNRanking(pattern -> pattern.partial(false).maxRankingPerPartition(99).specification(ImmutableList.of("a"), ImmutableList.of("a"), ImmutableMap.of("a", SortOrder.ASC_NULLS_FIRST)), values("a")).withAlias("row_number_1", new TopNRankingSymbolMatcher())));
    tester().assertThat(new PushdownFilterIntoWindow(tester().getPlannerContext())).on(p -> {
        Symbol rowNumberSymbol = p.symbol("row_number_1");
        Symbol a = p.symbol("a", BIGINT);
        OrderingScheme orderingScheme = new OrderingScheme(ImmutableList.of(a), ImmutableMap.of(a, SortOrder.ASC_NULLS_FIRST));
        return p.filter(expression("row_number_1 < cast(100 as bigint) and a = 1"), p.window(new WindowNode.Specification(ImmutableList.of(a), Optional.of(orderingScheme)), ImmutableMap.of(rowNumberSymbol, newWindowNodeFunction(ranking, a)), p.values(p.symbol("a"))));
    }).matches(filter("a = 1", topNRanking(pattern -> pattern.partial(false).maxRankingPerPartition(99).specification(ImmutableList.of("a"), ImmutableList.of("a"), ImmutableMap.of("a", SortOrder.ASC_NULLS_FIRST)), values("a")).withAlias("row_number_1", new TopNRankingSymbolMatcher())));
}