Examples with PlanMatchPattern com.facebook.presto.sql.planner.assertions.PlanMatchPattern used on opensource projects

Example 1 with PlanMatchPattern

use of com.facebook.presto.sql.planner.assertions.PlanMatchPattern in project presto by prestodb.

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.build());
    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(leftSpecification, ImmutableMap.of("SUM", functionCall("sum", COMMON_FRAME, ImmutableList.of(DISCOUNT_ALIAS))), any(leftTableScan))), any(window(rightSpecification, ImmutableMap.of("AVG", functionCall("avg", COMMON_FRAME, ImmutableList.of(rQuantityAlias))), any(rightTableScan)))))));
}

Example 2 with PlanMatchPattern

use of com.facebook.presto.sql.planner.assertions.PlanMatchPattern in project presto by prestodb.

the class TestReorderWindows method testNonMergeableABAReordersToAABAllOptimizers.

@Test
public void testNonMergeableABAReordersToAABAllOptimizers() {
    @Language("SQL") String sql = "select " + "sum(quantity) over(PARTITION BY suppkey ORDER BY orderkey ASC NULLS LAST) sum_quantity_A, " + "avg(discount) over(PARTITION BY partkey ORDER BY receiptdate ASC NULLS LAST) avg_discount_B, " + "min(tax) over(PARTITION BY suppkey ORDER BY shipdate ASC NULLS LAST) min_tax_A " + "from lineitem";
    PlanMatchPattern pattern = anyTree(window(windowAp, ImmutableList.of(functionCall("min", commonFrame, ImmutableList.of(TAX_ALIAS))), window(windowA, ImmutableList.of(functionCall("sum", commonFrame, ImmutableList.of(QUANTITY_ALIAS))), anyTree(window(windowB, ImmutableList.of(functionCall("avg", commonFrame, ImmutableList.of(DISCOUNT_ALIAS))), anyTree(LINEITEM_TABLESCAN_DOQPRSST))))));
    assertPlan(sql, pattern);
}

Example 3 with PlanMatchPattern

use of com.facebook.presto.sql.planner.assertions.PlanMatchPattern in project presto by prestodb.

the class TestMergeWindows method testMergeableWindowsAllOptimizers.

/**
     * There are two types of tests in here, and they answer two different
     * questions about MergeWindows (MW):
     *
     * 1) Is MW working as it's supposed to be? The tests running the minimal
     * set of optimizers can tell us this.
     * 2) Has some other optimizer changed the plan in such a way that MW no
     * longer merges windows with identical specifications because the plan
     * that MW sees cannot be optimized by MW? The test running the full set
     * of optimizers answers this, though it isn't actually meaningful unless
     * we know the answer to question 1 is "yes".
     *
     * The tests that use only the minimal set of optimizers are closer to true
     * "unit" tests in that they verify the behavior of MW with as few
     * external dependencies as possible. Those dependencies to include the
     * parser and analyzer, so the phrase "unit" tests should be taken with a
     * grain of salt. Using the parser and anayzler instead of creating plan
     * nodes by hand does have a couple of advantages over a true unit test:
     * 1) The tests are more self-maintaining.
     * 2) They're a lot easier to read.
     * 3) It's a lot less typing.
     *
     * The test that runs with all of the optimzers acts as an integration test
     * and ensures that MW is effective when run with the complete set of
     * optimizers.
     */
@Test
public void testMergeableWindowsAllOptimizers() {
    @Language("SQL") String sql = "SELECT " + "SUM(quantity) OVER (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_quantity_A, " + "SUM(quantity) OVER (PARTITION BY orderkey ORDER BY shipdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_quantity_B, " + "SUM(discount) OVER (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_discount_A " + "FROM lineitem";
    PlanMatchPattern pattern = anyTree(window(specificationA, ImmutableList.of(functionCall("sum", COMMON_FRAME, ImmutableList.of(QUANTITY_ALIAS)), functionCall("sum", COMMON_FRAME, ImmutableList.of(DISCOUNT_ALIAS))), anyTree(window(specificationB, ImmutableList.of(functionCall("sum", COMMON_FRAME, ImmutableList.of(QUANTITY_ALIAS))), anyNot(WindowNode.class, // should be anyTree(LINEITEM_TABLESCAN_DOQSS) but anyTree does not handle zero nodes case correctly
    LINEITEM_TABLESCAN_DOQSS)))));
    assertPlan(sql, pattern);
}

Example 4 with PlanMatchPattern

use of com.facebook.presto.sql.planner.assertions.PlanMatchPattern in project presto by prestodb.

the class TestMixedDistinctAggregationOptimizer 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(ImmutableList.of(groupByKeysSecond), aggregationsSecond, ImmutableMap.of(), Optional.empty(), project(aggregation(ImmutableList.of(groupByKeysFirst), aggregationsFirst, ImmutableMap.of(), Optional.empty(), groupingSet(groups.build(), group, anyTree(tableScan))))));
    assertUnitPlan(sql, expectedPlanPattern);
}