use of io.trino.sql.tree.WindowFrame.Type.ROWS in project trino by trinodb.
the class TestMergePatternRecognitionNodes method testParentDependsOnSourceCreatedOutputs.
@Test
public void testParentDependsOnSourceCreatedOutputs() {
ResolvedFunction lag = createTestMetadataManager().resolveFunction(tester().getSession(), QualifiedName.of("lag"), fromTypes(BIGINT));
// parent node's measure depends on child node's measure output
tester().assertThat(new MergePatternRecognitionNodesWithoutProject()).on(p -> p.patternRecognition(parentBuilder -> parentBuilder.addMeasure(p.symbol("dependent"), "LAST(X.measure)", BIGINT).rowsPerMatch(ALL_SHOW_EMPTY).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.patternRecognition(childBuilder -> childBuilder.addMeasure(p.symbol("measure"), "MATCH_NUMBER()", BIGINT).rowsPerMatch(ALL_SHOW_EMPTY).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.values(p.symbol("a"))))))).doesNotFire();
// parent node's measure depends on child node's window function output
tester().assertThat(new MergePatternRecognitionNodesWithoutProject()).on(p -> p.patternRecognition(parentBuilder -> parentBuilder.addMeasure(p.symbol("dependent"), "LAST(X.function)", BIGINT).rowsPerMatch(WINDOW).frame(new WindowNode.Frame(ROWS, CURRENT_ROW, Optional.empty(), Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty())).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.patternRecognition(childBuilder -> childBuilder.addWindowFunction(p.symbol("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())).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.values(p.symbol("a"))))))).doesNotFire();
// parent node's window function depends on child node's window function output
tester().assertThat(new MergePatternRecognitionNodesWithoutProject()).on(p -> p.patternRecognition(parentBuilder -> parentBuilder.addWindowFunction(p.symbol("dependent"), new WindowNode.Function(lag, ImmutableList.of(p.symbol("function").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())).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.patternRecognition(childBuilder -> childBuilder.addWindowFunction(p.symbol("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())).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.values(p.symbol("a"))))))).doesNotFire();
// parent node's window function depends on child node's measure output
tester().assertThat(new MergePatternRecognitionNodesWithoutProject()).on(p -> p.patternRecognition(parentBuilder -> parentBuilder.addWindowFunction(p.symbol("dependent"), new WindowNode.Function(lag, ImmutableList.of(p.symbol("measure").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())).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.patternRecognition(childBuilder -> childBuilder.addMeasure(p.symbol("measure"), "MATCH_NUMBER()", BIGINT).rowsPerMatch(WINDOW).frame(new WindowNode.Frame(ROWS, CURRENT_ROW, Optional.empty(), Optional.empty(), UNBOUNDED_FOLLOWING, Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty())).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.values(p.symbol("a"))))))).doesNotFire();
}
use of io.trino.sql.tree.WindowFrame.Type.ROWS 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())))));
}
use of io.trino.sql.tree.WindowFrame.Type.ROWS in project trino by trinodb.
the class QueryPlanner method planExpand.
public RelationPlan planExpand(Query query) {
checkArgument(analysis.isExpandableQuery(query), "query is not registered as expandable");
Union union = (Union) query.getQueryBody();
ImmutableList.Builder<NodeAndMappings> recursionSteps = ImmutableList.builder();
// plan anchor relation
Relation anchorNode = union.getRelations().get(0);
RelationPlan anchorPlan = new RelationPlanner(analysis, symbolAllocator, idAllocator, lambdaDeclarationToSymbolMap, plannerContext, outerContext, session, recursiveSubqueries).process(anchorNode, null);
// prune anchor plan outputs to contain only the symbols exposed in the scope
NodeAndMappings prunedAnchorPlan = pruneInvisibleFields(anchorPlan, idAllocator);
// if the anchor plan has duplicate output symbols, add projection on top to make the symbols unique
// This is necessary to successfully unroll recursion: the recursion step relation must follow
// the same layout while it might not have duplicate outputs where the anchor plan did
NodeAndMappings disambiguatedAnchorPlan = disambiguateOutputs(prunedAnchorPlan, symbolAllocator, idAllocator);
anchorPlan = new RelationPlan(disambiguatedAnchorPlan.getNode(), analysis.getScope(query), disambiguatedAnchorPlan.getFields(), outerContext);
recursionSteps.add(copy(anchorPlan.getRoot(), anchorPlan.getFieldMappings()));
// plan recursion step
Relation recursionStepRelation = union.getRelations().get(1);
RelationPlan recursionStepPlan = new RelationPlanner(analysis, symbolAllocator, idAllocator, lambdaDeclarationToSymbolMap, plannerContext, outerContext, session, ImmutableMap.of(NodeRef.of(analysis.getRecursiveReference(query)), anchorPlan)).process(recursionStepRelation, null);
// coerce recursion step outputs and prune them to contain only the symbols exposed in the scope
NodeAndMappings coercedRecursionStep;
List<Type> types = analysis.getRelationCoercion(recursionStepRelation);
if (types == null) {
coercedRecursionStep = pruneInvisibleFields(recursionStepPlan, idAllocator);
} else {
coercedRecursionStep = coerce(recursionStepPlan, types, symbolAllocator, idAllocator);
}
NodeAndMappings replacementSpot = new NodeAndMappings(anchorPlan.getRoot(), anchorPlan.getFieldMappings());
PlanNode recursionStep = coercedRecursionStep.getNode();
List<Symbol> mappings = coercedRecursionStep.getFields();
// unroll recursion
int maxRecursionDepth = getMaxRecursionDepth(session);
for (int i = 0; i < maxRecursionDepth; i++) {
recursionSteps.add(copy(recursionStep, mappings));
NodeAndMappings replacement = copy(recursionStep, mappings);
// if the recursion step plan has duplicate output symbols, add projection on top to make the symbols unique
// This is necessary to successfully unroll recursion: the relation on the next recursion step must follow
// the same layout while it might not have duplicate outputs where the plan for this step did
replacement = disambiguateOutputs(replacement, symbolAllocator, idAllocator);
recursionStep = replace(recursionStep, replacementSpot, replacement);
replacementSpot = replacement;
}
// after the last recursion step, check if the recursion converged. the last step is expected to return empty result
// 1. append window to count rows
NodeAndMappings checkConvergenceStep = copy(recursionStep, mappings);
Symbol countSymbol = symbolAllocator.newSymbol("count", BIGINT);
ResolvedFunction function = plannerContext.getMetadata().resolveFunction(session, QualifiedName.of("count"), ImmutableList.of());
WindowNode.Function countFunction = new WindowNode.Function(function, ImmutableList.of(), DEFAULT_FRAME, false);
WindowNode windowNode = new WindowNode(idAllocator.getNextId(), checkConvergenceStep.getNode(), new WindowNode.Specification(ImmutableList.of(), Optional.empty()), ImmutableMap.of(countSymbol, countFunction), Optional.empty(), ImmutableSet.of(), 0);
// 2. append filter to fail on non-empty result
ResolvedFunction fail = plannerContext.getMetadata().resolveFunction(session, QualifiedName.of("fail"), fromTypes(VARCHAR));
String recursionLimitExceededMessage = format("Recursion depth limit exceeded (%s). Use 'max_recursion_depth' session property to modify the limit.", maxRecursionDepth);
Expression predicate = new IfExpression(new ComparisonExpression(GREATER_THAN_OR_EQUAL, countSymbol.toSymbolReference(), new GenericLiteral("BIGINT", "0")), new Cast(new FunctionCall(fail.toQualifiedName(), ImmutableList.of(new Cast(new StringLiteral(recursionLimitExceededMessage), toSqlType(VARCHAR)))), toSqlType(BOOLEAN)), TRUE_LITERAL);
FilterNode filterNode = new FilterNode(idAllocator.getNextId(), windowNode, predicate);
recursionSteps.add(new NodeAndMappings(filterNode, checkConvergenceStep.getFields()));
// union all the recursion steps
List<NodeAndMappings> recursionStepsToUnion = recursionSteps.build();
List<Symbol> unionOutputSymbols = anchorPlan.getFieldMappings().stream().map(symbol -> symbolAllocator.newSymbol(symbol, "_expanded")).collect(toImmutableList());
ImmutableListMultimap.Builder<Symbol, Symbol> unionSymbolMapping = ImmutableListMultimap.builder();
for (NodeAndMappings plan : recursionStepsToUnion) {
for (int i = 0; i < unionOutputSymbols.size(); i++) {
unionSymbolMapping.put(unionOutputSymbols.get(i), plan.getFields().get(i));
}
}
List<PlanNode> nodesToUnion = recursionStepsToUnion.stream().map(NodeAndMappings::getNode).collect(toImmutableList());
PlanNode result = new UnionNode(idAllocator.getNextId(), nodesToUnion, unionSymbolMapping.build(), unionOutputSymbols);
if (union.isDistinct()) {
result = new AggregationNode(idAllocator.getNextId(), result, ImmutableMap.of(), singleGroupingSet(result.getOutputSymbols()), ImmutableList.of(), AggregationNode.Step.SINGLE, Optional.empty(), Optional.empty());
}
return new RelationPlan(result, anchorPlan.getScope(), unionOutputSymbols, outerContext);
}
use of io.trino.sql.tree.WindowFrame.Type.ROWS in project trino by trinodb.
the class TestPrunePattenRecognitionColumns method testRemovePatternRecognitionNode.
@Test
public void testRemovePatternRecognitionNode() {
ResolvedFunction rank = createTestMetadataManager().resolveFunction(tester().getSession(), QualifiedName.of("rank"), ImmutableList.of());
// MATCH_RECOGNIZE with options: AFTER MATCH SKIP PAST LAST ROW, ALL ROWS WITH UNMATCHED ROW
tester().assertThat(new PrunePattenRecognitionColumns()).on(p -> p.project(Assignments.identity(p.symbol("b")), p.patternRecognition(builder -> builder.rowsPerMatch(ALL_WITH_UNMATCHED).skipTo(PAST_LAST).pattern(new IrLabel("X")).addVariableDefinition(new IrLabel("X"), "true").source(p.values(p.symbol("a"), p.symbol("b")))))).matches(strictProject(ImmutableMap.of("b", expression("b")), values("a", "b")));
// pattern recognition in window
tester().assertThat(new PrunePattenRecognitionColumns()).on(p -> p.project(Assignments.identity(p.symbol("b")), p.patternRecognition(builder -> builder.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("b", expression("b")), values("a", "b")));
// unreferenced window functions and measures
tester().assertThat(new PrunePattenRecognitionColumns()).on(p -> p.project(Assignments.identity(p.symbol("b")), p.patternRecognition(builder -> builder.addWindowFunction(p.symbol("rank"), new WindowNode.Function(rank, ImmutableList.of(), 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("b", expression("b")), values("a", "b")));
}
Aggregations