use of io.trino.sql.tree.WindowFrame.Type.ROWS 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())))));
}
use of io.trino.sql.tree.WindowFrame.Type.ROWS 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"))))))));
}
use of io.trino.sql.tree.WindowFrame.Type.ROWS 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")))));
}
use of io.trino.sql.tree.WindowFrame.Type.ROWS 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")))));
}
use of io.trino.sql.tree.WindowFrame.Type.ROWS 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")));
}
Aggregations