Examples with ASC_NULLS_LAST io.trino.spi.connector.SortOrder.ASC_NULLS_LAST used on opensource projects

Example 1 with ASC_NULLS_LAST

use of io.trino.spi.connector.SortOrder.ASC_NULLS_LAST in project trino by trinodb.

the class TestLogicalPlanner method testDoNotPlanUnreferencedRowPatternMeasures.

@Test
public void testDoNotPlanUnreferencedRowPatternMeasures() {
    // row pattern measure `label` is not referenced
    assertPlan("SELECT val OVER w " + "          FROM (VALUES (1, 90)) t(id, value) " + "          WINDOW w AS ( " + "                   ORDER BY id " + "                   MEASURES " + "                            RUNNING LAST(value) AS val, " + "                            CLASSIFIER() AS label " + "                   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING " + "                   PATTERN (A+) " + "                   DEFINE A AS true " + "          )", output(project(patternRecognition(builder -> builder.specification(specification(ImmutableList.of(), ImmutableList.of("id"), ImmutableMap.of("id", ASC_NULLS_LAST))).addMeasure("val", "LAST(value)", INTEGER).rowsPerMatch(WINDOW).frame(windowFrame(ROWS, CURRENT_ROW, Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty())).pattern(new IrQuantified(new IrLabel("A"), oneOrMore(true))).addVariableDefinition(new IrLabel("A"), "true"), values(ImmutableList.of("id", "value"), ImmutableList.of(ImmutableList.of(new LongLiteral("1"), new LongLiteral("90"))))))));
    // row pattern measure `label` is not referenced
    assertPlan("SELECT min(value) OVER w " + "          FROM (VALUES (1, 90)) t(id, value) " + "          WINDOW w AS ( " + "                   ORDER BY id " + "                   MEASURES CLASSIFIER() AS label " + "                   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING " + "                   PATTERN (A+) " + "                   DEFINE A AS true " + "          )", output(project(patternRecognition(builder -> builder.specification(specification(ImmutableList.of(), ImmutableList.of("id"), ImmutableMap.of("id", ASC_NULLS_LAST))).addFunction("min", functionCall("min", ImmutableList.of("value"))).rowsPerMatch(WINDOW).frame(windowFrame(ROWS, CURRENT_ROW, Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty())).pattern(new IrQuantified(new IrLabel("A"), oneOrMore(true))).addVariableDefinition(new IrLabel("A"), "true"), values(ImmutableList.of("id", "value"), ImmutableList.of(ImmutableList.of(new LongLiteral("1"), new LongLiteral("90"))))))));
}

Example 2 with ASC_NULLS_LAST

use of io.trino.spi.connector.SortOrder.ASC_NULLS_LAST 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 3 with ASC_NULLS_LAST

use of io.trino.spi.connector.SortOrder.ASC_NULLS_LAST in project trino by trinodb.

the class TestMergePatternRecognitionNodes method testMergeWithoutProject.

@Test
public void testMergeWithoutProject() {
    ResolvedFunction lag = createTestMetadataManager().resolveFunction(tester().getSession(), QualifiedName.of("lag"), fromTypes(BIGINT));
    tester().assertThat(new MergePatternRecognitionNodesWithoutProject()).on(p -> p.patternRecognition(parentBuilder -> parentBuilder.partitionBy(ImmutableList.of(p.symbol("c"))).orderBy(new OrderingScheme(ImmutableList.of(p.symbol("d")), ImmutableMap.of(p.symbol("d"), ASC_NULLS_LAST))).addMeasure(p.symbol("parent_measure"), "LAST(X.b)", BIGINT).addWindowFunction(p.symbol("parent_function"), new WindowNode.Function(lag, ImmutableList.of(p.symbol("a").toSymbolReference()), DEFAULT_FRAME, false)).rowsPerMatch(WINDOW).frame(new WindowNode.Frame(ROWS, CURRENT_ROW, Optional.empty(), Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty())).skipTo(LAST, new IrLabel("X")).seek().addSubset(new IrLabel("U"), ImmutableSet.of(new IrLabel("X"))).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.patternRecognition(childBuilder -> childBuilder.partitionBy(ImmutableList.of(p.symbol("c"))).orderBy(new OrderingScheme(ImmutableList.of(p.symbol("d")), ImmutableMap.of(p.symbol("d"), ASC_NULLS_LAST))).addMeasure(p.symbol("child_measure"), "FIRST(X.a)", BIGINT).addWindowFunction(p.symbol("child_function"), new WindowNode.Function(lag, ImmutableList.of(p.symbol("b").toSymbolReference()), DEFAULT_FRAME, false)).rowsPerMatch(WINDOW).frame(new WindowNode.Frame(ROWS, CURRENT_ROW, Optional.empty(), Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty())).skipTo(LAST, new IrLabel("X")).seek().addSubset(new IrLabel("U"), ImmutableSet.of(new IrLabel("X"))).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.values(p.symbol("a"), p.symbol("b"), p.symbol("c"), p.symbol("d"))))))).matches(patternRecognition(builder -> builder.specification(specification(ImmutableList.of("c"), ImmutableList.of("d"), ImmutableMap.of("d", ASC_NULLS_LAST))).addMeasure("parent_measure", "LAST(X.b)", BIGINT).addMeasure("child_measure", "FIRST(X.a)", BIGINT).addFunction("parent_function", functionCall("lag", ImmutableList.of("a"))).addFunction("child_function", functionCall("lag", ImmutableList.of("b"))).rowsPerMatch(WINDOW).frame(windowFrame(ROWS, CURRENT_ROW, Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty())).skipTo(LAST, new IrLabel("X")).seek().addSubset(new IrLabel("U"), ImmutableSet.of(new IrLabel("X"))).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true"), values("a", "b", "c", "d")));
}