// Delete removes rows from a table.
// Privileges: DELETE and SELECT on table. We currently always use a SELECT statement.
// Notes: postgres requires DELETE. Also requires SELECT for "USING" and "WHERE" with tables.
// mysql requires DELETE. Also requires SELECT if a table is used in the "WHERE" clause.
func (p *planner) Delete(
n *parser.Delete, desiredTypes []parser.Type, autoCommit bool,
) (planNode, error) {
tn, err := p.getAliasedTableName(n.Table)
if err != nil {
return nil, err
}
en, err := p.makeEditNode(tn, autoCommit, privilege.DELETE)
if err != nil {
return nil, err
}
var requestedCols []sqlbase.ColumnDescriptor
if len(n.Returning) > 0 {
// TODO(dan): This could be made tighter, just the rows needed for RETURNING
// exprs.
requestedCols = en.tableDesc.Columns
}
fkTables := tablesNeededForFKs(*en.tableDesc, CheckDeletes)
if err := p.fillFKTableMap(fkTables); err != nil {
return nil, err
}
rd, err := makeRowDeleter(p.txn, en.tableDesc, fkTables, requestedCols, checkFKs)
if err != nil {
return nil, err
}
tw := tableDeleter{rd: rd, autoCommit: autoCommit}
// TODO(knz): Until we split the creation of the node from Start()
// for the SelectClause too, we cannot cache this. This is because
// this node's initSelect() method both does type checking and also
// performs index selection. We cannot perform index selection
// properly until the placeholder values are known.
rows, err := p.SelectClause(&parser.SelectClause{
Exprs: sqlbase.ColumnsSelectors(rd.fetchCols),
From: &parser.From{Tables: []parser.TableExpr{n.Table}},
Where: n.Where,
}, nil, nil, nil, publicAndNonPublicColumns)
if err != nil {
return nil, err
}
dn := &deleteNode{
n: n,
editNodeBase: en,
tw: tw,
}
if err := dn.run.initEditNode(&dn.editNodeBase, rows, n.Returning, desiredTypes); err != nil {
return nil, err
}
return dn, nil
}
func (p *planner) validateCheckExpr(
exprStr string, tableName parser.TableExpr, tableDesc *sqlbase.TableDescriptor,
) error {
expr, err := parser.ParseExprTraditional(exprStr)
if err != nil {
return err
}
sel := &parser.SelectClause{
Exprs: sqlbase.ColumnsSelectors(tableDesc.Columns),
From: &parser.From{Tables: parser.TableExprs{tableName}},
Where: &parser.Where{Expr: &parser.NotExpr{Expr: expr}},
}
lim := &parser.Limit{Count: parser.NewDInt(1)}
// This could potentially use a variant of planner.SelectClause that could
// use the tableDesc we have, but this is a rare operation and be benefit
// would be marginal compared to the work of the actual query, so the added
// complexity seems unjustified.
rows, err := p.SelectClause(sel, nil, lim, nil, publicColumns)
if err != nil {
return err
}
rows, err = p.optimizePlan(rows, allColumns(rows))
if err != nil {
return err
}
if err := p.startPlan(rows); err != nil {
return err
}
next, err := rows.Next()
if err != nil {
return err
}
if next {
return errors.Errorf("validation of CHECK %q failed on row: %s",
expr.String(), labeledRowValues(tableDesc.Columns, rows.Values()))
}
return nil
}
// Update updates columns for a selection of rows from a table.
// Privileges: UPDATE and SELECT on table. We currently always use a select statement.
// Notes: postgres requires UPDATE. Requires SELECT with WHERE clause with table.
// mysql requires UPDATE. Also requires SELECT with WHERE clause with table.
// TODO(guanqun): need to support CHECK in UPDATE
func (p *planner) Update(
n *parser.Update, desiredTypes []parser.Type, autoCommit bool,
) (planNode, error) {
tracing.AnnotateTrace()
tn, err := p.getAliasedTableName(n.Table)
if err != nil {
return nil, err
}
en, err := p.makeEditNode(tn, autoCommit, privilege.UPDATE)
if err != nil {
return nil, err
}
exprs := make([]*parser.UpdateExpr, len(n.Exprs))
for i, expr := range n.Exprs {
// Replace the sub-query nodes.
newExpr, err := p.replaceSubqueries(expr.Expr, len(expr.Names))
if err != nil {
return nil, err
}
exprs[i] = &parser.UpdateExpr{Tuple: expr.Tuple, Expr: newExpr, Names: expr.Names}
}
// Determine which columns we're inserting into.
names, err := p.namesForExprs(exprs)
if err != nil {
return nil, err
}
updateCols, err := p.processColumns(en.tableDesc, names)
if err != nil {
return nil, err
}
defaultExprs, err := makeDefaultExprs(updateCols, &p.parser, &p.evalCtx)
if err != nil {
return nil, err
}
var requestedCols []sqlbase.ColumnDescriptor
if len(n.Returning) > 0 || len(en.tableDesc.Checks) > 0 {
// TODO(dan): This could be made tighter, just the rows needed for RETURNING
// exprs.
requestedCols = en.tableDesc.Columns
}
fkTables := tablesNeededForFKs(*en.tableDesc, CheckUpdates)
if err := p.fillFKTableMap(fkTables); err != nil {
return nil, err
}
ru, err := makeRowUpdater(p.txn, en.tableDesc, fkTables, updateCols, requestedCols, rowUpdaterDefault)
if err != nil {
return nil, err
}
tw := tableUpdater{ru: ru, autoCommit: autoCommit}
tracing.AnnotateTrace()
// Generate the list of select targets. We need to select all of the columns
// plus we select all of the update expressions in case those expressions
// reference columns (e.g. "UPDATE t SET v = v + 1"). Note that we flatten
// expressions for tuple assignments just as we flattened the column names
// above. So "UPDATE t SET (a, b) = (1, 2)" translates into select targets of
// "*, 1, 2", not "*, (1, 2)".
targets := sqlbase.ColumnsSelectors(ru.fetchCols)
i := 0
// Remember the index where the targets for exprs start.
exprTargetIdx := len(targets)
desiredTypesFromSelect := make([]parser.Type, len(targets), len(targets)+len(exprs))
for i := range targets {
desiredTypesFromSelect[i] = parser.TypeAny
}
for _, expr := range exprs {
if expr.Tuple {
switch t := expr.Expr.(type) {
case (*parser.Tuple):
for _, e := range t.Exprs {
typ := updateCols[i].Type.ToDatumType()
e := fillDefault(e, typ, i, defaultExprs)
targets = append(targets, parser.SelectExpr{Expr: e})
desiredTypesFromSelect = append(desiredTypesFromSelect, typ)
i++
}
default:
return nil, fmt.Errorf("cannot use this expression to assign multiple columns: %s", expr.Expr)
}
} else {
typ := updateCols[i].Type.ToDatumType()
e := fillDefault(expr.Expr, typ, i, defaultExprs)
targets = append(targets, parser.SelectExpr{Expr: e})
desiredTypesFromSelect = append(desiredTypesFromSelect, typ)
i++
}
}
rows, err := p.SelectClause(&parser.SelectClause{
Exprs: targets,
From: &parser.From{Tables: []parser.TableExpr{n.Table}},
Where: n.Where,
}, nil, nil, desiredTypesFromSelect, publicAndNonPublicColumns)
if err != nil {
return nil, err
}
// Placeholders have their types populated in the above Select if they are part
// of an expression ("SET a = 2 + $1") in the type check step where those
// types are inferred. For the simpler case ("SET a = $1"), populate them
// using checkColumnType. This step also verifies that the expression
// types match the column types.
sel := rows.(*selectTopNode).source.(*selectNode)
for i, target := range sel.render[exprTargetIdx:] {
// DefaultVal doesn't implement TypeCheck
if _, ok := target.(parser.DefaultVal); ok {
continue
}
// TODO(nvanbenschoten) isn't this TypeCheck redundant with the call to SelectClause?
typedTarget, err := parser.TypeCheck(target, &p.semaCtx, updateCols[i].Type.ToDatumType())
if err != nil {
return nil, err
}
err = sqlbase.CheckColumnType(updateCols[i], typedTarget.ResolvedType(), p.semaCtx.Placeholders)
if err != nil {
return nil, err
}
}
updateColsIdx := make(map[sqlbase.ColumnID]int, len(ru.updateCols))
for i, col := range ru.updateCols {
updateColsIdx[col.ID] = i
}
un := &updateNode{
n: n,
editNodeBase: en,
updateCols: ru.updateCols,
updateColsIdx: updateColsIdx,
tw: tw,
}
if err := un.checkHelper.init(p, tn, en.tableDesc); err != nil {
return nil, err
}
if err := un.run.initEditNode(&un.editNodeBase, rows, n.Returning, desiredTypes); err != nil {
return nil, err
}
return un, nil
}