Examples with Unnest com.facebook.presto.sql.tree.Unnest used on opensource projects

Example 1 with Unnest

use of com.facebook.presto.sql.tree.Unnest in project presto by prestodb.

the class TestSqlParser method testUnnest.

@Test
public void testUnnest() {
    assertStatement("SELECT * FROM t CROSS JOIN UNNEST(a)", simpleQuery(selectList(new AllColumns()), new Join(Join.Type.CROSS, new Table(QualifiedName.of("t")), new Unnest(ImmutableList.of(new Identifier("a")), false), Optional.empty())));
    assertStatement("SELECT * FROM t CROSS JOIN UNNEST(a, b) WITH ORDINALITY", simpleQuery(selectList(new AllColumns()), new Join(Join.Type.CROSS, new Table(QualifiedName.of("t")), new Unnest(ImmutableList.of(new Identifier("a"), new Identifier("b")), true), Optional.empty())));
    assertStatement("SELECT * FROM t FULL JOIN UNNEST(a) AS tmp (c) ON true", simpleQuery(selectList(new AllColumns()), new Join(Join.Type.FULL, new Table(QualifiedName.of("t")), new AliasedRelation(new Unnest(ImmutableList.of(new Identifier("a")), false), new Identifier("tmp"), ImmutableList.of(new Identifier("c"))), Optional.of(new JoinOn(BooleanLiteral.TRUE_LITERAL)))));
}

Example 2 with Unnest

use of com.facebook.presto.sql.tree.Unnest in project presto by prestodb.

the class MaterializedViewPlanValidator method visitJoin.

@Override
protected Void visitJoin(Join node, MaterializedViewPlanValidatorContext context) {
    context.pushJoinNode(node);
    if (context.getJoinNodes().size() > 1) {
        throw new SemanticException(NOT_SUPPORTED, node, "More than one join in materialized view is not supported yet.");
    }
    JoinCriteria joinCriteria;
    switch(node.getType()) {
        case INNER:
            if (!node.getCriteria().isPresent()) {
                throw new SemanticException(NOT_SUPPORTED, node, "Inner join with no criteria is not supported for materialized view.");
            }
            joinCriteria = node.getCriteria().get();
            if (!(joinCriteria instanceof JoinOn)) {
                throw new SemanticException(NOT_SUPPORTED, node, "Only join-on is supported for materialized view.");
            }
            process(node.getLeft(), context);
            process(node.getRight(), context);
            context.setWithinJoinOn(true);
            process(((JoinOn) joinCriteria).getExpression(), context);
            context.setWithinJoinOn(false);
            break;
        case CROSS:
            if (!(node.getRight() instanceof AliasedRelation)) {
                throw new SemanticException(NOT_SUPPORTED, node, "Cross join is supported only with unnest for materialized view.");
            }
            AliasedRelation right = (AliasedRelation) node.getRight();
            if (!(right.getRelation() instanceof Unnest)) {
                throw new SemanticException(NOT_SUPPORTED, node, "Cross join is supported only with unnest for materialized view.");
            }
            process(node.getLeft(), context);
            break;
        case LEFT:
            if (!node.getCriteria().isPresent()) {
                throw new SemanticException(NOT_SUPPORTED, node, "Outer join with no criteria is not supported for materialized view.");
            }
            joinCriteria = node.getCriteria().get();
            if (!(joinCriteria instanceof JoinOn)) {
                throw new SemanticException(NOT_SUPPORTED, node, "Only join-on is supported for materialized view.");
            }
            process(node.getLeft(), context);
            boolean wasWithinOuterJoin = context.isWithinOuterJoin();
            context.setWithinOuterJoin(true);
            process(node.getRight(), context);
            // withinOuterJoin denotes if we are within an outer side of a join. Because it can be nested, replace it with its older value.
            // So we set it to false, only when we leave the topmost outer join.
            context.setWithinOuterJoin(wasWithinOuterJoin);
            context.setWithinJoinOn(true);
            process(((JoinOn) joinCriteria).getExpression(), context);
            context.setWithinJoinOn(false);
            break;
        default:
            throw new SemanticException(NOT_SUPPORTED, node, "Only inner join, left join and cross join unnested are supported for materialized view.");
    }
    context.popJoinNode();
    return null;
}

Example 3 with Unnest

use of com.facebook.presto.sql.tree.Unnest in project presto by prestodb.

the class RelationPlanner method visitJoin.

@Override
protected RelationPlan visitJoin(Join node, Void context) {
    // TODO: translate the RIGHT join into a mirrored LEFT join when we refactor (@martint)
    RelationPlan leftPlan = process(node.getLeft(), context);
    Optional<Unnest> unnest = getUnnest(node.getRight());
    if (unnest.isPresent()) {
        if (node.getType() != Join.Type.CROSS && node.getType() != Join.Type.IMPLICIT) {
            throw notSupportedException(unnest.get(), "UNNEST on other than the right side of CROSS JOIN");
        }
        return planCrossJoinUnnest(leftPlan, node, unnest.get());
    }
    Optional<Lateral> lateral = getLateral(node.getRight());
    if (lateral.isPresent()) {
        if (node.getType() != Join.Type.CROSS && node.getType() != Join.Type.IMPLICIT) {
            throw notSupportedException(lateral.get(), "LATERAL on other than the right side of CROSS JOIN");
        }
        return planLateralJoin(node, leftPlan, lateral.get());
    }
    RelationPlan rightPlan = process(node.getRight(), context);
    if (node.getCriteria().isPresent() && node.getCriteria().get() instanceof JoinUsing) {
        return planJoinUsing(node, leftPlan, rightPlan);
    }
    PlanBuilder leftPlanBuilder = initializePlanBuilder(leftPlan);
    PlanBuilder rightPlanBuilder = initializePlanBuilder(rightPlan);
    // NOTE: variables must be in the same order as the outputDescriptor
    List<VariableReferenceExpression> outputs = ImmutableList.<VariableReferenceExpression>builder().addAll(leftPlan.getFieldMappings()).addAll(rightPlan.getFieldMappings()).build();
    ImmutableList.Builder<JoinNode.EquiJoinClause> equiClauses = ImmutableList.builder();
    List<Expression> complexJoinExpressions = new ArrayList<>();
    List<Expression> postInnerJoinConditions = new ArrayList<>();
    if (node.getType() != Join.Type.CROSS && node.getType() != Join.Type.IMPLICIT) {
        Expression criteria = analysis.getJoinCriteria(node);
        RelationType left = analysis.getOutputDescriptor(node.getLeft());
        RelationType right = analysis.getOutputDescriptor(node.getRight());
        List<Expression> leftComparisonExpressions = new ArrayList<>();
        List<Expression> rightComparisonExpressions = new ArrayList<>();
        List<ComparisonExpression.Operator> joinConditionComparisonOperators = new ArrayList<>();
        for (Expression conjunct : ExpressionUtils.extractConjuncts(criteria)) {
            conjunct = ExpressionUtils.normalize(conjunct);
            if (!isEqualComparisonExpression(conjunct) && node.getType() != INNER) {
                complexJoinExpressions.add(conjunct);
                continue;
            }
            Set<QualifiedName> dependencies = VariablesExtractor.extractNames(conjunct, analysis.getColumnReferences());
            if (dependencies.stream().allMatch(left::canResolve) || dependencies.stream().allMatch(right::canResolve)) {
                // If the conjunct can be evaluated entirely with the inputs on either side of the join, add
                // it to the list complex expressions and let the optimizers figure out how to push it down later.
                complexJoinExpressions.add(conjunct);
            } else if (conjunct instanceof ComparisonExpression) {
                Expression firstExpression = ((ComparisonExpression) conjunct).getLeft();
                Expression secondExpression = ((ComparisonExpression) conjunct).getRight();
                ComparisonExpression.Operator comparisonOperator = ((ComparisonExpression) conjunct).getOperator();
                Set<QualifiedName> firstDependencies = VariablesExtractor.extractNames(firstExpression, analysis.getColumnReferences());
                Set<QualifiedName> secondDependencies = VariablesExtractor.extractNames(secondExpression, analysis.getColumnReferences());
                if (firstDependencies.stream().allMatch(left::canResolve) && secondDependencies.stream().allMatch(right::canResolve)) {
                    leftComparisonExpressions.add(firstExpression);
                    rightComparisonExpressions.add(secondExpression);
                    addNullFilters(complexJoinExpressions, node.getType(), firstExpression, secondExpression);
                    joinConditionComparisonOperators.add(comparisonOperator);
                } else if (firstDependencies.stream().allMatch(right::canResolve) && secondDependencies.stream().allMatch(left::canResolve)) {
                    leftComparisonExpressions.add(secondExpression);
                    rightComparisonExpressions.add(firstExpression);
                    addNullFilters(complexJoinExpressions, node.getType(), secondExpression, firstExpression);
                    joinConditionComparisonOperators.add(comparisonOperator.flip());
                } else {
                    // the case when we mix variables from both left and right join side on either side of condition.
                    complexJoinExpressions.add(conjunct);
                }
            } else {
                complexJoinExpressions.add(conjunct);
            }
        }
        leftPlanBuilder = subqueryPlanner.handleSubqueries(leftPlanBuilder, leftComparisonExpressions, node);
        rightPlanBuilder = subqueryPlanner.handleSubqueries(rightPlanBuilder, rightComparisonExpressions, node);
        // Add projections for join criteria
        leftPlanBuilder = leftPlanBuilder.appendProjections(leftComparisonExpressions, variableAllocator, idAllocator);
        rightPlanBuilder = rightPlanBuilder.appendProjections(rightComparisonExpressions, variableAllocator, idAllocator);
        for (int i = 0; i < leftComparisonExpressions.size(); i++) {
            if (joinConditionComparisonOperators.get(i) == ComparisonExpression.Operator.EQUAL) {
                VariableReferenceExpression leftVariable = leftPlanBuilder.translateToVariable(leftComparisonExpressions.get(i));
                VariableReferenceExpression righVariable = rightPlanBuilder.translateToVariable(rightComparisonExpressions.get(i));
                equiClauses.add(new JoinNode.EquiJoinClause(leftVariable, righVariable));
            } else {
                Expression leftExpression = leftPlanBuilder.rewrite(leftComparisonExpressions.get(i));
                Expression rightExpression = rightPlanBuilder.rewrite(rightComparisonExpressions.get(i));
                postInnerJoinConditions.add(new ComparisonExpression(joinConditionComparisonOperators.get(i), leftExpression, rightExpression));
            }
        }
    }
    PlanNode root = new JoinNode(getSourceLocation(node), idAllocator.getNextId(), JoinNodeUtils.typeConvert(node.getType()), leftPlanBuilder.getRoot(), rightPlanBuilder.getRoot(), equiClauses.build(), ImmutableList.<VariableReferenceExpression>builder().addAll(leftPlanBuilder.getRoot().getOutputVariables()).addAll(rightPlanBuilder.getRoot().getOutputVariables()).build(), Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableMap.of());
    if (node.getType() != INNER) {
        for (Expression complexExpression : complexJoinExpressions) {
            Set<InPredicate> inPredicates = subqueryPlanner.collectInPredicateSubqueries(complexExpression, node);
            if (!inPredicates.isEmpty()) {
                InPredicate inPredicate = Iterables.getLast(inPredicates);
                throw notSupportedException(inPredicate, "IN with subquery predicate in join condition");
            }
        }
        // subqueries can be applied only to one side of join - left side is selected in arbitrary way
        leftPlanBuilder = subqueryPlanner.handleUncorrelatedSubqueries(leftPlanBuilder, complexJoinExpressions, node);
    }
    RelationPlan intermediateRootRelationPlan = new RelationPlan(root, analysis.getScope(node), outputs);
    TranslationMap translationMap = new TranslationMap(intermediateRootRelationPlan, analysis, lambdaDeclarationToVariableMap);
    translationMap.setFieldMappings(outputs);
    translationMap.putExpressionMappingsFrom(leftPlanBuilder.getTranslations());
    translationMap.putExpressionMappingsFrom(rightPlanBuilder.getTranslations());
    if (node.getType() != INNER && !complexJoinExpressions.isEmpty()) {
        Expression joinedFilterCondition = ExpressionUtils.and(complexJoinExpressions);
        Expression rewrittenFilterCondition = translationMap.rewrite(joinedFilterCondition);
        root = new JoinNode(getSourceLocation(node), idAllocator.getNextId(), JoinNodeUtils.typeConvert(node.getType()), leftPlanBuilder.getRoot(), rightPlanBuilder.getRoot(), equiClauses.build(), ImmutableList.<VariableReferenceExpression>builder().addAll(leftPlanBuilder.getRoot().getOutputVariables()).addAll(rightPlanBuilder.getRoot().getOutputVariables()).build(), Optional.of(castToRowExpression(rewrittenFilterCondition)), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableMap.of());
    }
    if (node.getType() == INNER) {
        // rewrite all the other conditions using output variables from left + right plan node.
        PlanBuilder rootPlanBuilder = new PlanBuilder(translationMap, root);
        rootPlanBuilder = subqueryPlanner.handleSubqueries(rootPlanBuilder, complexJoinExpressions, node);
        for (Expression expression : complexJoinExpressions) {
            postInnerJoinConditions.add(rootPlanBuilder.rewrite(expression));
        }
        root = rootPlanBuilder.getRoot();
        Expression postInnerJoinCriteria;
        if (!postInnerJoinConditions.isEmpty()) {
            postInnerJoinCriteria = ExpressionUtils.and(postInnerJoinConditions);
            root = new FilterNode(getSourceLocation(postInnerJoinCriteria), idAllocator.getNextId(), root, castToRowExpression(postInnerJoinCriteria));
        }
    }
    return new RelationPlan(root, analysis.getScope(node), outputs);
}