Examples with CURRENT_ROW io.trino.sql.tree.FrameBound.Type.CURRENT_ROW used on opensource projects

Example 1 with CURRENT_ROW

use of io.trino.sql.tree.FrameBound.Type.CURRENT_ROW 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 CURRENT_ROW

use of io.trino.sql.tree.FrameBound.Type.CURRENT_ROW in project trino by trinodb.

the class TestWindowClause method testPreprojectExpressions.

@Test
public void testPreprojectExpressions() {
    @Language("SQL") String sql = "SELECT max(b) OVER w3 FROM (VALUES (1, 1, 1)) t(a, b, c) WINDOW w1 AS (PARTITION BY a + 1), w2 AS (w1 ORDER BY b + 2), w3 AS (w2 RANGE c + 3 PRECEDING)";
    PlanMatchPattern pattern = anyTree(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of("expr_a"), ImmutableList.of("expr_b"), ImmutableMap.of("expr_b", SortOrder.ASC_NULLS_LAST))).addFunction("max_result", functionCall("max", ImmutableList.of("b")), createTestMetadataManager().resolveFunction(TEST_SESSION, QualifiedName.of("max"), fromTypes(INTEGER)), windowFrame(RANGE, PRECEDING, Optional.of("frame_start"), Optional.of("expr_b"), CURRENT_ROW, Optional.empty(), Optional.empty())), project(ImmutableMap.of("frame_start", expression(new FunctionCall(QualifiedName.of("$operator$subtract"), ImmutableList.of(new SymbolReference("expr_b"), new SymbolReference("expr_c"))))), anyTree(project(ImmutableMap.of("expr_a", expression("a + 1"), "expr_b", expression("b + 2"), "expr_c", expression("c + 3")), anyTree(values("a", "b", "c")))))));
    assertPlan(sql, CREATED, pattern);
}

Example 3 with CURRENT_ROW

use of io.trino.sql.tree.FrameBound.Type.CURRENT_ROW in project trino by trinodb.

the class TestWindowFrameRange method testFramePrecedingWithSortKeyCoercions.

@Test
public void testFramePrecedingWithSortKeyCoercions() {
    @Language("SQL") String sql = "SELECT array_agg(key) OVER(ORDER BY key RANGE x PRECEDING) " + "FROM (VALUES (1, 1.1), (2, 2.2)) t(key, x)";
    PlanMatchPattern pattern = anyTree(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of(), ImmutableList.of("key"), ImmutableMap.of("key", SortOrder.ASC_NULLS_LAST))).addFunction("array_agg_result", functionCall("array_agg", ImmutableList.of("key")), createTestMetadataManager().resolveFunction(TEST_SESSION, QualifiedName.of("array_agg"), fromTypes(INTEGER)), windowFrame(RANGE, PRECEDING, Optional.of("frame_start_value"), Optional.of("key_for_frame_start_comparison"), CURRENT_ROW, Optional.empty(), Optional.empty())), project(ImmutableMap.of("key_for_frame_start_comparison", expression("CAST(key AS decimal(12, 1))")), project(ImmutableMap.of("frame_start_value", expression(new FunctionCall(QualifiedName.of("$operator$subtract"), ImmutableList.of(new SymbolReference("key_for_frame_start_calculation"), new SymbolReference("x"))))), project(ImmutableMap.of("key_for_frame_start_calculation", expression("CAST(key AS decimal(10, 0))")), filter("IF((x >= CAST(0 AS DECIMAL(2,1))), " + "true, " + "CAST(fail(CAST('Window frame offset value must not be negative or null' AS varchar)) AS boolean))", anyTree(values(ImmutableList.of("key", "x"), ImmutableList.of(ImmutableList.of(new LongLiteral("1"), new DecimalLiteral("1.1")), ImmutableList.of(new LongLiteral("2"), new DecimalLiteral("2.2")))))))))));
    assertPlan(sql, CREATED, pattern);
}

Example 4 with CURRENT_ROW

use of io.trino.sql.tree.FrameBound.Type.CURRENT_ROW in project trino by trinodb.

the class TestPrunePattenRecognitionColumns method testPruneUnreferencedWindowFunctionAndSources.

@Test
public void testPruneUnreferencedWindowFunctionAndSources() {
    ResolvedFunction lag = createTestMetadataManager().resolveFunction(tester().getSession(), QualifiedName.of("lag"), fromTypes(BIGINT));
    // remove window function "lag" and input symbol "b" used only by that function
    tester().assertThat(new PrunePattenRecognitionColumns()).on(p -> p.project(Assignments.identity(p.symbol("measure")), p.patternRecognition(builder -> builder.addWindowFunction(p.symbol("lag"), new WindowNode.Function(lag, ImmutableList.of(p.symbol("b").toSymbolReference()), DEFAULT_FRAME, false)).addMeasure(p.symbol("measure"), "LAST(X.a)", BIGINT).rowsPerMatch(WINDOW).frame(new WindowNode.Frame(ROWS, CURRENT_ROW, Optional.empty(), Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty())).skipTo(NEXT).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.values(p.symbol("a"), p.symbol("b")))))).matches(strictProject(ImmutableMap.of("measure", expression("measure")), patternRecognition(builder -> builder.addMeasure("measure", "LAST(X.a)", BIGINT).rowsPerMatch(WINDOW).frame(windowFrame(ROWS, CURRENT_ROW, Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty())).skipTo(NEXT).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true"), strictProject(ImmutableMap.of("a", expression("a")), values("a", "b")))));
}

Example 5 with CURRENT_ROW

use of io.trino.sql.tree.FrameBound.Type.CURRENT_ROW in project trino by trinodb.

the class TestPrunePattenRecognitionColumns method testPruneUnreferencedMeasureAndSources.

@Test
public void testPruneUnreferencedMeasureAndSources() {
    ResolvedFunction lag = createTestMetadataManager().resolveFunction(tester().getSession(), QualifiedName.of("lag"), fromTypes(BIGINT));
    // remove row pattern measure "measure" and input symbol "a" used only by that measure
    tester().assertThat(new PrunePattenRecognitionColumns()).on(p -> p.project(Assignments.identity(p.symbol("lag")), p.patternRecognition(builder -> builder.addWindowFunction(p.symbol("lag"), new WindowNode.Function(lag, ImmutableList.of(p.symbol("b").toSymbolReference()), DEFAULT_FRAME, false)).addMeasure(p.symbol("measure"), "LAST(X.a)", BIGINT).rowsPerMatch(WINDOW).frame(new WindowNode.Frame(ROWS, CURRENT_ROW, Optional.empty(), Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty())).skipTo(NEXT).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.values(p.symbol("a"), p.symbol("b")))))).matches(strictProject(ImmutableMap.of("lag", expression("lag")), patternRecognition(builder -> builder.addFunction("lag", functionCall("lag", ImmutableList.of("b"))).rowsPerMatch(WINDOW).frame(windowFrame(ROWS, CURRENT_ROW, Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty())).skipTo(NEXT).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true"), strictProject(ImmutableMap.of("b", expression("b")), values("a", "b")))));
}