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

Example 6 with PlanMatchPattern

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

the class TestWindowFrameRange method testFramePrecedingFollowingNoCoercions.

@Test
public void testFramePrecedingFollowingNoCoercions() {
    @Language("SQL") String sql = "SELECT array_agg(key) OVER(ORDER BY key RANGE BETWEEN x PRECEDING AND y FOLLOWING) " + "FROM (VALUES (1, 1, 1), (2, 2, 2)) t(key, x, y)";
    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"), FOLLOWING, Optional.of("frame_end_value"), Optional.of("key"))), project(ImmutableMap.of("frame_end_value", expression(new FunctionCall(QualifiedName.of("$operator$add"), ImmutableList.of(new SymbolReference("key"), new SymbolReference("y"))))), filter("IF((y >= CAST(0 AS INTEGER)), " + "true, " + "CAST(fail(CAST('Window frame offset value must not be negative or null' AS varchar)) AS boolean))", project(ImmutableMap.of("frame_start_value", expression(new FunctionCall(QualifiedName.of("$operator$subtract"), ImmutableList.of(new SymbolReference("key"), new SymbolReference("x"))))), filter("IF((x >= CAST(0 AS INTEGER)), " + "true, " + "CAST(fail(CAST('Window frame offset value must not be negative or null' AS varchar)) AS boolean))", anyTree(values(ImmutableList.of("key", "x", "y"), ImmutableList.of(ImmutableList.of(new LongLiteral("1"), new LongLiteral("1"), new LongLiteral("1")), ImmutableList.of(new LongLiteral("2"), new LongLiteral("2"), new LongLiteral("2")))))))))));
    assertPlan(sql, CREATED, pattern);
}

Example 7 with PlanMatchPattern

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

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

the class BaseJdbcConnectorTest method testCaseSensitiveAggregationPushdown.

@Test
public void testCaseSensitiveAggregationPushdown() {
    if (!hasBehavior(SUPPORTS_AGGREGATION_PUSHDOWN)) {
        // Covered by testAggregationPushdown
        return;
    }
    boolean supportsPushdownWithVarcharInequality = hasBehavior(SUPPORTS_PREDICATE_PUSHDOWN_WITH_VARCHAR_INEQUALITY);
    boolean supportsCountDistinctPushdown = hasBehavior(SUPPORTS_AGGREGATION_PUSHDOWN_COUNT_DISTINCT);
    PlanMatchPattern aggregationOverTableScan = node(AggregationNode.class, node(TableScanNode.class));
    PlanMatchPattern groupingAggregationOverTableScan = node(AggregationNode.class, node(ProjectNode.class, node(TableScanNode.class)));
    try (TestTable table = new TestTable(getQueryRunner()::execute, "test_case_sensitive_aggregation_pushdown", "(a_string varchar(1), a_char char(1), a_bigint bigint)", ImmutableList.of("'A', 'A', 1", "'B', 'B', 2", "'a', 'a', 3", "'b', 'b', 4"))) {
        // case-sensitive functions prevent pushdown
        assertConditionallyPushedDown(getSession(), "SELECT max(a_string), min(a_string), max(a_char), min(a_char) FROM " + table.getName(), supportsPushdownWithVarcharInequality, aggregationOverTableScan).skippingTypesCheck().matches("VALUES ('b', 'A', 'b', 'A')");
        // distinct over case-sensitive column prevents pushdown
        assertConditionallyPushedDown(getSession(), "SELECT distinct a_string FROM " + table.getName(), supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES 'A', 'B', 'a', 'b'");
        assertConditionallyPushedDown(getSession(), "SELECT distinct a_char FROM " + table.getName(), supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES 'A', 'B', 'a', 'b'");
        // case-sensitive grouping sets prevent pushdown
        assertConditionallyPushedDown(getSession(), "SELECT a_string, count(*) FROM " + table.getName() + " GROUP BY a_string", supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES ('A', BIGINT '1'), ('a', BIGINT '1'), ('b', BIGINT '1'), ('B', BIGINT '1')");
        assertConditionallyPushedDown(getSession(), "SELECT a_char, count(*) FROM " + table.getName() + " GROUP BY a_char", supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES ('A', BIGINT '1'), ('B', BIGINT '1'), ('a', BIGINT '1'), ('b', BIGINT '1')");
        // case-insensitive functions can still be pushed down as long as grouping sets are not case-sensitive
        assertThat(query("SELECT count(a_string), count(a_char) FROM " + table.getName())).isFullyPushedDown();
        assertThat(query("SELECT count(a_string), count(a_char) FROM " + table.getName() + " GROUP BY a_bigint")).isFullyPushedDown();
        // DISTINCT over case-sensitive columns prevents pushdown
        assertConditionallyPushedDown(getSession(), "SELECT count(DISTINCT a_string) FROM " + table.getName(), supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES BIGINT '4'");
        assertConditionallyPushedDown(getSession(), "SELECT count(DISTINCT a_char) FROM " + table.getName(), supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES BIGINT '4'");
        assertConditionallyPushedDown(getSession(), "SELECT count(DISTINCT a_string), count(DISTINCT a_bigint) FROM " + table.getName(), supportsPushdownWithVarcharInequality && supportsCountDistinctPushdown, node(ExchangeNode.class, node(AggregationNode.class, anyTree(node(TableScanNode.class))))).skippingTypesCheck().matches("VALUES (BIGINT '4', BIGINT '4')");
        assertConditionallyPushedDown(getSession(), "SELECT count(DISTINCT a_char), count(DISTINCT a_bigint) FROM " + table.getName(), supportsPushdownWithVarcharInequality && supportsCountDistinctPushdown, node(ExchangeNode.class, node(AggregationNode.class, anyTree(node(TableScanNode.class))))).skippingTypesCheck().matches("VALUES (BIGINT '4', BIGINT '4')");
    }
}

Example 9 with PlanMatchPattern

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

the class TestMergeWindows method testNotMergeAcrossJoinBranches.

@Test
public void testNotMergeAcrossJoinBranches() {
    String rOrderkeyAlias = "R_ORDERKEY";
    String rShipdateAlias = "R_SHIPDATE";
    String rQuantityAlias = "R_QUANTITY";
    @Language("SQL") String sql = "WITH foo AS (" + "SELECT " + "suppkey, orderkey, partkey, " + "SUM(discount) OVER (PARTITION BY orderkey ORDER BY shipdate, quantity DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) a " + "FROM lineitem WHERE (partkey = 272 OR partkey = 273) AND suppkey > 50 " + "), " + "bar AS ( " + "SELECT " + "suppkey, orderkey, partkey, " + "AVG(quantity) OVER (PARTITION BY orderkey ORDER BY shipdate, quantity DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) b " + "FROM lineitem WHERE (partkey = 272 OR partkey = 273) AND suppkey > 50 " + ")" + "SELECT * FROM foo, bar WHERE foo.a = bar.b";
    ExpectedValueProvider<WindowNode.Specification> leftSpecification = specification(ImmutableList.of(ORDERKEY_ALIAS), ImmutableList.of(SHIPDATE_ALIAS, QUANTITY_ALIAS), ImmutableMap.of(SHIPDATE_ALIAS, SortOrder.ASC_NULLS_LAST, QUANTITY_ALIAS, SortOrder.DESC_NULLS_LAST));
    ExpectedValueProvider<WindowNode.Specification> rightSpecification = specification(ImmutableList.of(rOrderkeyAlias), ImmutableList.of(rShipdateAlias, rQuantityAlias), ImmutableMap.of(rShipdateAlias, SortOrder.ASC_NULLS_LAST, rQuantityAlias, SortOrder.DESC_NULLS_LAST));
    // Too many items in the map to call ImmutableMap.of() :-(
    ImmutableMap.Builder<String, String> leftTableScanBuilder = ImmutableMap.builder();
    leftTableScanBuilder.put(DISCOUNT_ALIAS, "discount");
    leftTableScanBuilder.put(ORDERKEY_ALIAS, "orderkey");
    leftTableScanBuilder.put("PARTKEY", "partkey");
    leftTableScanBuilder.put(SUPPKEY_ALIAS, "suppkey");
    leftTableScanBuilder.put(QUANTITY_ALIAS, "quantity");
    leftTableScanBuilder.put(SHIPDATE_ALIAS, "shipdate");
    PlanMatchPattern leftTableScan = tableScan("lineitem", leftTableScanBuilder.buildOrThrow());
    PlanMatchPattern rightTableScan = tableScan("lineitem", ImmutableMap.of(rOrderkeyAlias, "orderkey", "R_PARTKEY", "partkey", "R_SUPPKEY", "suppkey", rQuantityAlias, "quantity", rShipdateAlias, "shipdate"));
    assertUnitPlan(sql, anyTree(filter("SUM = AVG", join(JoinNode.Type.INNER, ImmutableList.of(), any(window(windowMatcherBuilder -> windowMatcherBuilder.specification(leftSpecification).addFunction("SUM", functionCall("sum", COMMON_FRAME, ImmutableList.of(DISCOUNT_ALIAS))), any(leftTableScan))), any(window(windowMatcherBuilder -> windowMatcherBuilder.specification(rightSpecification).addFunction("AVG", functionCall("avg", COMMON_FRAME, ImmutableList.of(rQuantityAlias))), any(rightTableScan)))))));
}

Example 10 with PlanMatchPattern

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

the class TestOptimizeMixedDistinctAggregations method testMixedDistinctAggregationOptimizer.

@Test
public void testMixedDistinctAggregationOptimizer() {
    @Language("SQL") String sql = "SELECT custkey, max(totalprice) AS s, count(DISTINCT orderdate) AS d FROM orders GROUP BY custkey";
    String group = "GROUP";
    // Original keys
    String groupBy = "CUSTKEY";
    String aggregate = "TOTALPRICE";
    String distinctAggregation = "ORDERDATE";
    // Second Aggregation data
    List<String> groupByKeysSecond = ImmutableList.of(groupBy);
    Map<Optional<String>, ExpectedValueProvider<FunctionCall>> aggregationsSecond = ImmutableMap.of(Optional.of("arbitrary"), PlanMatchPattern.functionCall("arbitrary", false, ImmutableList.of(anySymbol())), Optional.of("count"), PlanMatchPattern.functionCall("count", false, ImmutableList.of(anySymbol())));
    // First Aggregation data
    List<String> groupByKeysFirst = ImmutableList.of(groupBy, distinctAggregation, group);
    Map<Optional<String>, ExpectedValueProvider<FunctionCall>> aggregationsFirst = ImmutableMap.of(Optional.of("MAX"), functionCall("max", ImmutableList.of("TOTALPRICE")));
    PlanMatchPattern tableScan = tableScan("orders", ImmutableMap.of("TOTALPRICE", "totalprice", "CUSTKEY", "custkey", "ORDERDATE", "orderdate"));
    // GroupingSet symbols
    ImmutableList.Builder<List<String>> groups = ImmutableList.builder();
    groups.add(ImmutableList.of(groupBy, aggregate));
    groups.add(ImmutableList.of(groupBy, distinctAggregation));
    PlanMatchPattern expectedPlanPattern = anyTree(aggregation(singleGroupingSet(groupByKeysSecond), aggregationsSecond, Optional.empty(), SINGLE, project(aggregation(singleGroupingSet(groupByKeysFirst), aggregationsFirst, Optional.empty(), SINGLE, groupId(groups.build(), group, tableScan)))));
    assertUnitPlan(sql, expectedPlanPattern);
}