Examples with LEFT com.facebook.presto.sql.planner.plan.JoinNode.Type.LEFT used on opensource projects

Example 1 with LEFT

use of com.facebook.presto.sql.planner.plan.JoinNode.Type.LEFT in project presto by prestodb.

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_STREAMING, ExchangeNode.Type.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().isRemote()), 1);
    Consumer<Plan> validateSingleStreamingAggregation = plan -> assertEquals(countOfMatchingNodes(plan, node -> node instanceof AggregationNode && ((AggregationNode) node).getGroupingKeys().contains(new VariableReferenceExpression(Optional.empty(), "unique", BIGINT)) && ((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 2 with LEFT

use of com.facebook.presto.sql.planner.plan.JoinNode.Type.LEFT in project presto by prestodb.

the class TestDetermineJoinDistributionType method testChoosesLeftWhenCriteriaEmpty.

@Test
public void testChoosesLeftWhenCriteriaEmpty() {
    int aRows = 1_000__00;
    int bRows = 1_0;
    assertDetermineJoinDistributionType(new CostComparator(75, 10, 15)).setSystemProperty(JOIN_DISTRIBUTION_TYPE, JoinDistributionType.AUTOMATIC.name()).setSystemProperty(JOIN_MAX_BROADCAST_TABLE_SIZE, "1PB").overrideStats("valuesA", PlanNodeStatsEstimate.builder().setOutputRowCount(aRows).addVariableStatistics(ImmutableMap.of(new VariableReferenceExpression(Optional.empty(), "A1", BIGINT), new VariableStatsEstimate(0, 100, 0, 640000, 100))).build()).overrideStats("valuesB", PlanNodeStatsEstimate.builder().setOutputRowCount(bRows).addVariableStatistics(ImmutableMap.of(new VariableReferenceExpression(Optional.empty(), "B1", BIGINT), new VariableStatsEstimate(0, 100, 0, 640000, 100))).build()).on(p -> p.join(RIGHT, p.values(new PlanNodeId("valuesA"), aRows, p.variable("A1", BIGINT)), p.values(new PlanNodeId("valuesB"), bRows, p.variable("B1", BIGINT)), ImmutableList.of(), ImmutableList.of(p.variable("A1", BIGINT), p.variable("B1", BIGINT)), Optional.empty())).matches(join(LEFT, ImmutableList.of(), Optional.empty(), Optional.of(REPLICATED), values(ImmutableMap.of("B1", 0)), values(ImmutableMap.of("A1", 0))));
}

Example 3 with LEFT

use of com.facebook.presto.sql.planner.plan.JoinNode.Type.LEFT in project presto by prestodb.

the class TestDetermineJoinDistributionType method testFlipAndReplicateRightOuterJoinWhenJoinCardinalityUnknown.

@Test
public void testFlipAndReplicateRightOuterJoinWhenJoinCardinalityUnknown() {
    int aRows = 10;
    int bRows = 1_000_000;
    assertDetermineJoinDistributionType(new CostComparator(75, 10, 15)).setSystemProperty(JOIN_DISTRIBUTION_TYPE, JoinDistributionType.AUTOMATIC.name()).overrideStats("valuesA", PlanNodeStatsEstimate.builder().setOutputRowCount(aRows).addVariableStatistics(ImmutableMap.of(new VariableReferenceExpression(Optional.empty(), "A1", BIGINT), VariableStatsEstimate.unknown())).build()).overrideStats("valuesB", PlanNodeStatsEstimate.builder().setOutputRowCount(bRows).addVariableStatistics(ImmutableMap.of(new VariableReferenceExpression(Optional.empty(), "B1", BIGINT), VariableStatsEstimate.unknown())).build()).on(p -> p.join(RIGHT, p.values(new PlanNodeId("valuesA"), aRows, p.variable("A1", BIGINT)), p.values(new PlanNodeId("valuesB"), bRows, p.variable("B1", BIGINT)), ImmutableList.of(new JoinNode.EquiJoinClause(p.variable("A1", BIGINT), p.variable("B1", BIGINT))), ImmutableList.of(p.variable("A1", BIGINT), p.variable("B1", BIGINT)), Optional.empty())).matches(join(LEFT, ImmutableList.of(equiJoinClause("A1", "B1")), Optional.empty(), Optional.of(REPLICATED), values(ImmutableMap.of("A1", 0)), values(ImmutableMap.of("B1", 0))));
}

Example 4 with LEFT

use of com.facebook.presto.sql.planner.plan.JoinNode.Type.LEFT in project presto by prestodb.

the class TestDetermineJoinDistributionType method testReplicateLeftOuterJoin.

@Test
public void testReplicateLeftOuterJoin() {
    int aRows = 10_000;
    int bRows = 10;
    assertDetermineJoinDistributionType(new CostComparator(75, 10, 15)).setSystemProperty(JOIN_DISTRIBUTION_TYPE, JoinDistributionType.AUTOMATIC.name()).overrideStats("valuesA", PlanNodeStatsEstimate.builder().setOutputRowCount(aRows).addVariableStatistics(ImmutableMap.of(new VariableReferenceExpression(Optional.empty(), "A1", BIGINT), new VariableStatsEstimate(0, 100, 0, 640000, 100))).build()).overrideStats("valuesB", PlanNodeStatsEstimate.builder().setOutputRowCount(bRows).addVariableStatistics(ImmutableMap.of(new VariableReferenceExpression(Optional.empty(), "B1", BIGINT), new VariableStatsEstimate(0, 100, 0, 640000, 100))).build()).on(p -> p.join(LEFT, p.values(new PlanNodeId("valuesA"), aRows, p.variable("A1", BIGINT)), p.values(new PlanNodeId("valuesB"), bRows, p.variable("B1", BIGINT)), ImmutableList.of(new JoinNode.EquiJoinClause(p.variable("A1", BIGINT), p.variable("B1", BIGINT))), ImmutableList.of(p.variable("A1", BIGINT), p.variable("B1", BIGINT)), Optional.empty())).matches(join(LEFT, ImmutableList.of(equiJoinClause("A1", "B1")), Optional.empty(), Optional.of(REPLICATED), values(ImmutableMap.of("A1", 0)), values(ImmutableMap.of("B1", 0))));
}

Example 5 with LEFT

use of com.facebook.presto.sql.planner.plan.JoinNode.Type.LEFT in project presto by prestodb.

the class ExtractSpatialJoins method tryCreateSpatialJoin.

private static Result tryCreateSpatialJoin(Context context, JoinNode joinNode, RowExpression filter, PlanNodeId nodeId, List<VariableReferenceExpression> outputVariables, CallExpression spatialFunction, Optional<RowExpression> radius, Metadata metadata, SplitManager splitManager, PageSourceManager pageSourceManager) {
    FunctionAndTypeManager functionAndTypeManager = metadata.getFunctionAndTypeManager();
    List<RowExpression> arguments = spatialFunction.getArguments();
    verify(arguments.size() == 2);
    RowExpression firstArgument = arguments.get(0);
    RowExpression secondArgument = arguments.get(1);
    // Currently, only inner joins are supported for spherical geometries.
    if (joinNode.getType() != INNER && isSphericalJoin(metadata, firstArgument, secondArgument)) {
        return Result.empty();
    }
    Set<VariableReferenceExpression> firstVariables = extractUnique(firstArgument);
    Set<VariableReferenceExpression> secondVariables = extractUnique(secondArgument);
    if (firstVariables.isEmpty() || secondVariables.isEmpty()) {
        return Result.empty();
    }
    // If either firstArgument or secondArgument is not a
    // VariableReferenceExpression, will replace the left/right join node
    // with a projection that adds the argument as a variable.
    Optional<VariableReferenceExpression> newFirstVariable = newGeometryVariable(context, firstArgument);
    Optional<VariableReferenceExpression> newSecondVariable = newGeometryVariable(context, secondArgument);
    PlanNode leftNode = joinNode.getLeft();
    PlanNode rightNode = joinNode.getRight();
    PlanNode newLeftNode;
    PlanNode newRightNode;
    // Check if the order of arguments of the spatial function matches the order of join sides
    int alignment = checkAlignment(joinNode, firstVariables, secondVariables);
    if (alignment > 0) {
        newLeftNode = newFirstVariable.map(variable -> addProjection(context, leftNode, variable, firstArgument)).orElse(leftNode);
        newRightNode = newSecondVariable.map(variable -> addProjection(context, rightNode, variable, secondArgument)).orElse(rightNode);
    } else if (alignment < 0) {
        newLeftNode = newSecondVariable.map(variable -> addProjection(context, leftNode, variable, secondArgument)).orElse(leftNode);
        newRightNode = newFirstVariable.map(variable -> addProjection(context, rightNode, variable, firstArgument)).orElse(rightNode);
    } else {
        return Result.empty();
    }
    RowExpression newFirstArgument = mapToExpression(newFirstVariable, firstArgument);
    RowExpression newSecondArgument = mapToExpression(newSecondVariable, secondArgument);
    // Implement partitioned spatial joins:
    // If the session parameter points to a valid spatial partitioning, use
    // that to assign to each probe and build rows the partitions that the
    // geometry intersects.  This is a projection that adds an array of ints
    // which is subsequently unnested.
    Optional<String> spatialPartitioningTableName = canPartitionSpatialJoin(joinNode) ? getSpatialPartitioningTableName(context.getSession()) : Optional.empty();
    Optional<KdbTree> kdbTree = spatialPartitioningTableName.map(tableName -> loadKdbTree(tableName, context.getSession(), metadata, splitManager, pageSourceManager));
    Optional<VariableReferenceExpression> leftPartitionVariable = Optional.empty();
    Optional<VariableReferenceExpression> rightPartitionVariable = Optional.empty();
    if (kdbTree.isPresent()) {
        leftPartitionVariable = Optional.of(context.getVariableAllocator().newVariable(newFirstArgument.getSourceLocation(), "pid", INTEGER));
        rightPartitionVariable = Optional.of(context.getVariableAllocator().newVariable(newSecondArgument.getSourceLocation(), "pid", INTEGER));
        if (alignment > 0) {
            newLeftNode = addPartitioningNodes(context, functionAndTypeManager, newLeftNode, leftPartitionVariable.get(), kdbTree.get(), newFirstArgument, Optional.empty());
            newRightNode = addPartitioningNodes(context, functionAndTypeManager, newRightNode, rightPartitionVariable.get(), kdbTree.get(), newSecondArgument, radius);
        } else {
            newLeftNode = addPartitioningNodes(context, functionAndTypeManager, newLeftNode, leftPartitionVariable.get(), kdbTree.get(), newSecondArgument, Optional.empty());
            newRightNode = addPartitioningNodes(context, functionAndTypeManager, newRightNode, rightPartitionVariable.get(), kdbTree.get(), newFirstArgument, radius);
        }
    }
    CallExpression newSpatialFunction = new CallExpression(spatialFunction.getSourceLocation(), spatialFunction.getDisplayName(), spatialFunction.getFunctionHandle(), spatialFunction.getType(), ImmutableList.of(newFirstArgument, newSecondArgument));
    RowExpression newFilter = RowExpressionNodeInliner.replaceExpression(filter, ImmutableMap.of(spatialFunction, newSpatialFunction));
    return Result.ofPlanNode(new SpatialJoinNode(joinNode.getSourceLocation(), nodeId, SpatialJoinNode.Type.fromJoinNodeType(joinNode.getType()), newLeftNode, newRightNode, outputVariables, newFilter, leftPartitionVariable, rightPartitionVariable, kdbTree.map(KdbTreeUtils::toJson)));
}