public static PlanMatchPattern exchange(ExchangeNode.Scope scope, ExchangeNode.Type type, PlanMatchPattern... sources) { return exchange(scope, type, ImmutableList.of(), sources); }
@Test public void testDistributedSort() { ImmutableList<PlanMatchPattern.Ordering> orderBy = ImmutableList.of(sort("ORDERKEY", DESCENDING, LAST)); assertDistributedPlan( "SELECT orderkey FROM orders ORDER BY orderkey DESC", output( exchange(REMOTE, GATHER, orderBy, exchange(LOCAL, GATHER, orderBy, sort(orderBy, exchange(REMOTE, REPARTITION, tableScan("orders", ImmutableMap.of( "ORDERKEY", "orderkey")))))))); assertDistributedPlan( "SELECT orderkey FROM orders ORDER BY orderkey DESC", Session.builder(this.getQueryRunner().getDefaultSession()) .setSystemProperty(DISTRIBUTED_SORT, Boolean.toString(false)) .build(), output( sort(orderBy, exchange(LOCAL, GATHER, exchange(REMOTE, GATHER, tableScan("orders", ImmutableMap.of( "ORDERKEY", "orderkey"))))))); } }
@Test public void testWindowAfterAggregation() { // Window partition key is a super set of group by key. assertDistributedPlan("SELECT rank() OVER (PARTITION BY custkey) FROM orders GROUP BY custkey", anyTree( window(windowMatcherBuilder -> windowMatcherBuilder .specification(specification(ImmutableList.of("custkey"), ImmutableList.of(), ImmutableMap.of())) .addFunction(functionCall("rank", Optional.empty(), ImmutableList.of())), project(aggregation(singleGroupingSet("custkey"), ImmutableMap.of(), ImmutableMap.of(), Optional.empty(), FINAL, exchange(LOCAL, GATHER, project(exchange(REMOTE, REPARTITION, anyTree(tableScan("orders", ImmutableMap.of("custkey", "custkey"))))))))))); // Window partition key is not a super set of group by key. assertDistributedPlan("SELECT rank() OVER (partition by custkey) FROM (SELECT shippriority, custkey, sum(totalprice) FROM orders GROUP BY shippriority, custkey)", anyTree( window(windowMatcherBuilder -> windowMatcherBuilder .specification(specification(ImmutableList.of("custkey"), ImmutableList.of(), ImmutableMap.of())) .addFunction(functionCall("rank", Optional.empty(), ImmutableList.of())), exchange(LOCAL, GATHER, exchange(REMOTE, REPARTITION, project(aggregation(singleGroupingSet("shippriority", "custkey"), ImmutableMap.of(), ImmutableMap.of(), Optional.empty(), FINAL, exchange(LOCAL, GATHER, exchange(REMOTE, REPARTITION, anyTree(tableScan("orders", ImmutableMap.of("custkey", "custkey", "shippriority", "shippriority")))))))))))); }
@Test public void testAggregation() { // simple group by assertDistributedPlan("SELECT orderstatus, sum(totalprice) FROM orders GROUP BY orderstatus", anyTree( aggregation( ImmutableMap.of("final_sum", functionCall("sum", ImmutableList.of("partial_sum"))), FINAL, exchange(LOCAL, GATHER, exchange(REMOTE, REPARTITION, aggregation( ImmutableMap.of("partial_sum", functionCall("sum", ImmutableList.of("totalprice"))), PARTIAL, anyTree(tableScan("orders", ImmutableMap.of("totalprice", "totalprice"))))))))); // simple group by over filter that keeps at most one group assertDistributedPlan("SELECT orderstatus, sum(totalprice) FROM orders WHERE orderstatus='O' GROUP BY orderstatus", anyTree( aggregation( ImmutableMap.of("final_sum", functionCall("sum", ImmutableList.of("partial_sum"))), FINAL, exchange(LOCAL, GATHER, exchange(REMOTE, REPARTITION, aggregation( ImmutableMap.of("partial_sum", functionCall("sum", ImmutableList.of("totalprice"))), PARTIAL, anyTree(tableScan("orders", ImmutableMap.of("totalprice", "totalprice"))))))))); }
@Test public void testCorrelatedScalarSubqueryInSelect() { assertDistributedPlan("SELECT name, (SELECT name FROM region WHERE regionkey = nation.regionkey) FROM nation", anyTree( filter(format("CASE \"is_distinct\" WHEN true THEN true ELSE CAST(fail(%s, 'Scalar sub-query has returned multiple rows') AS boolean) END", SUBQUERY_MULTIPLE_ROWS.toErrorCode().getCode()), markDistinct("is_distinct", ImmutableList.of("unique"), join(LEFT, ImmutableList.of(equiJoinClause("n_regionkey", "r_regionkey")), assignUniqueId("unique", exchange(REMOTE, REPARTITION, anyTree(tableScan("nation", ImmutableMap.of("n_regionkey", "regionkey"))))), anyTree( tableScan("region", ImmutableMap.of("r_regionkey", "regionkey")))))))); }
@Test public void testOrderBy() { // don't force gather assertPlanWithSession("SELECT orderkey FROM orders ORDER BY orderkey", singleNodeOutput(false), false, output( exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.GATHER, anyTree( tableScan("orders"))))); // force gather, same result assertPlanWithSession("SELECT orderkey FROM orders ORDER BY orderkey", singleNodeOutput(true), false, output( exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.GATHER, anyTree( tableScan("orders"))))); }
@Test public void testFullOuterJoinWithCoalesce() { assertDistributedPlan("SELECT coalesce(ts.a, r.a) " + "FROM (" + " SELECT coalesce(t.a, s.a) AS a " + " FROM (VALUES (1), (2), (3)) t(a) " + " FULL OUTER JOIN (VALUES (1), (4)) s(a)" + " ON t.a = s.a) ts " + "FULL OUTER JOIN (VALUES (2), (5)) r(a) on ts.a = r.a", anyTree( project( ImmutableMap.of("expr", expression("coalesce(ts, r)")), join( FULL, ImmutableList.of(equiJoinClause("ts", "r")), anyTree( project( ImmutableMap.of("ts", expression("coalesce(t, s)")), join( FULL, ImmutableList.of(equiJoinClause("t", "s")), exchange(REMOTE, REPARTITION, anyTree(values(ImmutableList.of("t")))), exchange(LOCAL, GATHER, anyTree(values(ImmutableList.of("s"))))))), exchange(LOCAL, GATHER, anyTree(values(ImmutableList.of("r")))))))); } }
node(JoinNode.class, assignUniqueId("unique", exchange(REMOTE, REPARTITION, anyTree( tableScan("nation", ImmutableMap.of("n_name", "name", "n_regionkey", "regionkey"))))),
.specification(specification(ImmutableList.of("orderstatus"), ImmutableList.of(), ImmutableMap.of())) .addFunction(functionCall("rank", Optional.empty(), ImmutableList.of())), exchange(LOCAL, GATHER, exchange(REMOTE, REPARTITION, project(tableScan("orders", ImmutableMap.of("orderstatus", "orderstatus")))))))); rowNumber(rowNumberMatcherBuilder -> rowNumberMatcherBuilder .partitionBy(ImmutableList.of("orderstatus")), exchange(LOCAL, GATHER, exchange(REMOTE, REPARTITION, project(tableScan("orders", ImmutableMap.of("orderstatus", "orderstatus")))))))); ImmutableMap.of("custkey", ASC_NULLS_LAST)) .partial(false), exchange(LOCAL, GATHER, exchange(REMOTE, REPARTITION, topNRowNumber(topNRowNumber -> topNRowNumber .specification(
@Test public void testSimpleScan() { // don't force gather assertPlanWithSession("SELECT * FROM orders", singleNodeOutput(false), false, output( tableScan("orders"))); // force gather assertPlanWithSession("SELECT * FROM orders", singleNodeOutput(true), false, output( exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.GATHER, tableScan("orders")))); }
private void assertExistsRewrittenToAggregationBelowJoin(@Language("SQL") String actual, @Language("SQL") String expected, boolean extraAggregation) { PlanMatchPattern source = node(ValuesNode.class); if (extraAggregation) { source = aggregation(ImmutableMap.of(), exchange(LOCAL, REPARTITION, aggregation(ImmutableMap.of(), anyTree( node(ValuesNode.class))))); } assertions.assertQueryAndPlan(actual, expected, anyTree( node(JoinNode.class, anyTree( node(ValuesNode.class)), anyTree( aggregation(ImmutableMap.of(), FINAL, exchange(LOCAL, REPARTITION, aggregation(ImmutableMap.of(), PARTIAL, anyTree(source))))))), plan -> assertEquals(countFinalAggregationNodes(plan), extraAggregation ? 2 : 1)); }
anyTree( spatialJoin("st_contains(g1, g3)", Optional.of(KDB_TREE_JSON), anyTree(unnest(exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.REPARTITION, project(ImmutableMap.of("p1", expression(format("spatial_partitions(cast('%s' as kdbtree), g1)", KDB_TREE_JSON))), project(ImmutableMap.of("g1", expression("ST_GeometryFromText(cast(name_a1 as varchar))")), project(ImmutableMap.of("g1", expression("ST_GeometryFromText(cast(name_a as varchar))")), tableScan("customer", ImmutableMap.of("name_a", "name")))))), anyTree(unnest(exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.REPARTITION, project(ImmutableMap.of("p2", expression(format("spatial_partitions(cast('%s' as kdbtree), g2)", KDB_TREE_JSON))), project(ImmutableMap.of("g2", expression("ST_GeometryFromText(cast(name_b1 as varchar))")),
exchange(REMOTE, REPARTITION, anyTree( tableScan("region", ImmutableMap.of("LEFT_REGIONKEY", "regionkey")))))), anyTree( exchange(REMOTE, REPARTITION, tableScan("region", ImmutableMap.of("RIGHT_REGIONKEY", "regionkey")))))), node(ValuesNode.class)), anyTree( exchange(REMOTE, GATHER, node(TableScanNode.class)))))); node(TableScanNode.class)), anyTree( exchange(REMOTE, REPLICATE, node(TableScanNode.class))))));
@Test public void testGroupBy() { // don't force gather assertPlanWithSession("SELECT orderkey, count(*) FROM orders GROUP BY orderkey", singleNodeOutput(false), false, output( node(AggregationNode.class, tableScan("orders")))); // force gather assertPlanWithSession("SELECT orderkey, count(*) FROM orders GROUP BY orderkey", singleNodeOutput(true), false, output( exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.GATHER, node(AggregationNode.class, tableScan("orders"))))); }
@Test public void testFilteredSelectFromPartitionedTable() { // use all optimizers, including AddExchanges List<PlanOptimizer> allOptimizers = getQueryRunner().getPlanOptimizers(false); assertPlan( "SELECT DISTINCT orderstatus FROM orders", // TODO this could be optimized to VALUES with values from partitions anyTree( tableScan("orders")), allOptimizers); assertPlan( "SELECT orderstatus FROM orders WHERE orderstatus = 'O'", // predicate matches exactly single partition, no FilterNode needed output( exchange( tableScan("orders"))), allOptimizers); assertPlan( "SELECT orderstatus FROM orders WHERE orderstatus = 'no_such_partition_value'", output( values("orderstatus")), allOptimizers); }
}) .matches( exchange( project( values(ImmutableList.of("a")))
.matches( project( exchange(REMOTE, GATHER, ImmutableList.of(sort("sortSymbol", ASCENDING, FIRST)), project( values(
.matches( project( exchange( project( values(