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

Example 1 with PlanMatchPattern

use of io.prestosql.sql.planner.assertions.PlanMatchPattern in project hetu-core by openlookeng.

the class TestEliminateSorts method testNotEliminateSorts.

@Test
public void testNotEliminateSorts() {
    @Language("SQL") String sql = "SELECT quantity, row_number() OVER (ORDER BY quantity) FROM lineitem ORDER BY tax";
    PlanMatchPattern pattern = anyTree(sort(anyTree(window(windowMatcherBuilder -> windowMatcherBuilder.specification(windowSpec).addFunction(functionCall("row_number", Optional.empty(), ImmutableList.of())), anyTree(LINEITEM_TABLESCAN_Q)))));
    assertUnitPlan(sql, pattern);
}

Example 2 with PlanMatchPattern

use of io.prestosql.sql.planner.assertions.PlanMatchPattern in project hetu-core by openlookeng.

the class TestMergeWindows method testMergeableWindowsAllOptimizers.

/**
 * There are two types of tests in here, and they answer two different
 * questions about MergeWindows (MW):
 * <p>
 * 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".
 * <p>
 * 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.
 * <p>
 * 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(windowMatcherBuilder -> windowMatcherBuilder.specification(specificationA).addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(QUANTITY_ALIAS))).addFunction(functionCall("sum", COMMON_FRAME, ImmutableList.of(DISCOUNT_ALIAS))), anyTree(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specificationB).addFunction(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 3 with PlanMatchPattern

use of io.prestosql.sql.planner.assertions.PlanMatchPattern in project hetu-core by openlookeng.

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, 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 4 with PlanMatchPattern

use of io.prestosql.sql.planner.assertions.PlanMatchPattern in project hetu-core by openlookeng.

the class TestPredicatePushdown 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 5 with PlanMatchPattern

use of io.prestosql.sql.planner.assertions.PlanMatchPattern in project hetu-core by openlookeng.

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(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)))))));
}