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

Example 21 with PlanMatchPattern

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

the class TestWindowClause method testWindowWithFrameCoercions.

@Test
public void testWindowWithFrameCoercions() {
    @Language("SQL") String sql = "SELECT a old_a, 2e0 a FROM (VALUES -100, -99, -98) t(a) WINDOW w AS (ORDER BY a + 1) ORDER BY count(*) OVER (w RANGE BETWEEN CURRENT ROW AND a + 1e0 FOLLOWING)";
    PlanMatchPattern pattern = anyTree(sort(ImmutableList.of(sort("count_result", ASCENDING, LAST)), project(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of(), ImmutableList.of("sortkey"), ImmutableMap.of("sortkey", SortOrder.ASC_NULLS_LAST))).addFunction("count_result", functionCall("count", ImmutableList.of()), createTestMetadataManager().resolveFunction(TEST_SESSION, QualifiedName.of("count"), fromTypes()), windowFrame(RANGE, CURRENT_ROW, Optional.empty(), Optional.empty(), FOLLOWING, Optional.of("frame_bound"), Optional.of("coerced_sortkey"))), project(ImmutableMap.of("frame_bound", expression(new FunctionCall(QualifiedName.of("$operator$add"), ImmutableList.of(new SymbolReference("coerced_sortkey"), new SymbolReference("frame_offset"))))), project(ImmutableMap.of("coerced_sortkey", expression("CAST(sortkey AS double)")), node(FilterNode.class, project(project(ImmutableMap.of(// sort key based on "a" in source scope
    "sortkey", expression("a + 1"), // frame offset based on "a" in output scope
    "frame_offset", expression("new_a + 1E0")), project(ImmutableMap.of("new_a", expression("2E0")), project(project(values("a")))))))))))));
    assertPlan(sql, CREATED, pattern);
}

Example 22 with PlanMatchPattern

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

the class TestWindowFrameRange method testFrameFollowingWithOffsetCoercion.

@Test
public void testFrameFollowingWithOffsetCoercion() {
    @Language("SQL") String sql = "SELECT array_agg(key) OVER(ORDER BY key RANGE BETWEEN CURRENT ROW AND x FOLLOWING) " + "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(createDecimalType(2, 1))), windowFrame(RANGE, CURRENT_ROW, Optional.empty(), Optional.empty(), FOLLOWING, Optional.of("frame_end_value"), Optional.of("key_for_frame_end_comparison"))), project(ImmutableMap.of("key_for_frame_end_comparison", expression("CAST(key AS decimal(12, 1))")), project(ImmutableMap.of("frame_end_value", expression(new FunctionCall(QualifiedName.of("$operator$add"), ImmutableList.of(new SymbolReference("key"), new SymbolReference("offset"))))), filter("IF((offset >= CAST(0 AS DECIMAL(10, 0))), " + "true, " + "CAST(fail(CAST('Window frame offset value must not be negative or null' AS varchar)) AS boolean))", project(ImmutableMap.of("offset", expression("CAST(x AS decimal(10, 0))")), anyTree(values(ImmutableList.of("key", "x"), ImmutableList.of(ImmutableList.of(new DecimalLiteral("1.1"), new LongLiteral("1")), ImmutableList.of(new DecimalLiteral("2.2"), new LongLiteral("2")))))))))));
    assertPlan(sql, CREATED, pattern);
}

Example 23 with PlanMatchPattern

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

the class AbstractPredicatePushdownTest method testPredicateOnPartitionSymbolsPushedThroughWindow.

@Test
public void testPredicateOnPartitionSymbolsPushedThroughWindow() {
    PlanMatchPattern tableScan = tableScan("orders", ImmutableMap.of("CUST_KEY", "custkey", "ORDER_KEY", "orderkey"));
    assertPlan("SELECT * FROM (" + "SELECT custkey, orderkey, rank() OVER (PARTITION BY custkey  ORDER BY orderdate ASC)" + "FROM orders" + ") WHERE custkey = 0 AND orderkey > 0", anyTree(filter("ORDER_KEY > BIGINT '0'", anyTree(node(WindowNode.class, anyTree(filter("CUST_KEY = BIGINT '0'", tableScan)))))));
}

Example 24 with PlanMatchPattern

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

the class BaseJdbcConnectorTest method testLimitPushdown.

@Test
public void testLimitPushdown() {
    if (!hasBehavior(SUPPORTS_LIMIT_PUSHDOWN)) {
        // Use high limit for result determinism
        assertThat(query("SELECT name FROM nation LIMIT 30")).isNotFullyPushedDown(LimitNode.class);
        return;
    }
    // Use high limit for result determinism
    assertThat(query("SELECT name FROM nation LIMIT 30")).isFullyPushedDown();
    assertThat(query("SELECT name FROM nation LIMIT 3")).skipResultsCorrectnessCheckForPushdown().isFullyPushedDown();
    // with filter over numeric column
    assertThat(query("SELECT name FROM nation WHERE regionkey = 3 LIMIT 5")).isFullyPushedDown();
    // with filter over varchar column
    PlanMatchPattern filterOverTableScan = node(FilterNode.class, node(TableScanNode.class));
    assertConditionallyPushedDown(getSession(), "SELECT name FROM nation WHERE name < 'EEE' LIMIT 5", hasBehavior(SUPPORTS_PREDICATE_PUSHDOWN_WITH_VARCHAR_INEQUALITY), filterOverTableScan);
    // with aggregation
    PlanMatchPattern aggregationOverTableScan = node(AggregationNode.class, anyTree(node(TableScanNode.class)));
    assertConditionallyPushedDown(getSession(), // global aggregation, LIMIT removed
    "SELECT max(regionkey) FROM nation LIMIT 5", hasBehavior(SUPPORTS_AGGREGATION_PUSHDOWN), aggregationOverTableScan);
    assertConditionallyPushedDown(getSession(), "SELECT regionkey, max(nationkey) FROM nation GROUP BY regionkey LIMIT 5", hasBehavior(SUPPORTS_AGGREGATION_PUSHDOWN), aggregationOverTableScan);
    // distinct limit can be pushed down even without aggregation pushdown
    assertThat(query("SELECT DISTINCT regionkey FROM nation LIMIT 5")).isFullyPushedDown();
    // with aggregation and filter over numeric column
    assertConditionallyPushedDown(getSession(), "SELECT regionkey, count(*) FROM nation WHERE nationkey < 5 GROUP BY regionkey LIMIT 3", hasBehavior(SUPPORTS_AGGREGATION_PUSHDOWN), aggregationOverTableScan);
    // with aggregation and filter over varchar column
    if (hasBehavior(SUPPORTS_PREDICATE_PUSHDOWN_WITH_VARCHAR_INEQUALITY)) {
        assertConditionallyPushedDown(getSession(), "SELECT regionkey, count(*) FROM nation WHERE name < 'EGYPT' GROUP BY regionkey LIMIT 3", hasBehavior(SUPPORTS_AGGREGATION_PUSHDOWN), aggregationOverTableScan);
    }
    // with TopN over numeric column
    PlanMatchPattern topnOverTableScan = node(TopNNode.class, anyTree(node(TableScanNode.class)));
    assertConditionallyPushedDown(getSession(), "SELECT * FROM (SELECT regionkey FROM nation ORDER BY nationkey ASC LIMIT 10) LIMIT 5", hasBehavior(SUPPORTS_TOPN_PUSHDOWN), topnOverTableScan);
    // with TopN over varchar column
    assertConditionallyPushedDown(getSession(), "SELECT * FROM (SELECT regionkey FROM nation ORDER BY name ASC LIMIT 10) LIMIT 5", hasBehavior(SUPPORTS_TOPN_PUSHDOWN_WITH_VARCHAR), topnOverTableScan);
    // with join
    PlanMatchPattern joinOverTableScans = node(JoinNode.class, anyTree(node(TableScanNode.class)), anyTree(node(TableScanNode.class)));
    assertConditionallyPushedDown(joinPushdownEnabled(getSession()), "SELECT n.name, r.name " + "FROM nation n " + "LEFT JOIN region r USING (regionkey) " + "LIMIT 30", hasBehavior(SUPPORTS_JOIN_PUSHDOWN), joinOverTableScans);
}

Example 25 with PlanMatchPattern

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

the class BaseJdbcConnectorTest method testJoinPushdownDisabled.

@Test
public void testJoinPushdownDisabled() {
    Session noJoinPushdown = Session.builder(getSession()).setCatalogSessionProperty(getSession().getCatalog().orElseThrow(), JOIN_PUSHDOWN_ENABLED, "false").setSystemProperty("enable_dynamic_filtering", "false").setSystemProperty("optimize_hash_generation", "false").build();
    PlanMatchPattern partitionedJoinOverTableScans = node(JoinNode.class, exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.REPARTITION, node(TableScanNode.class)), exchange(ExchangeNode.Scope.LOCAL, exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.REPARTITION, node(TableScanNode.class))));
    assertThat(query(noJoinPushdown, "SELECT r.name, n.name FROM nation n JOIN region r ON n.regionkey = r.regionkey")).isNotFullyPushedDown(partitionedJoinOverTableScans);
}