Examples with StmtTargetTableScan org.voltdb.planner.parseinfo.StmtTargetTableScan used on opensource projects

Example 1 with StmtTargetTableScan

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

the class InsertSubPlanAssembler method nextPlan.

@Override
AbstractPlanNode nextPlan() {
    if (m_bestAndOnlyPlanWasGenerated) {
        return null;
    }
    // We may generate a few different plans for the subquery, but by the time
    // we get here, we'll generate only one plan for the INSERT statement itself.
    // Mostly this method exists to check that we can find a valid partitioning
    // for the statement.
    m_bestAndOnlyPlanWasGenerated = true;
    ParsedInsertStmt insertStmt = (ParsedInsertStmt) m_parsedStmt;
    Table targetTable = insertStmt.m_tableList.get(0);
    targetTable.getTypeName();
    StmtSubqueryScan subquery = insertStmt.getSubqueryScan();
    boolean subqueryIsMultiFragment = subquery.getBestCostPlan().rootPlanGraph.hasAnyNodeOfType(PlanNodeType.SEND);
    if (targetTable.getIsreplicated()) {
        // setUpForNewPlans already validates this
        assert (!m_partitioning.wasSpecifiedAsSingle() && !m_partitioning.isInferredSingle());
        // Cannot access any partitioned tables in subquery for replicated table
        if (!subquery.getIsReplicated()) {
            throw new PlanningErrorException("Subquery in " + getSqlType() + " INTO ... SELECT statement may not access " + "partitioned data for insertion into replicated table " + targetTable.getTypeName() + ".");
        }
    } else if (!m_partitioning.wasSpecifiedAsSingle()) {
        if (subqueryIsMultiFragment) {
            // What is the appropriate level of detail for this message?
            m_recentErrorMsg = getSqlType() + " INTO ... SELECT statement subquery is too complex.  " + "Please either simplify the subquery or use a SELECT followed by an INSERT.";
            return null;
        }
        Column partitioningCol = targetTable.getPartitioncolumn();
        if (partitioningCol == null) {
            assert (m_targetIsExportTable);
            m_recentErrorMsg = "The target table for an INSERT INTO ... SELECT statement is an " + "stream with no partitioning column defined.  " + "This is not currently supported.  Please define a " + "partitioning column for this stream to use it with INSERT INTO ... SELECT.";
            return null;
        }
        List<StmtTableScan> tables = new ArrayList<>();
        StmtTargetTableScan stmtTargetTableScan = new StmtTargetTableScan(targetTable);
        tables.add(stmtTargetTableScan);
        tables.add(subquery);
        // Create value equivalence between the partitioning column of the target table
        // and the corresponding expression produced by the subquery.
        HashMap<AbstractExpression, Set<AbstractExpression>> valueEquivalence = new HashMap<>();
        int i = 0;
        boolean setEquivalenceForPartitioningCol = false;
        for (Column col : insertStmt.m_columns.keySet()) {
            if (partitioningCol.compareTo(col) == 0) {
                List<SchemaColumn> partitioningColumns = stmtTargetTableScan.getPartitioningColumns();
                assert (partitioningColumns.size() == 1);
                AbstractExpression targetPartitionColExpr = partitioningColumns.get(0).getExpression();
                TupleValueExpression selectedExpr = subquery.getOutputExpression(i);
                assert (!valueEquivalence.containsKey(targetPartitionColExpr));
                assert (!valueEquivalence.containsKey(selectedExpr));
                Set<AbstractExpression> equivSet = new HashSet<>();
                equivSet.add(targetPartitionColExpr);
                equivSet.add(selectedExpr);
                valueEquivalence.put(targetPartitionColExpr, equivSet);
                valueEquivalence.put(selectedExpr, equivSet);
                setEquivalenceForPartitioningCol = true;
            }
            ++i;
        }
        if (!setEquivalenceForPartitioningCol) {
            // partitioning column of target table is not being set from value produced by the subquery.
            m_recentErrorMsg = "Partitioning column must be assigned a value " + "produced by the subquery in an " + getSqlType() + " INTO ... SELECT statement.";
            return null;
        }
        m_partitioning.analyzeForMultiPartitionAccess(tables, valueEquivalence);
        if (!m_partitioning.isJoinValid()) {
            m_recentErrorMsg = "Partitioning could not be determined for " + getSqlType() + " INTO ... SELECT statement.  " + "Please ensure that statement does not attempt to copy row data from one partition to another, " + "which is unsupported.";
            return null;
        }
    }
    return subquery.getBestCostPlan().rootPlanGraph;
}

Example 2 with StmtTargetTableScan

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

the class AbstractParsedStmt method addTableToStmtCache.

/**
     * Add a table to the statement cache.
     * @param table
     * @param tableAlias
     * @return the cache entry
     */
protected StmtTableScan addTableToStmtCache(Table table, String tableAlias) {
    // Create an index into the query Catalog cache
    StmtTableScan tableScan = m_tableAliasMap.get(tableAlias);
    if (tableScan == null) {
        tableScan = new StmtTargetTableScan(table, tableAlias, m_stmtId);
        m_tableAliasMap.put(tableAlias, tableScan);
    }
    return tableScan;
}

Example 3 with StmtTargetTableScan

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

the class CatalogSchemaTools method toSchema.

/**
     * Convert a Table catalog object into the proper SQL DDL, including all indexes,
     * constraints, and foreign key references.
     * Also returns just the CREATE TABLE statement, since, like all good methods,
     * it should have two purposes....
     * It would be nice to have a separate method to just generate the CREATE TABLE,
     * but we use that pass to also figure out what separate constraint and index
     * SQL DDL needs to be generated, so instead, we opt to build the CREATE TABLE DDL
     * separately as we go here, and then fill it in to the StringBuilder being used
     * to construct the full canonical DDL at the appropriate time.
     * @param sb - the schema being built
     * @param catalog_tbl - object to be analyzed
     * @param viewQuery - the Query if this Table is a View
     * @param isExportOnly Is this a export table.
     * @param streamPartitionColumn stream partition column
     * @param streamTarget - true if this Table is an Export Table
     * @return SQL Schema text representing the CREATE TABLE statement to generate the table
     */
public static String toSchema(StringBuilder sb, Table catalog_tbl, String viewQuery, boolean isExportOnly, String streamPartitionColumn, String streamTarget) {
    assert (!catalog_tbl.getColumns().isEmpty());
    boolean tableIsView = (viewQuery != null);
    // We need the intermediate results of building the table schema string so that
    // we can return the full CREATE TABLE statement, so accumulate it separately
    final StringBuilder table_sb = new StringBuilder();
    final Set<Index> skip_indexes = new HashSet<>();
    final Set<Constraint> skip_constraints = new HashSet<>();
    if (tableIsView) {
        table_sb.append("CREATE VIEW ").append(catalog_tbl.getTypeName()).append(" (");
    } else {
        if (isExportOnly) {
            table_sb.append("CREATE STREAM ").append(catalog_tbl.getTypeName());
            if (streamPartitionColumn != null && viewQuery == null) {
                table_sb.append(" PARTITION ON COLUMN ").append(streamPartitionColumn);
            }
            //Default target means no target.
            if (streamTarget != null && !streamTarget.equalsIgnoreCase(Constants.DEFAULT_EXPORT_CONNECTOR_NAME)) {
                table_sb.append(" EXPORT TO TARGET ").append(streamTarget);
            }
        } else {
            table_sb.append("CREATE TABLE ").append(catalog_tbl.getTypeName());
        }
        table_sb.append(" (");
    }
    // Columns
    String add = "\n";
    for (Column catalog_col : CatalogUtil.getSortedCatalogItems(catalog_tbl.getColumns(), "index")) {
        VoltType col_type = VoltType.get((byte) catalog_col.getType());
        if (tableIsView) {
            table_sb.append(add).append(spacer).append(catalog_col.getTypeName());
            add = ",\n";
            continue;
        }
        table_sb.append(add).append(spacer).append(catalog_col.getTypeName()).append(" ").append(col_type.toSQLString()).append(col_type.isVariableLength() && catalog_col.getSize() > 0 ? "(" + catalog_col.getSize() + (catalog_col.getInbytes() ? " BYTES" : "") + ")" : "");
        // Default value
        String defaultvalue = catalog_col.getDefaultvalue();
        //VoltType defaulttype = VoltType.get((byte)catalog_col.getDefaulttype());
        boolean nullable = catalog_col.getNullable();
        // TODO: Shouldn't have to check whether the string contains "null"
        if (defaultvalue == null) {
        } else if (defaultvalue.toLowerCase().equals("null") && nullable) {
            defaultvalue = null;
        } else {
            if (col_type == VoltType.TIMESTAMP) {
                if (defaultvalue.startsWith("CURRENT_TIMESTAMP")) {
                    defaultvalue = "CURRENT_TIMESTAMP";
                } else {
                    assert (defaultvalue.matches("[0-9]+"));
                    long epoch = Long.parseLong(defaultvalue);
                    Date d = new Date(epoch / 1000);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    defaultvalue = "\'" + sdf.format(d) + "." + StringUtils.leftPad(String.valueOf(epoch % 1000000), 6, "0") + "\'";
                }
            } else {
                // XXX: if (defaulttype != VoltType.VOLTFUNCTION) {
                // TODO: Escape strings properly
                defaultvalue = defaultvalue.replace("\'", "\'\'");
                defaultvalue = "'" + defaultvalue + "'";
            }
        }
        if (defaultvalue == null) {
            table_sb.append((!nullable ? " NOT NULL" : ""));
        } else {
            table_sb.append(" DEFAULT ").append(defaultvalue != null ? defaultvalue : "NULL").append(!nullable ? " NOT NULL" : "");
        }
        // Single-column constraints
        for (ConstraintRef catalog_const_ref : catalog_col.getConstraints()) {
            Constraint catalog_const = catalog_const_ref.getConstraint();
            ConstraintType const_type = ConstraintType.get(catalog_const.getType());
            // Check if there is another column in our table with the same constraint
            // If there is, then we need to add it to the end of the table definition
            boolean found = false;
            for (Column catalog_other_col : catalog_tbl.getColumns()) {
                if (catalog_other_col.equals(catalog_col))
                    continue;
                if (catalog_other_col.getConstraints().getIgnoreCase(catalog_const.getTypeName()) != null) {
                    found = true;
                    break;
                }
            }
            if (!found) {
                switch(const_type) {
                    case FOREIGN_KEY:
                        {
                            Table catalog_fkey_tbl = catalog_const.getForeignkeytable();
                            Column catalog_fkey_col = null;
                            for (ColumnRef ref : catalog_const.getForeignkeycols()) {
                                catalog_fkey_col = ref.getColumn();
                                // Nasty hack to get first item
                                break;
                            }
                            assert (catalog_fkey_col != null);
                            table_sb.append(" REFERENCES ").append(catalog_fkey_tbl.getTypeName()).append(" (").append(catalog_fkey_col.getTypeName()).append(")");
                            skip_constraints.add(catalog_const);
                            break;
                        }
                    default:
                }
            }
        }
        add = ",\n";
    }
    // Constraints
    for (Constraint catalog_const : catalog_tbl.getConstraints()) {
        if (skip_constraints.contains(catalog_const))
            continue;
        ConstraintType const_type = ConstraintType.get(catalog_const.getType());
        // Primary Keys / Unique Constraints
        if (const_type == ConstraintType.PRIMARY_KEY || const_type == ConstraintType.UNIQUE) {
            Index catalog_idx = catalog_const.getIndex();
            if (!tableIsView) {
                // Get the ConstraintType.
                table_sb.append(add).append(spacer);
                if (!catalog_const.getTypeName().startsWith(HSQLInterface.AUTO_GEN_PREFIX)) {
                    table_sb.append("CONSTRAINT ").append(catalog_const.getTypeName()).append(" ");
                }
                if (const_type == ConstraintType.PRIMARY_KEY || const_type == ConstraintType.UNIQUE) {
                    if (const_type == ConstraintType.PRIMARY_KEY) {
                        table_sb.append("PRIMARY KEY (");
                    } else {
                        if (catalog_idx.getAssumeunique()) {
                            table_sb.append("ASSUMEUNIQUE (");
                        } else {
                            table_sb.append("UNIQUE (");
                        }
                    }
                    String col_add = "";
                    if (catalog_idx.getExpressionsjson() != null && !catalog_idx.getExpressionsjson().equals("")) {
                        String exprStrings = new String();
                        StmtTargetTableScan tableScan = new StmtTargetTableScan(catalog_tbl);
                        try {
                            List<AbstractExpression> expressions = AbstractExpression.fromJSONArrayString(catalog_idx.getExpressionsjson(), tableScan);
                            String sep = "";
                            for (AbstractExpression expr : expressions) {
                                exprStrings += sep + expr.explain(catalog_tbl.getTypeName());
                                sep = ",";
                            }
                        } catch (JSONException e) {
                        }
                        table_sb.append(col_add).append(exprStrings);
                    } else {
                        for (ColumnRef catalog_colref : CatalogUtil.getSortedCatalogItems(catalog_idx.getColumns(), "index")) {
                            table_sb.append(col_add).append(catalog_colref.getColumn().getTypeName());
                            col_add = ", ";
                        }
                    // FOR
                    }
                    table_sb.append(")");
                }
            }
            if (catalog_idx.getTypeName().startsWith(HSQLInterface.AUTO_GEN_PREFIX) || catalog_idx.getTypeName().startsWith(HSQLInterface.AUTO_GEN_MATVIEW)) {
                skip_indexes.add(catalog_idx);
            }
        // Foreign Key
        } else if (const_type == ConstraintType.FOREIGN_KEY) {
            Table catalog_fkey_tbl = catalog_const.getForeignkeytable();
            String col_add = "";
            String our_columns = "";
            String fkey_columns = "";
            for (ColumnRef catalog_colref : catalog_const.getForeignkeycols()) {
                // The name of the ColumnRef is the column in our base table
                Column our_column = catalog_tbl.getColumns().getIgnoreCase(catalog_colref.getTypeName());
                assert (our_column != null);
                our_columns += col_add + our_column.getTypeName();
                Column fkey_column = catalog_colref.getColumn();
                assert (fkey_column != null);
                fkey_columns += col_add + fkey_column.getTypeName();
                col_add = ", ";
            }
            table_sb.append(add).append(spacer + "CONSTRAINT ").append(catalog_const.getTypeName()).append(" FOREIGN KEY (").append(our_columns).append(") REFERENCES ").append(catalog_fkey_tbl.getTypeName()).append(" (").append(fkey_columns).append(")");
        }
        skip_constraints.add(catalog_const);
    }
    if (catalog_tbl.getTuplelimit() != Integer.MAX_VALUE) {
        table_sb.append(add).append(spacer + "LIMIT PARTITION ROWS ").append(String.valueOf(catalog_tbl.getTuplelimit()));
        String deleteStmt = CatalogUtil.getLimitPartitionRowsDeleteStmt(catalog_tbl);
        if (deleteStmt != null) {
            if (deleteStmt.endsWith(";")) {
                // StatementCompiler appends the semicolon, we don't want it here.
                deleteStmt = deleteStmt.substring(0, deleteStmt.length() - 1);
            }
            table_sb.append("\n" + spacer + spacer + "EXECUTE (").append(deleteStmt).append(")");
        }
    }
    if (viewQuery != null) {
        table_sb.append("\n) AS \n");
        table_sb.append(spacer).append(viewQuery).append(";\n");
    } else {
        table_sb.append("\n);\n");
    }
    // We've built the full CREATE TABLE statement for this table,
    // Append the generated table schema to the canonical DDL StringBuilder
    sb.append(table_sb.toString());
    // Partition Table for regular tables (non-streams)
    if (catalog_tbl.getPartitioncolumn() != null && viewQuery == null && !isExportOnly) {
        sb.append("PARTITION TABLE ").append(catalog_tbl.getTypeName()).append(" ON COLUMN ").append(catalog_tbl.getPartitioncolumn().getTypeName()).append(";\n");
    }
    // All other Indexes
    for (Index catalog_idx : catalog_tbl.getIndexes()) {
        if (skip_indexes.contains(catalog_idx))
            continue;
        if (catalog_idx.getUnique()) {
            if (catalog_idx.getAssumeunique()) {
                sb.append("CREATE ASSUMEUNIQUE INDEX ");
            } else {
                sb.append("CREATE UNIQUE INDEX ");
            }
        } else {
            sb.append("CREATE INDEX ");
        }
        sb.append(catalog_idx.getTypeName()).append(" ON ").append(catalog_tbl.getTypeName()).append(" (");
        add = "";
        String jsonstring = catalog_idx.getExpressionsjson();
        if (jsonstring.isEmpty()) {
            for (ColumnRef catalog_colref : CatalogUtil.getSortedCatalogItems(catalog_idx.getColumns(), "index")) {
                sb.append(add).append(catalog_colref.getColumn().getTypeName());
                add = ", ";
            }
        } else {
            List<AbstractExpression> indexedExprs = null;
            try {
                indexedExprs = AbstractExpression.fromJSONArrayString(jsonstring, new StmtTargetTableScan(catalog_tbl));
            } catch (JSONException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            if (indexedExprs != null) {
                for (AbstractExpression expr : indexedExprs) {
                    sb.append(add).append(expr.explain(catalog_tbl.getTypeName()));
                    add = ", ";
                }
            }
        }
        sb.append(")");
        String jsonPredicate = catalog_idx.getPredicatejson();
        if (!jsonPredicate.isEmpty()) {
            try {
                AbstractExpression predicate = AbstractExpression.fromJSONString(jsonPredicate, new StmtTargetTableScan(catalog_tbl));
                sb.append(" WHERE ").append(predicate.explain(catalog_tbl.getTypeName()));
            } catch (JSONException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        sb.append(";\n");
    }
    if (catalog_tbl.getIsdred()) {
        sb.append("DR TABLE ").append(catalog_tbl.getTypeName()).append(";\n");
    }
    sb.append("\n");
    // statement to whoever might be interested (DDLCompiler, I'm looking in your direction)
    return table_sb.toString();
}

Example 4 with StmtTargetTableScan

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

the class SeqScanPlanNode method computeCostEstimates.

@Override
public void computeCostEstimates(long childOutputTupleCountEstimate, DatabaseEstimates estimates, ScalarValueHints[] paramHints) {
    if (m_isSubQuery) {
        // Get estimates from the sub-query
        // @TODO For the sub-query the cost estimates will be calculated separately
        // At the moment its contribution to the parent's cost plan is irrelevant because
        // all parent plans have the same best cost plan for the sub-query
        m_estimatedProcessedTupleCount = SUBQUERY_TABLE_ESTIMATES_HACK.minTuples;
        m_estimatedOutputTupleCount = SUBQUERY_TABLE_ESTIMATES_HACK.minTuples;
        return;
    }
    Table target = ((StmtTargetTableScan) m_tableScan).getTargetTable();
    TableEstimates tableEstimates = estimates.getEstimatesForTable(target.getTypeName());
    // This maxTuples value estimates the number of tuples fetched from the sequential scan.
    // It's a vague measure of the cost of the scan.
    // Its accuracy depends a lot on what kind of post-filtering or projection needs to happen, if any.
    // The tuplesRead value is also used to estimate the number of RESULT rows, regardless of
    // how effective post-filtering might be -- as if all rows passed the filters.
    // This is at least semi-consistent with the ignoring of post-filter effects in IndexScanPlanNode.
    // In effect, though, it gives index scans an "unfair" advantage when they reduce the estimated result size
    // by taking into account the indexed filters -- follow-on plan steps, sorts (etc.), are costed lower
    // as if they are operating on fewer rows than would have come out of the seqscan,
    // though that's nonsense.
    // In any case, it's important to keep an eye on any changes (discounts) to SeqScanPlanNode's costing
    // here to make sure that SeqScanPlanNode never gains an unfair advantage over IndexScanPlanNode.
    m_estimatedProcessedTupleCount = tableEstimates.maxTuples;
    m_estimatedOutputTupleCount = tableEstimates.maxTuples;
}

Example 5 with StmtTargetTableScan

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

the class CatalogUtil method updateUsageAnnotations.

/**
     * Given plan graphs and a SQL stmt, compute a bi-directonal usage map between
     * schema (indexes, table & views) and SQL/Procedures.
     * Use "annotation" objects to store this extra information in the catalog
     * during compilation and catalog report generation.
     */
public static void updateUsageAnnotations(Database db, Statement stmt, AbstractPlanNode topPlan, AbstractPlanNode bottomPlan) {
    Map<String, StmtTargetTableScan> tablesRead = new TreeMap<>();
    Collection<String> indexes = new TreeSet<>();
    if (topPlan != null) {
        topPlan.getTablesAndIndexes(tablesRead, indexes);
    }
    if (bottomPlan != null) {
        bottomPlan.getTablesAndIndexes(tablesRead, indexes);
    }
    String updated = "";
    if (!stmt.getReadonly()) {
        updated = topPlan.getUpdatedTable();
        if (updated == null) {
            updated = bottomPlan.getUpdatedTable();
        }
        assert (updated.length() > 0);
    }
    Set<String> readTableNames = tablesRead.keySet();
    stmt.setTablesread(StringUtils.join(readTableNames, ","));
    stmt.setTablesupdated(updated);
    Set<String> tableDotIndexNames = new TreeSet<>();
    for (Table table : db.getTables()) {
        if (readTableNames.contains(table.getTypeName())) {
            readTableNames.remove(table.getTypeName());
            for (String indexName : indexes) {
                Index index = table.getIndexes().get(indexName);
                if (index != null) {
                    tableDotIndexNames.add(table.getTypeName() + "." + index.getTypeName());
                }
            }
        }
    }
    String indexString = StringUtils.join(tableDotIndexNames, ",");
    stmt.setIndexesused(indexString);
    assert (tablesRead.size() == 0);
}