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

Example 1 with RowNumberSymbolMatcher

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

the class TestLogicalPlanner method testOffset.

@Test
public void testOffset() {
    assertPlan("SELECT name FROM nation OFFSET 2 ROWS", any(strictProject(ImmutableMap.of("name", new ExpressionMatcher("name")), filter("row_num > BIGINT '2'", rowNumber(pattern -> pattern.partitionBy(ImmutableList.of()), any(tableScan("nation", ImmutableMap.of("name", "name")))).withAlias("row_num", new RowNumberSymbolMatcher())))));
    assertPlan("SELECT name FROM nation ORDER BY regionkey OFFSET 2 ROWS", any(strictProject(ImmutableMap.of("name", new ExpressionMatcher("name")), filter("row_num > BIGINT '2'", rowNumber(pattern -> pattern.partitionBy(ImmutableList.of()), anyTree(sort(ImmutableList.of(sort("regionkey", ASCENDING, LAST)), any(tableScan("nation", ImmutableMap.of("name", "name", "regionkey", "regionkey")))))).withAlias("row_num", new RowNumberSymbolMatcher())))));
    assertPlan("SELECT name FROM nation ORDER BY regionkey OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY", any(strictProject(ImmutableMap.of("name", new ExpressionMatcher("name")), filter("row_num > BIGINT '2'", rowNumber(pattern -> pattern.partitionBy(ImmutableList.of()), any(topN(7, ImmutableList.of(sort("regionkey", ASCENDING, LAST)), TopNNode.Step.FINAL, anyTree(tableScan("nation", ImmutableMap.of("name", "name", "regionkey", "regionkey")))))).withAlias("row_num", new RowNumberSymbolMatcher())))));
    assertPlan("SELECT name FROM nation OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY", any(strictProject(ImmutableMap.of("name", new ExpressionMatcher("name")), filter("row_num > BIGINT '2'", rowNumber(pattern -> pattern.partitionBy(ImmutableList.of()), limit(7, any(tableScan("nation", ImmutableMap.of("name", "name"))))).withAlias("row_num", new RowNumberSymbolMatcher())))));
}

Example 2 with RowNumberSymbolMatcher

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

the class TestPushPredicateThroughProjectIntoRowNumber method testPredicateSatisfied.

@Test
public void testPredicateSatisfied() {
    tester().assertThat(new PushPredicateThroughProjectIntoRowNumber(tester().getPlannerContext())).on(p -> {
        Symbol a = p.symbol("a");
        Symbol rowNumber = p.symbol("row_number");
        return p.filter(PlanBuilder.expression("row_number < 5"), p.project(Assignments.identity(rowNumber), p.rowNumber(ImmutableList.of(), Optional.of(3), rowNumber, p.values(a))));
    }).matches(project(ImmutableMap.of("row_number", expression("row_number")), rowNumber(pattern -> pattern.maxRowCountPerPartition(Optional.of(3)), values(ImmutableList.of("a"))).withAlias("row_number", new RowNumberSymbolMatcher())));
    tester().assertThat(new PushPredicateThroughProjectIntoRowNumber(tester().getPlannerContext())).on(p -> {
        Symbol a = p.symbol("a");
        Symbol rowNumber = p.symbol("row_number");
        return p.filter(PlanBuilder.expression("row_number < 3"), p.project(Assignments.identity(rowNumber), p.rowNumber(ImmutableList.of(), Optional.of(5), rowNumber, p.values(a))));
    }).matches(project(ImmutableMap.of("row_number", expression("row_number")), rowNumber(pattern -> pattern.maxRowCountPerPartition(Optional.of(2)), values(ImmutableList.of("a"))).withAlias("row_number", new RowNumberSymbolMatcher())));
}

Example 3 with RowNumberSymbolMatcher

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

the class TestWindowFilterPushDown method testFilterAboveRowNumber.

@Test
public void testFilterAboveRowNumber() {
    // remove subplan if predicate on row number symbol can't be satisfied
    assertPlan("SELECT * FROM (SELECT name, row_number() OVER() FROM nation) t(name, row_number) WHERE row_number < 0", output(ImmutableList.of("name", "row_number"), values("name", "row_number")));
    // include limit into RowNUmberNode on the basis of predicate; remove filter because predicate is satisfied
    assertPlan("SELECT * FROM (SELECT name, row_number() OVER() FROM nation) t(name, row_number) WHERE row_number < 2", output(ImmutableList.of("name", "row_number"), rowNumber(pattern -> pattern.maxRowCountPerPartition(Optional.of(1)), any(tableScan("nation", ImmutableMap.of("name", "name")))).withAlias("row_number", new RowNumberSymbolMatcher())));
    // include limit into RowNUmberNode on the basis of predicate; remove filter because predicate is satisfied
    assertPlan("SELECT * FROM (SELECT name, row_number() OVER() FROM nation) t(name, row_number) WHERE row_number <= 1", output(ImmutableList.of("name", "row_number"), rowNumber(pattern -> pattern.maxRowCountPerPartition(Optional.of(1)), any(tableScan("nation", ImmutableMap.of("name", "name")))).withAlias("row_number", new RowNumberSymbolMatcher())));
    // include limit into RowNUmberNode on the basis of predicate; remove filter because predicate is satisfied
    assertPlan("SELECT * FROM (SELECT name, row_number() OVER() FROM nation) t(name, row_number) WHERE row_number <= 1 AND row_number > -10", output(ImmutableList.of("name", "row_number"), rowNumber(pattern -> pattern.maxRowCountPerPartition(Optional.of(1)), any(tableScan("nation", ImmutableMap.of("name", "name")))).withAlias("row_number", new RowNumberSymbolMatcher())));
    // include limit into RowNUmberNode on the basis of predicate; cannot remove filter because predicate is not satisfied
    assertPlan("SELECT * FROM (SELECT name, row_number() OVER() FROM nation) t(name, row_number) WHERE row_number > 1 AND row_number < 3", output(ImmutableList.of("name", "row_number"), filter("(row_number > BIGINT '1') AND (row_number < BIGINT '3')", rowNumber(pattern -> pattern.maxRowCountPerPartition(Optional.of(2)), any(tableScan("nation", ImmutableMap.of("name", "name")))).withAlias("row_number", new RowNumberSymbolMatcher()))));
}

Example 4 with RowNumberSymbolMatcher

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

the class TestLogicalPlanner method testWithTies.

@Test
public void testWithTies() {
    assertPlan("SELECT name, regionkey FROM nation ORDER BY regionkey FETCH FIRST 6 ROWS WITH TIES", any(strictProject(ImmutableMap.of("name", new ExpressionMatcher("name"), "regionkey", new ExpressionMatcher("regionkey")), topNRanking(pattern -> pattern.specification(ImmutableList.of(), ImmutableList.of("regionkey"), ImmutableMap.of("regionkey", SortOrder.ASC_NULLS_LAST)).rankingType(RANK).maxRankingPerPartition(6).partial(false), anyTree(tableScan("nation", ImmutableMap.of("name", "name", "regionkey", "regionkey")))))));
    assertPlan("SELECT name, regionkey FROM nation ORDER BY regionkey OFFSET 10 ROWS FETCH FIRST 6 ROWS WITH TIES", any(strictProject(ImmutableMap.of("name", new ExpressionMatcher("name"), "regionkey", new ExpressionMatcher("regionkey")), filter("row_num > BIGINT '10'", rowNumber(pattern -> pattern.partitionBy(ImmutableList.of()), strictProject(ImmutableMap.of("name", new ExpressionMatcher("name"), "regionkey", new ExpressionMatcher("regionkey")), topNRanking(pattern -> pattern.specification(ImmutableList.of(), ImmutableList.of("regionkey"), ImmutableMap.of("regionkey", SortOrder.ASC_NULLS_LAST)).rankingType(RANK).maxRankingPerPartition(16).partial(false), anyTree(tableScan("nation", ImmutableMap.of("name", "name", "regionkey", "regionkey")))))).withAlias("row_num", new RowNumberSymbolMatcher())))));
}

Example 5 with RowNumberSymbolMatcher

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

the class TestPushdownFilterIntoRowNumber method testSourceRowNumber.

@Test
public void testSourceRowNumber() {
    tester().assertThat(new PushdownFilterIntoRowNumber(tester().getPlannerContext())).on(p -> {
        Symbol a = p.symbol("a");
        Symbol rowNumberSymbol = p.symbol("row_number_1");
        return p.filter(expression("row_number_1 < cast(100 as bigint)"), p.rowNumber(ImmutableList.of(a), Optional.empty(), rowNumberSymbol, p.values(a)));
    }).matches(rowNumber(rowNumber -> rowNumber.maxRowCountPerPartition(Optional.of(99)).partitionBy(ImmutableList.of("a")), values("a")));
    tester().assertThat(new PushdownFilterIntoRowNumber(tester().getPlannerContext())).on(p -> {
        Symbol a = p.symbol("a");
        Symbol rowNumberSymbol = p.symbol("row_number_1");
        return p.filter(expression("row_number_1 < cast(100 as bigint)"), p.rowNumber(ImmutableList.of(a), Optional.of(10), rowNumberSymbol, p.values(a)));
    }).matches(rowNumber(rowNumber -> rowNumber.maxRowCountPerPartition(Optional.of(10)).partitionBy(ImmutableList.of("a")), values("a")));
    tester().assertThat(new PushdownFilterIntoRowNumber(tester().getPlannerContext())).on(p -> {
        Symbol a = p.symbol("a");
        Symbol rowNumberSymbol = p.symbol("row_number_1");
        return p.filter(expression("cast(3 as bigint) < row_number_1 and row_number_1 < cast(5 as bigint)"), p.rowNumber(ImmutableList.of(a), Optional.of(10), rowNumberSymbol, p.values(a)));
    }).matches(filter("cast(3 as bigint) < row_number_1 and row_number_1 < cast(5 as bigint)", rowNumber(rowNumber -> rowNumber.maxRowCountPerPartition(Optional.of(4)).partitionBy(ImmutableList.of("a")), values("a")).withAlias("row_number_1", new RowNumberSymbolMatcher())));
    tester().assertThat(new PushdownFilterIntoRowNumber(tester().getPlannerContext())).on(p -> {
        Symbol a = p.symbol("a");
        Symbol rowNumberSymbol = p.symbol("row_number_1");
        return p.filter(expression("row_number_1 < cast(5 as bigint) and a = 1"), p.rowNumber(ImmutableList.of(a), Optional.of(10), rowNumberSymbol, p.values(a)));
    }).matches(filter("a = 1", rowNumber(rowNumber -> rowNumber.maxRowCountPerPartition(Optional.of(4)).partitionBy(ImmutableList.of("a")), values("a")).withAlias("row_number_1", new RowNumberSymbolMatcher())));
}