Examples with PlanMatchPattern io.trino.sql.planner.assertions.PlanMatchPattern used on opensource projects

Example 1 with PlanMatchPattern

use of io.trino.sql.planner.assertions.PlanMatchPattern in project trino by trinodb.

the class TestLogicalPlanner method testBroadcastCorrelatedSubqueryAvoidsRemoteExchangeBeforeAggregation.

@Test
public void testBroadcastCorrelatedSubqueryAvoidsRemoteExchangeBeforeAggregation() {
    Session broadcastJoin = Session.builder(this.getQueryRunner().getDefaultSession()).setSystemProperty(JOIN_DISTRIBUTION_TYPE, JoinDistributionType.BROADCAST.name()).setSystemProperty(FORCE_SINGLE_NODE_OUTPUT, Boolean.toString(false)).build();
    // make sure there is a remote exchange on the build side
    PlanMatchPattern joinBuildSideWithRemoteExchange = anyTree(node(JoinNode.class, anyTree(node(TableScanNode.class)), anyTree(exchange(REMOTE, ExchangeNode.Type.REPLICATE, anyTree(node(TableScanNode.class))))));
    // validates that there exists only one remote exchange
    Consumer<Plan> validateSingleRemoteExchange = plan -> assertEquals(countOfMatchingNodes(plan, node -> node instanceof ExchangeNode && ((ExchangeNode) node).getScope() == REMOTE), 1);
    Consumer<Plan> validateSingleStreamingAggregation = plan -> assertEquals(countOfMatchingNodes(plan, node -> node instanceof AggregationNode && ((AggregationNode) node).getGroupingKeys().contains(new Symbol("unique")) && ((AggregationNode) node).isStreamable()), 1);
    // region is unpartitioned, AssignUniqueId should provide satisfying partitioning for count(*) after LEFT JOIN
    assertPlanWithSession("SELECT (SELECT count(*) FROM region r2 WHERE r2.regionkey > r1.regionkey) FROM region r1", broadcastJoin, false, joinBuildSideWithRemoteExchange, validateSingleRemoteExchange.andThen(validateSingleStreamingAggregation));
    // orders is naturally partitioned, AssignUniqueId should not overwrite its natural partitioning
    assertPlanWithSession("SELECT count(count) " + "FROM (SELECT o1.orderkey orderkey, (SELECT count(*) FROM orders o2 WHERE o2.orderkey > o1.orderkey) count FROM orders o1) " + "GROUP BY orderkey", broadcastJoin, false, joinBuildSideWithRemoteExchange, validateSingleRemoteExchange.andThen(validateSingleStreamingAggregation));
}

Example 2 with PlanMatchPattern

use of io.trino.sql.planner.assertions.PlanMatchPattern in project trino by trinodb.

the class TestRecursiveCte method testRecursiveQuery.

@Test
public void testRecursiveQuery() {
    @Language("SQL") String sql = "WITH RECURSIVE t(n) AS (" + "                SELECT 1" + "                UNION ALL" + "                SELECT n + 2 FROM t WHERE n < 6" + "                )" + "                SELECT * from t";
    PlanMatchPattern pattern = anyTree(union(// base term
    project(project(project(ImmutableMap.of("expr", expression("1")), values()))), // first recursion step
    project(project(project(ImmutableMap.of("expr_0", expression("expr + 2")), filter("expr < 6", project(project(project(ImmutableMap.of("expr", expression("1")), values()))))))), // "post-recursion" step with convergence assertion
    filter("IF((count >= BIGINT '0'), " + "CAST(fail(CAST('Recursion depth limit exceeded (1). Use ''max_recursion_depth'' session property to modify the limit.' AS varchar)) AS boolean), " + "true)", window(windowBuilder -> windowBuilder.addFunction("count", functionCall("count", ImmutableList.of())), project(project(project(ImmutableMap.of("expr_1", expression("expr + 2")), filter("expr < 6", project(ImmutableMap.of("expr", expression("expr_0")), project(project(project(ImmutableMap.of("expr_0", expression("expr + 2")), filter("expr < 6", project(project(project(ImmutableMap.of("expr", expression("1")), values()))))))))))))))));
    assertPlan(sql, CREATED, pattern);
}

Example 3 with PlanMatchPattern

use of io.trino.sql.planner.assertions.PlanMatchPattern in project trino by trinodb.

the class TestWindowClause method testPreprojectExpression.

@Test
public void testPreprojectExpression() {
    @Language("SQL") String sql = "SELECT max(b) OVER w FROM (VALUES (1, 1)) t(a, b) WINDOW w AS (PARTITION BY a + 1)";
    PlanMatchPattern pattern = anyTree(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of("expr"), ImmutableList.of(), ImmutableMap.of())).addFunction("max_result", functionCall("max", ImmutableList.of("b"))), anyTree(project(ImmutableMap.of("expr", expression("a + 1")), anyTree(values("a", "b"))))));
    assertPlan(sql, CREATED, pattern);
}

Example 4 with PlanMatchPattern

use of io.trino.sql.planner.assertions.PlanMatchPattern 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 5 with PlanMatchPattern

use of io.trino.sql.planner.assertions.PlanMatchPattern in project trino by trinodb.

the class TestWindowClause method testWindowFunctionsInSelectAndOrderBy.

@Test
public void testWindowFunctionsInSelectAndOrderBy() {
    @Language("SQL") String sql = "SELECT array_agg(a) OVER (w ORDER BY a + 1), -a a FROM (VALUES 1, 2, 3) t(a) WINDOW w AS () ORDER BY max(a) OVER (w ORDER BY a + 1)";
    PlanMatchPattern pattern = anyTree(sort(ImmutableList.of(sort("max_result", ASCENDING, LAST)), any(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of(), ImmutableList.of("order_by_window_sortkey"), ImmutableMap.of("order_by_window_sortkey", SortOrder.ASC_NULLS_LAST))).addFunction("max_result", functionCall("max", ImmutableList.of("minus_a"))), any(project(ImmutableMap.of("order_by_window_sortkey", expression("minus_a + 1")), project(ImmutableMap.of("minus_a", expression("-a")), window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of(), ImmutableList.of("select_window_sortkey"), ImmutableMap.of("select_window_sortkey", SortOrder.ASC_NULLS_LAST))).addFunction("array_agg_result", functionCall("array_agg", ImmutableList.of("a"))), anyTree(project(ImmutableMap.of("select_window_sortkey", expression("a + 1")), anyTree(values("a"))))))))))));
    assertPlan(sql, CREATED, pattern);
}