Search in sources :

Example 6 with JoinNode

use of org.voltdb.planner.parseinfo.JoinNode in project voltdb by VoltDB.

the class SelectSubPlanAssembler method generateInnerAccessPaths.

/**
     * Generate all possible access paths for an inner node in a join.
     * The set of potential index expressions depends whether the inner node can be inlined
     * with the NLIJ or not. In the former case, inner and inner-outer join expressions can
     * be considered for the index access. In the latter, only inner join expressions qualifies.
     *
     * @param parentNode A parent node to the node to generate paths to.
     */
private void generateInnerAccessPaths(BranchNode parentNode) {
    JoinNode innerChildNode = parentNode.getRightNode();
    assert (innerChildNode != null);
    // In case of inner join WHERE and JOIN expressions can be merged
    if (parentNode.getJoinType() == JoinType.INNER) {
        parentNode.m_joinInnerOuterList.addAll(parentNode.m_whereInnerOuterList);
        parentNode.m_whereInnerOuterList.clear();
        parentNode.m_joinInnerList.addAll(parentNode.m_whereInnerList);
        parentNode.m_whereInnerList.clear();
    }
    if (innerChildNode instanceof BranchNode) {
        generateOuterAccessPaths((BranchNode) innerChildNode);
        generateInnerAccessPaths((BranchNode) innerChildNode);
        // The inner node is a join node itself. Only naive access path is possible
        innerChildNode.m_accessPaths.add(getRelevantNaivePath(parentNode.m_joinInnerOuterList, parentNode.m_joinInnerList));
        return;
    }
    // The inner table can have multiple index access paths based on
    // inner and inner-outer join expressions plus the naive one.
    List<AbstractExpression> filterExprs = null;
    List<AbstractExpression> postExprs = null;
    // the inner join expression will effectively filter out inner tuple prior to the NLJ.
    if (parentNode.getJoinType() != JoinType.FULL) {
        filterExprs = parentNode.m_joinInnerList;
    } else {
        postExprs = parentNode.m_joinInnerList;
    }
    StmtTableScan innerTable = innerChildNode.getTableScan();
    assert (innerTable != null);
    innerChildNode.m_accessPaths.addAll(getRelevantAccessPathsForTable(innerTable, parentNode.m_joinInnerOuterList, filterExprs, postExprs));
    // If there are inner expressions AND inner-outer expressions, it could be that there
    // are indexed access paths that use elements of both in the indexing expressions,
    // especially in the case of a compound index.
    // These access paths can not be considered for use with an NLJ because they rely on
    // inner-outer expressions.
    // If there is a possibility that NLIJ will not be an option due to the
    // "special case" processing that puts a send/receive plan between the join node
    // and its inner child node, other access paths need to be considered that use the
    // same indexes as those identified so far but in a simpler, less effective way
    // that does not rely on inner-outer expressions.
    // The following simplistic method of finding these access paths is to force
    // inner-outer expressions to be handled as NLJ-compatible post-filters and repeat
    // the search for access paths.
    // This will typically generate some duplicate access paths, including the naive
    // sequential scan path and any indexed paths that happened to use only the inner
    // expressions.
    // For now, we deal with this redundancy by dropping (and re-generating) all
    // access paths EXCPT those that reference the inner-outer expressions.
    // TODO: implementing access path hash and equality and possibly using a "Set"
    // would allow deduping as new access paths are added OR
    // the simplified access path search process could be based on
    // the existing indexed access paths -- for each access path that "hasInnerOuterIndexExpression"
    // try to generate and add a simpler access path using the same index,
    // this time with the inner-outer expressions used only as non-indexable post-filters.
    // Don't bother generating these redundant or inferior access paths unless there is
    // an inner-outer expression and a chance that NLIJ will be taken out of the running.
    boolean mayNeedInnerSendReceive = (!m_partitioning.wasSpecifiedAsSingle()) && (m_partitioning.getCountOfPartitionedTables() > 0) && (parentNode.getJoinType() != JoinType.INNER) && !innerTable.getIsReplicated();
    if (mayNeedInnerSendReceive && !parentNode.m_joinInnerOuterList.isEmpty()) {
        List<AccessPath> innerOuterAccessPaths = new ArrayList<>();
        for (AccessPath innerAccessPath : innerChildNode.m_accessPaths) {
            if ((innerAccessPath.index != null) && hasInnerOuterIndexExpression(innerChildNode.getTableAlias(), innerAccessPath.indexExprs, innerAccessPath.initialExpr, innerAccessPath.endExprs)) {
                innerOuterAccessPaths.add(innerAccessPath);
            }
        }
        if (parentNode.getJoinType() != JoinType.FULL) {
            filterExprs = parentNode.m_joinInnerList;
            postExprs = parentNode.m_joinInnerOuterList;
        } else {
            // For FULL join type the inner join expressions must be part of the post predicate
            // in order to stay at the join node and not be pushed down to the inner node
            filterExprs = null;
            postExprs = new ArrayList<>(parentNode.m_joinInnerList);
            postExprs.addAll(parentNode.m_joinInnerOuterList);
        }
        Collection<AccessPath> nljAccessPaths = getRelevantAccessPathsForTable(innerTable, null, filterExprs, postExprs);
        innerChildNode.m_accessPaths.clear();
        innerChildNode.m_accessPaths.addAll(nljAccessPaths);
        innerChildNode.m_accessPaths.addAll(innerOuterAccessPaths);
    }
    assert (innerChildNode.m_accessPaths.size() > 0);
}
Also used : BranchNode(org.voltdb.planner.parseinfo.BranchNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) JoinNode(org.voltdb.planner.parseinfo.JoinNode) ArrayList(java.util.ArrayList) StmtTableScan(org.voltdb.planner.parseinfo.StmtTableScan)

Example 7 with JoinNode

use of org.voltdb.planner.parseinfo.JoinNode in project voltdb by VoltDB.

the class SelectSubPlanAssembler method queueJoinOrders.

/**
     * Compute every permutation of the list of involved tables and put them in a deque.
     * TODO(XIN): takes at least 3.3% cpu of planner. Optimize it when possible.
     */
public static ArrayDeque<JoinNode> queueJoinOrders(JoinNode joinNode, boolean findAll) {
    assert (joinNode != null);
    // Clone the original
    JoinNode clonedTree = (JoinNode) joinNode.clone();
    // Split join tree into a set of subtrees. The join type for all nodes in a subtree is the same
    List<JoinNode> subTrees = clonedTree.extractSubTrees();
    assert (!subTrees.isEmpty());
    // Generate possible join orders for each sub-tree separately
    ArrayList<List<JoinNode>> joinOrderList = generateJoinOrders(subTrees);
    // Reassemble the all possible combinations of the sub-tree and queue them
    ArrayDeque<JoinNode> joinOrders = new ArrayDeque<>();
    queueSubJoinOrders(joinOrderList, 0, new ArrayList<JoinNode>(), joinOrders, findAll);
    return joinOrders;
}
Also used : JoinNode(org.voltdb.planner.parseinfo.JoinNode) ArrayList(java.util.ArrayList) List(java.util.List) ArrayDeque(java.util.ArrayDeque)

Example 8 with JoinNode

use of org.voltdb.planner.parseinfo.JoinNode in project voltdb by VoltDB.

the class ParsedSelectStmt method isValidJoinOrder.

/**
     * Validate the specified join order against the join tree.
     * In general, outer joins are not associative and commutative.
     * Not all orders are valid.
     * In case of a valid join order, the initial join tree is
     * rebuilt to match the specified order
     * @param tables list of table aliases(or tables) to join
     * @return true if the join order is valid
     */
private boolean isValidJoinOrder(List<String> tableAliases) {
    assert (m_joinTree != null);
    // Split the original tree into the sub-trees
    // having the same join type for all nodes
    List<JoinNode> subTrees = m_joinTree.extractSubTrees();
    // For a sub-tree with inner joins only, any join order is valid.
    // The only requirement is that each and every table from that
    // sub-tree constitute an uninterrupted sequence in the specified
    // join order.
    // The outer joins are associative but changing the join order
    // precedence includes moving ON clauses to preserve the initial
    // SQL semantics.
    // For example,
    // T1 right join T2 on T1.C1 = T2.C1 left join T3 on T2.C2=T3.C2
    // can be rewritten as
    // T1 right join (T2 left join T3 on T2.C2=T3.C2) on T1.C1 = T2.C1
    // At the moment, such transformations are not supported.
    // The specified joined order must match the SQL order.
    int tableNameIdx = 0;
    List<JoinNode> finalSubTrees = new ArrayList<>();
    // the top sub-tree is the first one on the list.
    for (int i = subTrees.size() - 1; i >= 0; --i) {
        JoinNode subTree = subTrees.get(i);
        // Get all tables for the subTree
        List<JoinNode> subTableNodes = subTree.generateLeafNodesJoinOrder();
        JoinNode joinOrderSubTree;
        if ((subTree instanceof BranchNode) && ((BranchNode) subTree).getJoinType() != JoinType.INNER) {
            // add the sub-tree as is
            joinOrderSubTree = subTree;
            for (JoinNode tableNode : subTableNodes) {
                if (tableNode.getId() >= 0) {
                    String tableAlias = tableNode.getTableAlias();
                    if (!tableAliases.get(tableNameIdx++).equals(tableAlias)) {
                        return false;
                    }
                }
            }
        } else {
            // Collect all the "real" tables from the sub-tree
            // skipping the nodes representing
            // the sub-trees with the different join type (id < 0)
            Map<String, JoinNode> nodeNameMap = new HashMap<>();
            for (JoinNode tableNode : subTableNodes) {
                if (tableNode.getId() >= 0) {
                    nodeNameMap.put(tableNode.getTableAlias(), tableNode);
                }
            }
            // rearrange the sub tree to match the order
            List<JoinNode> joinOrderSubNodes = new ArrayList<>();
            for (int j = 0; j < subTableNodes.size(); ++j) {
                if (subTableNodes.get(j).getId() >= 0) {
                    assert (tableNameIdx < tableAliases.size());
                    String tableAlias = tableAliases.get(tableNameIdx);
                    if (tableAlias == null || !nodeNameMap.containsKey(tableAlias)) {
                        return false;
                    }
                    joinOrderSubNodes.add(nodeNameMap.get(tableAlias));
                    ++tableNameIdx;
                } else {
                    // It's dummy node
                    joinOrderSubNodes.add(subTableNodes.get(j));
                }
            }
            joinOrderSubTree = JoinNode.reconstructJoinTreeFromTableNodes(joinOrderSubNodes, JoinType.INNER);
            //Collect all the join/where conditions to reassign them later
            AbstractExpression combinedWhereExpr = subTree.getAllFilters();
            if (combinedWhereExpr != null) {
                joinOrderSubTree.setWhereExpression(combinedWhereExpr.clone());
            }
            // The new tree root node id must match the original one
            // to be able to reconnect the subtrees
            joinOrderSubTree.setId(subTree.getId());
        }
        finalSubTrees.add(0, joinOrderSubTree);
    }
    // if we got there the join order is OK. Rebuild the whole tree
    JoinNode newNode = JoinNode.reconstructJoinTreeFromSubTrees(finalSubTrees);
    m_joinOrderList.add(newNode);
    return true;
}
Also used : BranchNode(org.voltdb.planner.parseinfo.BranchNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) HashMap(java.util.HashMap) JoinNode(org.voltdb.planner.parseinfo.JoinNode) ArrayList(java.util.ArrayList)

Example 9 with JoinNode

use of org.voltdb.planner.parseinfo.JoinNode in project voltdb by VoltDB.

the class AbstractParsedStmt method parseTable.

/**
     *
     * @param tableNode
     */
private void parseTable(VoltXMLElement tableNode) {
    String tableName = tableNode.attributes.get("table");
    assert (tableName != null);
    String tableAlias = tableNode.attributes.get("tablealias");
    if (tableAlias == null) {
        tableAlias = tableName;
    }
    // Hsql rejects name conflicts in a single query
    m_tableAliasListAsJoinOrder.add(tableAlias);
    VoltXMLElement subqueryElement = null;
    // Possible sub-query
    for (VoltXMLElement childNode : tableNode.children) {
        if (!childNode.name.equals("tablesubquery")) {
            continue;
        }
        if (childNode.children.isEmpty()) {
            continue;
        }
        // sub-query FROM (SELECT ...)
        subqueryElement = childNode.children.get(0);
        break;
    }
    // add table to the query cache before processing the JOIN/WHERE expressions
    // The order is important because processing sub-query expressions assumes that
    // the sub-query is already registered
    StmtTableScan tableScan = null;
    Table table = null;
    // In case of a subquery we need to preserve its filter expressions
    AbstractExpression simplifiedSubqueryFilter = null;
    if (subqueryElement == null) {
        table = getTableFromDB(tableName);
        assert (table != null);
        tableScan = addTableToStmtCache(table, tableAlias);
        m_tableList.add(table);
    } else {
        AbstractParsedStmt subquery = parseFromSubQuery(subqueryElement);
        StmtSubqueryScan subqueryScan = addSubqueryToStmtCache(subquery, tableAlias);
        tableScan = subqueryScan;
        StmtTargetTableScan simpler = simplifierForSubquery(subquery);
        if (simpler != null) {
            tableScan = addSimplifiedSubqueryToStmtCache(subqueryScan, simpler);
            table = simpler.getTargetTable();
            // Extract subquery's filters
            assert (subquery.m_joinTree != null);
            // Adjust the table alias in all TVEs from the eliminated
            // subquery expressions. Example:
            // SELECT TA2.CA FROM (SELECT C CA FROM T TA1 WHERE C > 0) TA2
            // The table alias TA1 from the original TVE (T)TA1.C from the
            // subquery WHERE condition needs to be replaced with the alias
            // TA2. The new TVE will be (T)TA2.C.
            // The column alias does not require an adjustment.
            simplifiedSubqueryFilter = subquery.m_joinTree.getAllFilters();
            List<TupleValueExpression> tves = ExpressionUtil.getTupleValueExpressions(simplifiedSubqueryFilter);
            for (TupleValueExpression tve : tves) {
                tve.setTableAlias(tableScan.getTableAlias());
                tve.setOrigStmtId(m_stmtId);
            }
        }
    }
    AbstractExpression joinExpr = parseJoinCondition(tableNode);
    AbstractExpression whereExpr = parseWhereCondition(tableNode);
    if (simplifiedSubqueryFilter != null) {
        // Add subqueruy's expressions as JOIN filters to make sure they will
        // stay at the node level in case of an OUTER joins and won't affect
        // the join simplification process:
        // select * from T LEFT JOIN (select C FROM T1 WHERE C > 2) S ON T.C = S.C;
        joinExpr = (joinExpr != null) ? ExpressionUtil.combine(joinExpr, simplifiedSubqueryFilter) : simplifiedSubqueryFilter;
    }
    // The join type of the leaf node is always INNER
    // For a new tree its node's ids start with 0 and keep incrementing by 1
    int nodeId = (m_joinTree == null) ? 0 : m_joinTree.getId() + 1;
    JoinNode leafNode;
    if (table != null) {
        assert (tableScan instanceof StmtTargetTableScan);
        leafNode = new TableLeafNode(nodeId, joinExpr, whereExpr, (StmtTargetTableScan) tableScan);
    } else {
        assert (tableScan instanceof StmtSubqueryScan);
        leafNode = new SubqueryLeafNode(nodeId, joinExpr, whereExpr, (StmtSubqueryScan) tableScan);
        leafNode.updateContentDeterminismMessage(((StmtSubqueryScan) tableScan).calculateContentDeterminismMessage());
    }
    if (m_joinTree == null) {
        // this is the first table
        m_joinTree = leafNode;
    } else {
        // Build the tree by attaching the next table always to the right
        // The node's join type is determined by the type of its right node
        JoinType joinType = JoinType.get(tableNode.attributes.get("jointype"));
        assert (joinType != JoinType.INVALID);
        JoinNode joinNode = new BranchNode(nodeId + 1, joinType, m_joinTree, leafNode);
        m_joinTree = joinNode;
    }
}
Also used : StmtSubqueryScan(org.voltdb.planner.parseinfo.StmtSubqueryScan) TupleValueExpression(org.voltdb.expressions.TupleValueExpression) Table(org.voltdb.catalog.Table) TableLeafNode(org.voltdb.planner.parseinfo.TableLeafNode) JoinNode(org.voltdb.planner.parseinfo.JoinNode) JoinType(org.voltdb.types.JoinType) VoltXMLElement(org.hsqldb_voltpatches.VoltXMLElement) Constraint(org.voltdb.catalog.Constraint) StmtTableScan(org.voltdb.planner.parseinfo.StmtTableScan) BranchNode(org.voltdb.planner.parseinfo.BranchNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) SubqueryLeafNode(org.voltdb.planner.parseinfo.SubqueryLeafNode) StmtTargetTableScan(org.voltdb.planner.parseinfo.StmtTargetTableScan)

Example 10 with JoinNode

use of org.voltdb.planner.parseinfo.JoinNode in project voltdb by VoltDB.

the class SelectSubPlanAssembler method generateSubPlanForJoinNodeRecursively.

/**
     * generate all possible plans for the tree.
     *
     * @param rootNode The root node for the whole join tree.
     * @param nodes The node list to iterate over.
     */
private void generateSubPlanForJoinNodeRecursively(JoinNode rootNode, int nextNode, List<JoinNode> nodes) {
    assert (nodes.size() > nextNode);
    JoinNode joinNode = nodes.get(nextNode);
    if (nodes.size() == nextNode + 1) {
        for (AccessPath path : joinNode.m_accessPaths) {
            joinNode.m_currentAccessPath = path;
            AbstractPlanNode plan = getSelectSubPlanForJoinNode(rootNode);
            if (plan == null) {
                continue;
            }
            m_plans.add(plan);
        }
        return;
    }
    for (AccessPath path : joinNode.m_accessPaths) {
        joinNode.m_currentAccessPath = path;
        generateSubPlanForJoinNodeRecursively(rootNode, nextNode + 1, nodes);
    }
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) JoinNode(org.voltdb.planner.parseinfo.JoinNode)

Aggregations

JoinNode (org.voltdb.planner.parseinfo.JoinNode)14 ArrayList (java.util.ArrayList)9 AbstractExpression (org.voltdb.expressions.AbstractExpression)9 BranchNode (org.voltdb.planner.parseinfo.BranchNode)7 StmtTableScan (org.voltdb.planner.parseinfo.StmtTableScan)3 List (java.util.List)2 AbstractPlanNode (org.voltdb.plannodes.AbstractPlanNode)2 JoinType (org.voltdb.types.JoinType)2 ArrayDeque (java.util.ArrayDeque)1 HashMap (java.util.HashMap)1 HashSet (java.util.HashSet)1 Set (java.util.Set)1 VoltXMLElement (org.hsqldb_voltpatches.VoltXMLElement)1 Constraint (org.voltdb.catalog.Constraint)1 Table (org.voltdb.catalog.Table)1 TupleValueExpression (org.voltdb.expressions.TupleValueExpression)1 StmtSubqueryScan (org.voltdb.planner.parseinfo.StmtSubqueryScan)1 StmtTargetTableScan (org.voltdb.planner.parseinfo.StmtTargetTableScan)1 SubqueryLeafNode (org.voltdb.planner.parseinfo.SubqueryLeafNode)1 TableLeafNode (org.voltdb.planner.parseinfo.TableLeafNode)1