Examples with ROW_NUMBER io.trino.sql.planner.plan.TopNRankingNode.RankingType.ROW_NUMBER used on opensource projects

Example 1 with ROW_NUMBER

use of io.trino.sql.planner.plan.TopNRankingNode.RankingType.ROW_NUMBER 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()))));
}